递归CTE中引用前N行实现自定义test列逻辑的技术问题

需求规则 #

需要计算test列，逻辑如下：

• 当value1=1时：
  • 若前两行的test值不全为1，test设为1
  • 若前两行的test值均为1，test设为0
• 当value1=0时，test直接设为0
• 初始行（index=1）的test固定为0

问题分析 #

原递归CTE仅关联前一行数据，无法获取前两行的test值；且窗口函数（如LAG）不能在递归CTE的递归分支中使用。因此需要调整递归结构，在递归过程中保留前一行的test值，从而间接获取前两行的状态。

修正后的SQL代码 #

with data (index, value1) as (
     select * from values
     (1,1),
     (2,1),
     (3,1),
     (4,1),
     (5,0),
     (6,0),
     (7,1),
     (8,1),
     (9,0),
     (10,1)
),
cte AS (
    -- 锚点成员：初始化第一行数据，新增prev_test存储前一行test（无前置则设为0）
    SELECT
        index,
        value1,
        0 AS test,
        0 AS prev_test
    FROM data
    WHERE index = 1
    
    UNION ALL
    
    -- 递归成员：关联上一行，同时获取前一行的test和前两行的test（通过prev_test）
    SELECT 
        a.index,
        a.value1,
        CASE
            WHEN a.value1 = 0 THEN 0
            WHEN b.test = 1 AND b.prev_test = 1 THEN 0
            ELSE 1
        END AS test,
        b.test AS prev_test
    FROM data as a
    INNER JOIN cte as b
        ON b.index + 1 = a.index
)
-- 输出目标列并按index排序
SELECT index, value1, test
FROM cte
ORDER BY index;

代码逻辑说明 #

1. 锚点成员：初始化index=1的行，test设为0，新增prev_test字段（用于存储前一行的test值），因无前置行，设为0。
2. 递归成员：
   • 通过b.index + 1 = a.index关联上一行的CTE结果，保证行顺序。
   • 对当前行a：
     • 若value1=0，直接返回test=0。
     • 若value1=1，检查上一行的test（当前行的前一行test）和上一行的prev_test（当前行的前两行test）是否均为1，若是则test=0，否则test=1。
     • 将上一行的test赋值给当前行的prev_test，供下一次递归使用。
3. 最终查询：仅取出需要的index、value1、test列，并按index排序，输出结果与预期一致。

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