Google BigQuery标准SQL中基于前一行值计算字段z的方法问询

对于这种依赖前一行计算结果的递推场景，普通窗口函数（比如LAG()）确实无法满足需求——因为窗口函数只能访问原始表或CTE中已存在的列，无法引用当前查询中正在计算的列的前一行值。在Google BigQuery标准SQL中，我们可以用递归CTE来实现这种逐行的递推逻辑。

完整实现代码 #

下面是针对你给出的复杂计算规则的可行方案，我会用示例数据演示：

WITH original_data AS (
  -- 模拟你的原始表数据，替换成实际表名即可
  SELECT '13:00' AS myTime, 15 AS x, 22 AS y UNION ALL
  SELECT '13:05', 7, 21 UNION ALL
  SELECT '13:10', 7, 5 UNION ALL
  SELECT '13:15', 9, 16 UNION ALL
  SELECT '13:20', 14, 5
),
sorted_rows AS (
  -- 按时间排序并添加行号，确保递推顺序正确
  SELECT *, ROW_NUMBER() OVER (ORDER BY myTime) AS row_num
  FROM original_data
),
recursive_calc AS (
  -- 递归基础：第一行z固定为0
  SELECT myTime, x, y, row_num, 0 AS z
  FROM sorted_rows
  WHERE row_num = 1
  
  UNION ALL
  
  -- 递归逻辑：逐行计算后续z值
  SELECT 
    curr.myTime,
    curr.x,
    curr.y,
    curr.row_num,
    -- 按照规则判断当前行z的值
    CASE
      WHEN curr.x + prev.z < curr.y THEN 0
      ELSE curr.x + prev.z - curr.y
    END AS z
  FROM sorted_rows curr
  JOIN recursive_calc prev ON curr.row_num = prev.row_num + 1
)
-- 输出最终结果
SELECT myTime, x, y, z
FROM recursive_calc
ORDER BY myTime;

代码逻辑说明 #

1. original_data：模拟你的原始表，实际使用时直接替换成你的表名即可。
2. sorted_rows：给数据按myTime排序并添加行号，保证递推时严格按照时间顺序计算。
3. recursive_calc：核心的递归CTE：
   • 基础部分：取出第一行数据，z值固定为0；
   • 递归部分：通过行号关联上一行的计算结果，根据你定义的规则生成当前行的z值：
     • 若x + 前一行z < y，则z=0；
     • 否则z = x + 前一行z - y。

测试结果 #

运行上述代码后，会得到你预期的结果：

+---------+----+----+----+
| myTime | x | y | z |
+---------+----+----+----+
| 13:00 | 15 | 22 | 0 |
| 13:05 | 7 | 21 | 0 |
| 13:10 | 7 | 5 | 2 |
| 13:15 | 9 | 16 | 0 |
| 13:20 | 14 | 5 | 9 |
+---------+----+----+----+

为什么之前的方案失效？ #

你之前尝试的LAG(z,1)方法，问题在于z是当前查询中正在计算的列——窗口函数LAG()只能访问查询执行前就存在的列数据，无法引用同一个SELECT语句中还未生成的计算结果。而递归CTE是逐行构建结果集，每一行的计算都能直接用上一行已经生成的z值，完美解决了这个依赖问题。

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