如何在BigQuery中对选定列生成含分位数、均值等指标的统计汇总

思路1：UNION ALL 拼接（适合列数少的场景） #

列数较少时直接手动拼接每个列的统计逻辑，是兼容性最高的写法，标准SQL通用：

SELECT 
  'Col1' AS 列名,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Col1) AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Col1) AS Q2,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Col1) AS Q3,
  AVG(Col1) AS 均值,
  ROUND(STDDEV_POP(Col1),2) AS 标准差
FROM 你的表名
UNION ALL
SELECT 
  'Col2' AS 列名,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Col2) AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Col2) AS Q2,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Col2) AS Q3,
  AVG(Col2) AS 均值,
  ROUND(STDDEV_POP(Col2),2) AS 标准差
FROM 你的表名
UNION ALL
SELECT 
  'Col3' AS 列名,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Col3) AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Col3) AS Q2,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Col3) AS Q3,
  AVG(Col3) AS 均值,
  ROUND(STDDEV_POP(Col3),2) AS 标准差
FROM 你的表名

思路2：动态SQL生成（适合列数多/列频繁变动的场景） #

列数较多时手动写重复逻辑效率低，可通过查询数据库的系统表获取所有数值列名，自动拼接SQL语句执行。以下是MySQL的示例：

SET @sql = NULL;
SELECT GROUP_CONCAT(
  'SELECT ''' , column_name , ''' AS 列名,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY ', column_name , ') AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ', column_name , ') AS Q2,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY ', column_name , ') AS Q3,
  AVG(', column_name , ') AS 均值,
  ROUND(STDDEV_POP(', column_name , '),2) AS 标准差
  FROM 你的表名'
  SEPARATOR ' UNION ALL '
) INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = '你的表名' 
AND column_name NOT IN ('ID'); -- 此处过滤不需要统计的非数值列

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

思路3：行转列后分组聚合（代码更简洁，推荐） #

先通过UNPIVOT将多列转为「列名+对应数值」的行结构，再按列名分组统计，只需写一次统计函数逻辑，无需重复编码：

WITH unpivot_data AS (
  SELECT 列名, 数值
  FROM 你的表名
  UNPIVOT (
    数值 FOR 列名 IN (Col1, Col2, Col3)
  ) AS unpvt
)
SELECT 
  列名,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY 数值) AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY 数值) AS Q2,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY 数值) AS Q3,
  AVG(数值) AS 均值,
  ROUND(STDDEV_POP(数值),2) AS 标准差
FROM unpivot_data
GROUP BY 列名;

Hive/Spark SQL没有UNPIVOT关键字，可使用stack函数实现相同效果：

WITH unpivot_data AS (
  SELECT stack(3, 'Col1', Col1, 'Col2', Col2, 'Col3', Col3) AS (列名, 数值)
  FROM 你的表名
)
-- 后续统计逻辑和上面一致

转置格式输出实现 #

如果需要统计指标为行、列名为列的第二种输出格式，可在上面的统计结果基础上再做一次行转列+列转行：

WITH col_stats AS (
  SELECT 
    列名,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY 数值) AS Q1,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY 数值) AS Q2,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY 数值) AS Q3,
    AVG(数值) AS 均值,
    ROUND(STDDEV_POP(数值),2) AS 标准差
  FROM (
    SELECT 列名, 数值 FROM 你的表名 UNPIVOT (数值 FOR 列名 IN (Col1, Col2, Col3)) AS unpvt
  ) t
  GROUP BY 列名
)
SELECT 统计指标, Col1, Col2, Col3
FROM col_stats
UNPIVOT (
  统计值 FOR 统计指标 IN (Q1, Q2, Q3, 均值, 标准差)
) AS unpvt
PIVOT (
  MAX(统计值) FOR 列名 IN (Col1, Col2, Col3)
) AS pvt;

注意事项 #

• 不同数据库的分位数、行转列语法存在细微差异，可根据实际使用的数据库调整对应语法。
• 动态SQL场景需要提前过滤非数值类型的列，避免统计报错。
• 如果需要计算样本标准差，可将代码中的STDDEV_POP替换为STDDEV_SAMP。

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