BigQuery获取表所有列唯一值的查询问题求助

我在BigQuery中有一张表my-project1.my-dataset1.cars，表结构和数据如下：

我需要编写一个自动化查询，输出每列的列名、唯一值数量、唯一值列表，期望结果如下：

我尝试了以下查询但无法正常运行：

WITH
    all_data AS (
        SELECT *
        FROM   `my-project1.my-dataset1.cars` 
    ),
    column_names AS (
        SELECT column_name
        FROM `my-project1`.`my-dataset1`.INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'cars' 
    ),
    distinct_values AS (
        SELECT
            column_name,
            ARRAY_LENGTH(ARRAY(select distinct column_name from all_data)) AS number_of_options,
            ARRAY(select distinct column_name from all_data) AS distinct_options
        FROM column_names
    )
SELECT
    *
FROM distinct_values

我的实际表有上百列，没法手动处理，请问这个查询哪里有问题？

原查询的问题 #

• 列名引用错误：在distinct_values部分，你写的select distinct column_name from all_data里的column_name是字符串常量，不是动态引用当前行的列名。BigQuery会去表中找名为column_name的列，显然你的表没有这一列，直接报错。
• 不支持动态列引用：标准SQL没法直接在循环列名时动态引用列，必须用UNPIVOT转置或者动态生成SQL的方式实现。

自动化解决方案 #

方案1：UNPIVOT转置后聚合（适合快速实现） #

如果表中列类型可以统一转换为字符串（或本身类型一致），可以先把所有列转成键值对，再按列名聚合：

WITH unpivoted_data AS (
  SELECT 
    column_name,
    value
  FROM `my-project1.my-dataset1.cars`
  UNPIVOT (
    value FOR column_name IN (year, make, model)
  )
)
SELECT
  column_name,
  COUNT(DISTINCT value) AS number_of_options,
  ARRAY_AGG(DISTINCT value ORDER BY value) AS distinct_options
FROM unpivoted_data
GROUP BY column_name
ORDER BY column_name;

方案2：动态生成SQL（完全自动化，适配百列场景） #

手动写UNPIVOT的列列表太麻烦，直接从INFORMATION_SCHEMA获取所有列名，动态生成完整SQL并执行：

DECLARE sql STRING;

SET sql = (
  SELECT CONCAT(
    'WITH unpivoted_data AS (',
    '  SELECT column_name, value FROM `my-project1.my-dataset1.cars` UNPIVOT (value FOR column_name IN (',
    STRING_AGG(column_name, ', ' ORDER BY column_name),
    '))',
    ')',
    'SELECT column_name, COUNT(DISTINCT value) AS number_of_options, ARRAY_AGG(DISTINCT value ORDER BY value) AS distinct_options FROM unpivoted_data GROUP BY column_name ORDER BY column_name'
  )
  FROM `my-project1.my-dataset1.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'cars'
);

EXECUTE IMMEDIATE sql;

这个方案会自动获取所有列名，生成包含全部列的UNPIVOT语句，执行后就能得到目标结果。如果列类型混合（数字、字符串等），UNPIVOT会自动把值转成STRING类型，确保统一处理。

补充说明 #

• 如果表中有NULL值，COUNT(DISTINCT value)会忽略NULL。如果需要统计NULL，可以改成COUNT(DISTINCT IFNULL(value, 'NULL'))，ARRAY_AGG里也同步调整为ARRAY_AGG(DISTINCT IFNULL(value, 'NULL') ORDER BY IFNULL(value, 'NULL'))。
• 超大表使用UNPIVOT可能有性能开销，可以考虑限制数据范围（比如加WHERE条件）后再处理。

内容的提问来源于stack exchange，提问作者Kris