BigQuery脚本:根据表列中包含的每一行运行SQL查询 [英] BigQuery scripting: running a SQL query based on each row included in a table column

问题描述

我的来源表包括 url_id 和 SQL [标准sql，字符串]

My origin table includes url_id and SQL [standard sql, string]

我想在字符串列"SQL"中运行每个SQL语句.并将结果与​​相应的url_id结合起来

I would like to run each of the SQL statements in the string column "SQL" and join the outcome with the correspondent url_id

预期结果

更新

为了便于重现，我从公共BQ导入了类似的模式:

to make this easy to reproduce I´ve imported a similar schema from public BQ:

bigquery-public-data.covid19_italy.data_by_province

bigquery-public-data.covid19_italy.data_by_province

如果我跑步

select string_agg(
    format('select %i as url_id, (%s) as simpleSKU', url_id, sql), 
    ' union all '   )

FROM `xxx.staging.test_query

它返回

推荐答案

在下面考虑

execute immediate (
  select 'select url_id, region_name from (' || string_agg(
    format('select %i as url_id, array(%s) as region_names', url_id, sql), 
    ' union all '
  ) || '), unnest(region_names) as region_name'
  from your_table
);   

如果应用于您问题中的样本数据，请输出

if applied to sample data in your question - output it

这篇关于BigQuery脚本:根据表列中包含的每一行运行SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！