BigQuery如何按日计算多行数值差值并新增OTHER结果行

以下写法默认原始表结构为：

• dt：统计日期
• product：产品维度，现有取值为A、B、C、TOTAL
• metric_value：对应统计指标值

实现逻辑 #

不需要复杂嵌套，核心是按日期维度拿到当日TOTAL值与A/B/C三类产品的数值总和，二者差值即为OTHER产品的当日指标，最后将计算出的OTHER行与原始表数据合并即可。

写法1：CTE聚合实现（推荐，易维护） #

逻辑清晰，后续调整计算规则的修改成本低，适合生产环境使用：

WITH daily_agg AS (
  SELECT
    dt,
    MAX(IF(product = 'TOTAL', metric_value, NULL)) AS daily_total,
    SUM(IF(product IN ('A', 'B', 'C'), metric_value, 0)) AS abc_total
  FROM `你的项目名.数据集名.你的原始表名` -- 替换为实际表地址
  GROUP BY dt
)
-- 读取原始表所有存量数据
SELECT dt, product, metric_value FROM `你的项目名.数据集名.你的原始表名`
UNION ALL
-- 拼接计算得到的OTHER产品行
SELECT
  dt,
  'OTHER' AS product,
  daily_total - abc_total AS metric_value
FROM daily_agg

写法2：窗口函数实现（匹配你最初的实现思路） #

不需要单独做分组聚合，直接通过分区窗口函数计算同日期下的数值差，代码更简洁：

SELECT dt, product, metric_value FROM `你的项目名.数据集名.你的原始表名`
UNION ALL
SELECT DISTINCT
  dt,
  'OTHER' AS product,
  MAX(IF(product = 'TOTAL', metric_value, NULL)) OVER (PARTITION BY dt)
  - SUM(IF(product IN ('A', 'B', 'C'), metric_value, 0)) OVER (PARTITION BY dt) AS metric_value
FROM `你的项目名.数据集名.你的原始表名`

校验提示：如果运行后发现OTHER值为0或者负数，优先检查原始表中TOTAL行的统计口径是否包含OTHER、以及A/B/C三类产品的枚举值是否写全，避免漏算产品导致差值错误。

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