如何在BigQuery中判断ROLLUP分组NULL值并实现GROUPING功能

BigQuery原生支持GROUPING与GROUPING_ID功能，无需提前清洗空值，也无需自定义UDF即可直接使用。

GROUPING函数接受单个分组列作为入参，若当前聚合行的该列是ROLLUP/CUBE操作生成的汇总占位NULL，则返回1，否则返回0；GROUPING_ID可接受多个分组列作为入参，会按入参顺序将每列的GROUPING结果拼接为二进制数后转换为整数返回，可直接用来区分不同的聚合层级。

你提供的示例可以直接改造为如下写法实现需求：

WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, NULL, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  sku,
  day,
  SUM(price) AS total,
  GROUPING(sku) AS is_sku_rolled_up,
  GROUPING(day) AS is_day_rolled_up,
  GROUPING_ID(sku, day) AS rollup_level
FROM Sales
GROUP BY ROLLUP(sku, day)
ORDER BY sku, day;

返回结果中rollup_level字段对应不同聚合层级：

• 0：按sku+day维度分组，行中NULL为原始数据自带的空值
• 1：按sku维度汇总，day字段的NULL为ROLLUP生成的汇总占位
• 3：全量汇总，sku与day的NULL均为ROLLUP生成的汇总占位

即使涉及大量分组列，只需将所有分组列按顺序传入GROUPING_ID即可快速得到聚合层级编码，使用成本极低。

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