如何在BigQuery中高效统计500列的distinct count？

针对500列的大表，逐个写COUNT(DISTINCT)效率极低，推荐用动态SQL批量生成查询的方式，自动遍历所有列并返回结果，步骤如下：

方案1：精确去重计数 #

通过INFORMATION_SCHEMA.COLUMNS获取列名，动态生成UNION ALL拼接的查询语句，最后执行得到结果：

-- 替换成你的项目、数据集、表名
DECLARE target_project STRING DEFAULT 'your_project';
DECLARE target_dataset STRING DEFAULT 'your_dataset';
DECLARE target_table STRING DEFAULT 'your_table';
DECLARE sql_query STRING;

SET sql_query = (
  SELECT STRING_AGG(
    -- 为每个列生成单独的查询语句，用反引号包裹列名避免特殊字符冲突
    FORMAT(
      "SELECT '%s' AS column_name, COUNT(DISTINCT `%s`) AS distinct_count FROM `%s.%s.%s`",
      column_name, column_name, target_project, target_dataset, target_table
    ),
    "\nUNION ALL\n" -- 拼接所有列的查询
  )
  FROM `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = target_table
);

-- 执行动态生成的SQL
EXECUTE IMMEDIATE sql_query;

方案2：近似去重计数（更快） #

如果你的场景不需要绝对精确的计数，推荐用APPROX_COUNT_DISTINCT替代COUNT(DISTINCT)，速度提升数倍，误差通常在1%以内，适合超大型表：

DECLARE target_project STRING DEFAULT 'your_project';
DECLARE target_dataset STRING DEFAULT 'your_dataset';
DECLARE target_table STRING DEFAULT 'your_table';
DECLARE sql_query STRING;

SET sql_query = (
  SELECT STRING_AGG(
    FORMAT(
      "SELECT '%s' AS column_name, APPROX_COUNT_DISTINCT(`%s`) AS approx_distinct_count FROM `%s.%s.%s`",
      column_name, column_name, target_project, target_dataset, target_table
    ),
    "\nUNION ALL\n"
  )
  FROM `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = target_table
);

EXECUTE IMMEDIATE sql_query;

注意事项 #

• 确保你有目标表的查询权限，以及INFORMATION_SCHEMA.COLUMNS的读取权限
• 列名包含特殊字符（如空格、关键字）时，反引号会自动处理，避免语法错误
• 若表数据量极大，可考虑给查询添加PARTITION BY或CLUSTER BY的过滤条件，进一步缩小计算范围

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