google-bigquery - 使用 CTE 选择主要查询的列

Question

可重现的例子

这利用了 bigquery 公共数据集，但可以随意替换类似的数据结构：

with common_columns as (
    select c1.column_name from `bigquery-public-data`.ml_datasets.INFORMATION_SCHEMA.COLUMNS c1 where c1.table_name = 'iris'
    INTERSECT DISTINCT
    select c2.column_name from `bigquery-public-data`.ml_datasets.INFORMATION_SCHEMA.COLUMNS c2 where c2.table_name = 'iris' and not regexp_contains(c2.column_name,r'sepal')
)

select `species`,`petal_width`,`petal_length` from `bigquery-public-data`.ml_datasets.iris
UNION ALL 
select `species`,`petal_width`,`petal_length` from `bigquery-public-data`.ml_datasets.iris

目标

我想要完成的是使用 CTE 找到它们之间的公共列c1，c2然后在联合查询中引用这些公共表。

就像是：

select (insert column_names from common_columns here) from my_dataset
UNION ALL
select (insert column_names from common_columns here) from my_second_dataset

c1（为了这个例子，我将c2它们视为完全不同的表，只是为了一个简单的例子而使用相同的源表）

标签： google-bigquery

Answer 1

考虑下面的例子

execute immediate( 
  select * from (
    with common_columns as (
      select c1.column_name from `bigquery-public-data`.ml_datasets.INFORMATION_SCHEMA.COLUMNS c1 where c1.table_name = 'iris'
      INTERSECT DISTINCT
      select c2.column_name from `bigquery-public-data`.ml_datasets.INFORMATION_SCHEMA.COLUMNS c2 where c2.table_name = 'iris' and not regexp_contains(c2.column_name,r'sepal')
    )
    select 'select ' || string_agg(column_name, ', ') || ' from `bigquery-public-data`.ml_datasets.iris'
    from common_columns
  )
)      

正如你在这里看到的 - 内部选择

with common_columns as (
    select c1.column_name from `bigquery-public-data`.ml_datasets.INFORMATION_SCHEMA.COLUMNS c1 where c1.table_name = 'iris'
    INTERSECT DISTINCT
    select c2.column_name from `bigquery-public-data`.ml_datasets.INFORMATION_SCHEMA.COLUMNS c2 where c2.table_name = 'iris' and not regexp_contains(c2.column_name,r'sepal')
)
select 'select ' || string_agg(column_name, ', ') || ' from `bigquery-public-data`.ml_datasets.iris'
from common_columns    

输出动态构建最终选择语句 - 输出是

...然后 -execute immediate执行它。显然，您可以修改该内部查询以构建您的情况所需的任何内容 - 包括联合所有等。