如何在SQL中访问当前创建列的上一行值？类似Qlik的Peek()函数

需求说明 #

需要生成new_order列，规则如下：

• 若当前行id与上一行id不同，new_order取当前行row_order
• 若当前行Type为start，new_order取当前行row_order
• 其他情况，new_order取上一行的new_order值

当前问题 #

现有递归CTE代码输出不符合预期，第7-8行new_order错误使用了上一行的row_order而非new_order，导致结果偏离期望。

当前输出 #

row_order   id  Type    new_order
1           11  start   1
2           11  go      1
3           11  start   3
4           11  go      3
5           12  start   5
6           12  go      5
7           12  go      6
8           12  go      7

期望输出 #

row_order   id  Type    new_order
1           11  start   1
2           11  go      1
3           11  start   3
4           11  go      3
5           12  start   5
6           12  go      5
7           12  go      5
8           12  go      5

尝试过的代码 #

DECLARE @t TABLE (row_order INT, id INT,  Type VARCHAR(100));
INSERT INTO @t VALUES
(1, 11, 'start'),
(2, 11, 'go'),
(3, 11, 'start'),
(4, 11, 'go'),
(5, 12, 'start'),
(6, 12, 'go'),
(7, 12, 'go'),
(8, 12, 'go');

WITH rcte AS (
    SELECT 
        *
        ,row_order AS new_order
    FROM @t
    WHERE row_order = 1
    UNION ALL
    SELECT 
        curr.* 
        ,CASE
            WHEN curr.id <> prev.id THEN curr.row_order
            WHEN curr.Type = 'start' THEN curr.row_order
            ELSE prev.row_order -- 此处错误：应该引用prev.new_order而非prev.row_order
        END AS new_order
    FROM @t AS curr
    JOIN rcte AS prev ON curr.row_order = (prev.row_order + 1)
)
SELECT *
FROM rcte

Qlik中的实现方式 #

在Qlik数据加载编辑器中，可通过Peek()函数直接实现该逻辑。Peek()是Qlik脚本的记录间函数，能在加载数据时访问上一行（或指定行）的字段值，无需表关联即可完成递推计算。

SQL解决方案 #

方案1：修正递归CTE #

核心修改：将ELSE分支的prev.row_order替换为prev.new_order，确保引用的是上一行已计算出的new_order值。

DECLARE @t TABLE (row_order INT, id INT,  Type VARCHAR(100));
INSERT INTO @t VALUES
(1, 11, 'start'),
(2, 11, 'go'),
(3, 11, 'start'),
(4, 11, 'go'),
(5, 12, 'start'),
(6, 12, 'go'),
(7, 12, 'go'),
(8, 12, 'go');

WITH rcte AS (
    SELECT 
        *
        ,row_order AS new_order
    FROM @t
    WHERE row_order = 1
    UNION ALL
    SELECT 
        curr.* 
        ,CASE
            WHEN curr.id <> prev.id THEN curr.row_order
            WHEN curr.Type = 'start' THEN curr.row_order
            ELSE prev.new_order -- 修正为引用上一行的new_order
        END AS new_order
    FROM @t AS curr
    JOIN rcte AS prev ON curr.row_order = (prev.row_order + 1)
)
SELECT row_order, id, Type, new_order
FROM rcte
ORDER BY row_order;

方案2：使用窗口函数（非递归） #

通过标记重置点、分组后取组内首行row_order的方式实现，适合大数据量场景（递归CTE在数据量大时可能存在性能瓶颈）。

DECLARE @t TABLE (row_order INT, id INT,  Type VARCHAR(100));
INSERT INTO @t VALUES
(1, 11, 'start'),
(2, 11, 'go'),
(3, 11, 'start'),
(4, 11, 'go'),
(5, 12, 'start'),
(6, 12, 'go'),
(7, 12, 'go'),
(8, 12, 'go');

WITH flagged AS (
    SELECT 
        *,
        -- 标记需要重置new_order的行
        CASE 
            WHEN id <> LAG(id) OVER(ORDER BY row_order) OR Type = 'start' THEN 1 
            ELSE 0 
        END AS reset_flag
    FROM @t
),
grouped AS (
    SELECT 
        *,
        -- 累加重置标记生成分组ID
        SUM(reset_flag) OVER(ORDER BY row_order ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
    FROM flagged
)
SELECT 
    row_order,
    id,
    Type,
    -- 取每个分组的第一个row_order作为new_order
    FIRST_VALUE(row_order) OVER(PARTITION BY grp ORDER BY row_order) AS new_order
FROM grouped
ORDER BY row_order;

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