BigQuery表列中位数计算及新增中位数列存储方案咨询

实现方案 #

方案1：列数较少时直接手动编写SQL #

不需要做JOIN操作，直接用BigQuery内置的中位数窗口函数全表计算即可，仅会扫描一次原表，适配大数据量场景：

SELECT
  *,
  -- 计算COL_1全表中位数
  PERCENTILE_CONT(0.5) OVER() AS MCOL_1,
  -- 计算COL_2全表中位数
  PERCENTILE_CONT(0.5) OVER() AS MCOL_2,
  -- 按规则补充剩余列的中位数计算逻辑
  PERCENTILE_CONT(0.5) OVER() AS MCOL_N
FROM `你的项目ID.你的数据集名.你的表名`

说明：如果要求中位数必须是表中存在的离散值，把PERCENTILE_CONT替换为PERCENTILE_DISC(0.5) OVER()即可。

方案2：列数较多时用动态SQL自动生成 #

如果列数N很大，手动编写所有列的计算逻辑效率低，可以用BigQuery的动态SQL自动拼接逻辑：

DECLARE sql_str STRING;

-- 自动拼接所有待计算列的中位数逻辑
SET sql_str = (
  SELECT CONCAT(
    "SELECT *, ",
    STRING_AGG(CONCAT("PERCENTILE_CONT(0.5) OVER() AS MCOL_", SPLIT(column_name, '_')[OFFSET(1)]), ", "),
    " FROM `你的项目ID.你的数据集名.你的表名`"
  )
  FROM `你的项目ID.你的数据集名.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = '你的表名'
  -- 可按实际列名规则调整过滤条件，只处理需要计算中位数的列
  AND column_name LIKE 'COL_%'
);

-- 执行生成好的SQL
EXECUTE IMMEDIATE sql_str;

• 全程不需要手动逐个列写计算逻辑，会自动读取表结构生成对应代码
• 无JOIN操作，仅扫描一次原表，大数据量下性能无额外损耗
• 可根据实际的列名规则调整WHERE条件的过滤逻辑，适配不同的表结构

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