如何基于ROW_NUMBER()获取分组内下一条记录的时间戳

问题描述 #

我通过以下SQL生成了一个临时表：

SELECT att.property, att.stayDate, att.addedTimeStamp, att.rowNumber 
FROM ( 
    SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)),4) AS property, 
           CAST(att.stay_date AS DATE) AS stayDate, 
           CAST(added_timestamp AS DATETIME) AS addedTimeStamp, 
           ROW_NUMBER() OVER(PARTITION BY property, stay_date ORDER BY added_timestamp) AS rowNumber 
    FROM dbo.tb_rm_portal_attention_days att 
    WHERE att.revenue_initiative = 'Test' 
) att

临时表的数据如下：

我需要从该临时表中获取property、stayDate字段，将当前记录的addedTimeStamp作为firstTimeStamp，分组内下一条记录的addedTimeStamp作为secondTimeStamp，当为分组内最后一条记录时，secondTimeStamp设为NULL，预期输出格式如下：

解决方案 #

这刚好是SQL窗口函数LEAD()的典型应用场景！LEAD()可以在同一个分组（分区）内，获取当前行之后指定偏移量的行的字段值，默认偏移量为1（也就是下一行），当没有下一行时会返回NULL，完全匹配你的需求。

你只需要在原查询的外层加入LEAD()函数即可，完整SQL如下：

SELECT 
    att.property, 
    att.stayDate, 
    att.addedTimeStamp AS firstTimeStamp,
    -- 使用LEAD获取分组内下一条记录的addedTimeStamp，无下一条则返回NULL
    LEAD(att.addedTimeStamp) OVER(PARTITION BY att.property, att.stayDate ORDER BY att.rowNumber) AS secondTimeStamp
FROM ( 
    SELECT 
        RIGHT('000' + CAST(att.property AS VARCHAR(4)),4) AS property, 
        CAST(att.stay_date AS DATE) AS stayDate, 
        CAST(added_timestamp AS DATETIME) AS addedTimeStamp, 
        ROW_NUMBER() OVER(PARTITION BY property, stay_date ORDER BY added_timestamp) AS rowNumber 
    FROM dbo.tb_rm_portal_attention_days att 
    WHERE att.revenue_initiative = 'Test' 
) att
ORDER BY att.property, att.stayDate, att.rowNumber;

说明 #

1. 内层子查询保持你原来的逻辑，生成包含rowNumber的中间结果；
2. 外层查询中，LEAD(att.addedTimeStamp)指定要获取下一行的addedTimeStamp；
3. PARTITION BY att.property, att.stayDate确保只在同一个酒店（property）和入住日期（stayDate）的分组内查找下一行；
4. ORDER BY att.rowNumber保证查找的是按时间顺序的下一行（因为rowNumber本来就是按added_timestamp排序生成的，也可以直接写ORDER BY att.addedTimeStamp，结果一致）；
5. 最后的ORDER BY是为了让输出结果和你预期的顺序完全匹配。

另外注意：你给出的预期输出中最后一条记录的firstTimeStamp存在笔误，使用上述SQL会得到正确的结果（最后一条的firstTimeStamp为分组内最晚的addedTimeStamp）。

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