google-bigquery - BigQuery：在查询 SELECT * EXCEPT(columns) 中使用 INFORMATION_SCHEMA 的结果来过滤重复的列

Question

试图找到两个表之间的重叠列并在同一个 SQL 查询中使用结果。

具有两个单独工作但不能将它们组合在一起的子查询的代码示例：

WITH DIFF AS (
  SELECT column_name AS Columns
  -- SELECT ARRAY_AGG(column_name) AS Columns
  -- SELECT STRING_AGG(column_name) AS Columns
  FROM (
  SELECT column_name, 'S' AS S
  FROM DB.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
  WHERE table_name = 'T1'
  INTERSECT DISTINCT
  SELECT column_name, 'S' AS S
  FROM DB.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
  WHERE table_name = 'T2'
  )
  -- GROUP BY S
)

SELECT T1.*, T2.* EXCEPT(SELECT Columns FROM DIFF)
-- SELECT T1.*, T2.* EXCEPT (T1.*)
FROM DB.T1
LEFT JOIN DB.T2 USING(GUID)

使用 Python pandas，连接非常容易，因为在重复列的情况下，后缀具有强大的功能，但在当前的 SQL 中，我们缺少这种功能。

标签： google-bigquery