BigQuery按半年分组批量求和多列的实现方案问询

步骤1：获取需要求和的数值列名 #

通过BigQuery内置的INFORMATION_SCHEMA.COLUMNS系统视图，筛选出目标表中除分组字段、日期字段外的所有数值类型列：

SELECT column_name
FROM `你的项目ID.你的数据集ID.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'trans'
  AND data_type IN ('INT64', 'FLOAT64', 'NUMERIC', 'BIGNUMERIC') -- 仅保留数值类型列
  AND column_name NOT IN ('user_id', 'trans_date') -- 排除分组字段和日期字段（日期字段需替换为表中实际字段名）

步骤2：自动生成SUM语句片段 #

用STRING_AGG函数将列名拼接成带别名的求和语句，同时可通过IFNULL处理空值（避免求和结果为NULL）：

SELECT STRING_AGG(
  CONCAT('IFNULL(SUM(', column_name, '), 0) AS ', column_name),
  ', '
) AS sum_columns
FROM `你的项目ID.你的数据集ID.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'trans'
  AND data_type IN ('INT64', 'FLOAT64', 'NUMERIC', 'BIGNUMERIC')
  AND column_name NOT IN ('user_id', 'trans_date')

执行后会得到类似IFNULL(SUM(col1), 0) AS col1, IFNULL(SUM(col2), 0) AS col2的字符串，这就是自动生成的求和列代码。

步骤3：拼接完整分组查询 #

将上面生成的求和片段替换到以下模板中，同时完成年份、半年的分组逻辑：

SELECT
  user_id,
  EXTRACT(YEAR FROM trans_date) AS year,
  CASE WHEN EXTRACT(MONTH FROM trans_date) <=6 THEN 'H1' ELSE 'H2' END AS half_year,
  -- 替换为步骤2生成的sum_columns字符串
  IFNULL(SUM(col1), 0) AS col1,
  IFNULL(SUM(col2), 0) AS col2,
  ...
FROM `你的项目ID.你的数据集ID.trans`
GROUP BY user_id, year, half_year
ORDER BY user_id, year, half_year

可选：完全自动化执行动态SQL #

如果不想手动复制粘贴，可使用EXECUTE IMMEDIATE直接执行动态生成的完整查询：

DECLARE sum_columns STRING;

SET sum_columns = (
  SELECT STRING_AGG(
    CONCAT('IFNULL(SUM(', column_name, '), 0) AS ', column_name),
    ', '
  )
  FROM `你的项目ID.你的数据集ID.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'trans'
    AND data_type IN ('INT64', 'FLOAT64', 'NUMERIC', 'BIGNUMERIC')
    AND column_name NOT IN ('user_id', 'trans_date')
);

EXECUTE IMMEDIATE CONCAT('
  SELECT
    user_id,
    EXTRACT(YEAR FROM trans_date) AS year,
    CASE WHEN EXTRACT(MONTH FROM trans_date) <=6 THEN ''H1'' ELSE ''H2'' END AS half_year,
    ', sum_columns, '
  FROM `你的项目ID.你的数据集ID.trans`
  GROUP BY user_id, year, half_year
  ORDER BY user_id, year, half_year
');

注意事项 #

• 替换代码中的你的项目ID和你的数据集ID为实际信息
• 如果表中日期字段不是trans_date，需同步替换为表内真实日期列名
• 若无需将NULL转为0，可去掉IFNULL(..., 0)，直接使用SUM(column_name) AS column_name

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