BigQuery SQL 实现带负值重置规则的有序累计聚合方案咨询

实现思路 #

• 首先对每个user_id的记录按timestamp升序排序，生成序号方便逐行迭代计算
• 采用递归CTE逐行计算累计值，每次计算时取「上一轮累计值 + 当前行(quantity1 - quantity2)」和0的最大值，实现负值自动重置为0的规则
• 最终可以输出每行的中间累计结果，也可以取每个用户最后一行的累计值作为最终结果

完整SQL代码 #

WITH sample_data AS (
  -- 示例数据，实际使用时替换为你自己的表
  SELECT 'A' AS user_id, DATE('2021-01-10') AS timestamp, 10 AS quantity1, 0 AS quantity2 UNION ALL
  SELECT 'B' AS user_id, DATE('2021-01-17') AS timestamp, 10 AS quantity1, 0 AS quantity2 UNION ALL
  SELECT 'A' AS user_id, DATE('2021-01-19') AS timestamp, 1 AS quantity1, 12 AS quantity2 UNION ALL
  SELECT 'B' AS user_id, DATE('2021-01-25') AS timestamp, 10 AS quantity1, 8 AS quantity2 UNION ALL
  SELECT 'A' AS user_id, DATE('2021-01-27') AS timestamp, 2 AS quantity1, 8 AS quantity2
),
-- 给每个用户的行按时间排序加序号
ranked_data AS (
  SELECT 
    *,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY timestamp ASC) AS rn,
    quantity1 - quantity2 AS delta
  FROM sample_data
),
-- 递归CTE计算累计值
recursive_calc AS (
  -- 基准行：每个用户的第一条记录
  SELECT 
    user_id,
    rn,
    timestamp,
    delta,
    GREATEST(delta, 0) AS running_total
  FROM ranked_data
  WHERE rn = 1
  
  UNION ALL
  
  -- 迭代计算后续行
  SELECT 
    r.user_id,
    r.rn,
    r.timestamp,
    r.delta,
    GREATEST(c.running_total + r.delta, 0) AS running_total
  FROM recursive_calc c
  JOIN ranked_data r 
    ON c.user_id = r.user_id 
    AND c.rn = r.rn - 1
)
-- 输出所有行的中间累计结果，如需最终用户级结果可加QUALIFY rn = MAX(rn) OVER(PARTITION BY user_id)
SELECT user_id, timestamp, delta, running_total
FROM recursive_calc
ORDER BY user_id, rn;

输出结果说明 #

运行以上代码会得到如下结果，完全符合预期的计算逻辑：

如果只需要每个用户的最终累计值，只需要在最后的SELECT语句前加一行QUALIFY rn = MAX(rn) OVER(PARTITION BY user_id)即可，得到用户A最终结果0、用户B最终结果12。

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