SQL按seq列获取事件序列最后一条值 窗口函数实现方案

实现方案 #

你之前使用LAST_VALUE()不生效是因为窗口函数默认的计算框架问题：LAST_VALUE()默认仅统计分区内从第一行到当前行的范围，无法读取当前行之后的记录，自然拿不到整个分组的最后一条值。如果一定要用LAST_VALUE()实现需要手动指定全分区的窗口范围，写法繁琐易出错，更推荐以下两种更简洁的方案：

方案1：用ROW_NUMBER()过滤目标记录 #

先给每个支付单下的金额录入记录按seq倒序排序，取排序第一的就是最后一次录入的金额，再关联获取其他字段即可：

WITH amount_rn AS (
    SELECT
        PaymentID,
        Result AS Amount,
        ROW_NUMBER() OVER(PARTITION BY PaymentID ORDER BY Seq DESC) AS rn
    FROM 表名
    WHERE Description = 'Amount Entered'
)
SELECT
    t.PaymentID,
    MAX(CASE WHEN t.Description = 'Entered Payment Page' THEN t.Result END) AS InPayment,
    a.Amount,
    MAX(CASE WHEN t.Description = 'Amount Confirmed' THEN t.Result END) WITHIN GROUP (ORDER BY t.Seq DESC) AS Confirmed
FROM 表名 t
INNER JOIN amount_rn a ON t.PaymentID = a.PaymentID AND a.rn = 1
GROUP BY t.PaymentID, a.Amount

方案2：用FIRST_VALUE()配合倒序直接取值 #

不需要子查询，直接用窗口函数在同一句查询中取到所有目标值：

SELECT DISTINCT
    PaymentID,
    -- 取第一条进入支付页的结果
    FIRST_VALUE(Result) OVER(PARTITION BY PaymentID ORDER BY Seq) AS InPayment,
    -- 倒序取第一条金额录入记录的结果，就是最后一次录入的金额
    FIRST_VALUE(CASE WHEN Description = 'Amount Entered' THEN Result END) OVER(
        PARTITION BY PaymentID
        ORDER BY CASE WHEN Description = 'Amount Entered' THEN 1 ELSE 2 END, Seq DESC
    ) AS Amount,
    -- 倒序取第一条支付确认记录的结果
    FIRST_VALUE(CASE WHEN Description = 'Amount Confirmed' THEN Result END) OVER(
        PARTITION BY PaymentID
        ORDER BY CASE WHEN Description = 'Amount Confirmed' THEN 1 ELSE 2 END, Seq DESC
    ) AS Confirmed
FROM 表名

两种方案都可以得到你期望的结果，性能差异不大，可以根据你的使用场景选择。

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