SQL实现：按日期排序获取每个用户第2、3条记录的取值及成本

现有用户行为记录表，样例数据如下：

USER_ID value  date         cost
001         A  01-01-2021     10
002         C  01-01-2021     11
001         D  01-02-2021     21
002         G  01-02-2021     23
001         H  01-03-2021     32

要求以date字段判定记录发生先后顺序，查询每个USER_ID对应的第2次、第3次记录的value取值，同时获取对应记录的cost字段值。

填充规则 #

• 若某USER_ID不存在第3条有效记录，对应的third_val字段填充为NA
• 缺失第三条记录时，匹配的third_cost字段值置为0

期望输出 #

USER_ID   second_val  third_val second_cost  third_cost
001                D          H          21          32
002                G         NA          23           0

使用窗口函数先完成分组内排序打标，再通过条件聚合完成行转列即可得到目标结果，参考代码如下：

WITH ranked_log AS (
    SELECT
        USER_ID,
        value,
        cost,
        ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY date ASC) AS visit_rank
    FROM user_log
)
SELECT
    USER_ID,
    MAX(CASE WHEN visit_rank = 2 THEN value END) AS second_val,
    COALESCE(MAX(CASE WHEN visit_rank = 3 THEN value END), 'NA') AS third_val,
    MAX(CASE WHEN visit_rank = 2 THEN cost END) AS second_cost,
    COALESCE(MAX(CASE WHEN visit_rank = 3 THEN cost END), 0) AS third_cost
FROM ranked_log
WHERE visit_rank IN (2, 3)
GROUP BY USER_ID;

注意：如果存在同一用户同一日期多条记录的场景，可以在排序逻辑中补充主键/精确时间戳字段作为二级排序规则，保证排序结果唯一。

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