如何旋转下一个row_number()

Question

我有一个由此查询填充的温度表：

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             rowNumber
0053       2020-03-20   2019-03-04 17:10:32.837    1
0053       2020-03-20   2019-03-05 17:10:29.480    2
0053       2020-03-20   2019-03-06 17:10:25.940    3
0053       2020-03-20   2019-03-07 17:10:21.930    4
0100       2020-03-25   2019-03-04 17:10:32.837    1
0100       2020-03-25   2019-03-05 17:10:29.480    2
0100       2020-03-25   2019-03-06 17:10:25.940    3
0100       2020-03-25   2019-03-07 17:10:21.930    4

我想拥有 property， stayDate， addedTimeStamp，然后是组中的下一个 addedTimeStamp，如果是最大的返回null或其他...不确定是否有意义...

我的最终目标本质上是从该临时表中获取以下目标：

property   stayDate      firstTimeStamp            secondTimeStamp
0053       2020-03-20    2019-03-04 17:10:32.837   2019-03-05 17:10:29.480
0053       2020-03-20    2019-03-05 17:10:29.480   2019-03-06 17:10:25.940
0053       2020-03-20    2019-03-06 17:10:25.940   2019-03-07 17:10:21.930
0053       2020-03-20    2019-03-06 17:10:25.940   NULL
0100       2020-03-25    2019-03-04 17:10:32.837   2019-03-05 17:10:29.480
0100       2020-03-25    2019-03-05 17:10:29.480   2019-03-06 17:10:25.940
0100       2020-03-25    2019-03-06 17:10:25.940   2019-03-07 17:10:21.930
0100       2020-03-25    2019-03-06 17:10:25.940   NULL

Answer 1

您想要lead()，但您不需要子查询：

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 firstTimeStamp,
       LEAD(added_timestamp) OVER (PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'

Answer 2

这是使用LEAD窗口函数的完美示例

LEAD(firstTimeStamp) OVER 
    (PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp

您可以将LEAD放入主查询中，如果您在其他地方不需要的话，可以删除ROW_NUMBER。

SELECT  att.property, att.stayDate, att.addedTimeStamp, att.rowNumber, att.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,
            LEAD(added_timestamp) OVER 
                (PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
        FROM dbo.tb_rm_portal_attention_days att
            WHERE att.revenue_initiative = 'Test'
    ) att