如何获取设备当前记录之后最早的AVLB状态UPDATE_DATE

需要为设备的每条记录，找出该记录之后最早出现的AVLB状态对应的UPDATE_DATE，无后续AVLB则返回NULL。

测试数据 #

+--------+-------------+-------------+------------+
| EQUNR  | UPDATE_DATE | UPDATE_TIME | SYS_STATUS |
+--------+-------------+-------------+------------+
| 111111 | 16/12/2004  |      113943 | AVLB       |
| 111111 | 1/07/2015   |      135327 | AVLB       |
| 111111 | 30/05/2017  |       11628 | INST       |
| 111111 | 17/07/2017  |      154016 | INST       |
| 111111 | 17/07/2017  |      153348 | AVLB       |
| 111111 | 2/08/2020   |       92803 | AVLB       |
| 111111 | 3/08/2020   |      144704 | ESTO       |
| 111111 | 7/08/2020   |       95733 | AVLB       |
| 111111 | 13/04/2021  |      111815 | ESTO       |
| 111111 | 10/05/2021  |      111914 | AVLB       |
| 111111 | 27/08/2021  |      225400 | INST       |
+--------+-------------+-------------+------------+

预期输出 #

+--------+-------------+-------------+------------+-----------------------+
| EQUNR  | UPDATE_DATE | UPDATE_TIME | SYS_STATUS | NEXT_AVLB_UPDATE_DATE |
+--------+-------------+-------------+------------+-----------------------+
| 111111 | 16/12/2004  |      113943 | AVLB       | 1/07/2015             |
| 111111 | 1/07/2015   |      135327 | AVLB       | 17/07/2017            |
| 111111 | 30/05/2017  |       11628 | INST       | 17/07/2017            |
| 111111 | 17/07/2017  |      154016 | INST       | 17/07/2017            |
| 111111 | 17/07/2017  |      153348 | AVLB       | 2/08/2020             |
| 111111 | 2/08/2020   |       92803 | AVLB       | 7/08/2020             |
| 111111 | 3/08/2020   |      144704 | ESTO       | 7/08/2020             |
| 111111 | 7/08/2020   |       95733 | AVLB       | 10/05/2021            |
| 111111 | 13/04/2021  |      111815 | ESTO       | 10/05/2021            |
| 111111 | 10/05/2021  |      111914 | AVLB       | NULL                  |
| 111111 | 27/08/2021  |      225400 | INST       | NULL                  |
+--------+-------------+-------------+------------+-----------------------+

尝试的SQL（未得到预期结果） #

SELECT * 
    , FIRST_VALUE(CASE WHEN SYS_STATUS='AVLB' THEN UPDATE_DATE END) OVER(PARTITION BY EQUNR ORDER BY UPDATE_DATE ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS NEXT_AVLB_STATUS
FROM #temptable
ORDER BY update_date

测试表创建与插入脚本 #

CREATE TABLE #temptable ( [EQUNR] nvarchar(18), [UPDATE_DATE] date, [UPDATE_TIME] nvarchar(6), [SYS_STATUS] nvarchar(4) )

INSERT INTO #temptable ([EQUNR], [UPDATE_DATE], [UPDATE_TIME], [SYS_STATUS])
VALUES
( N'111111 ', N'2004-12-16', N'113943', N'AVLB' ),
( N'111111 ', N'2015-07-01', N'135327', N'AVLB' ),
( N'111111 ', N'2017-05-30', N'011628', N'INST' ),
( N'111111 ', N'2017-07-17', N'154016', N'INST' ),
( N'111111 ', N'2017-07-17', N'153348', N'AVLB' ),
( N'111111 ', N'2020-08-02', N'092803', N'AVLB' ),
( N'111111 ', N'2020-08-03', N'144704', N'ESTO' ),
( N'111111 ', N'2020-08-07', N'095733', N'AVLB' ),
( N'111111 ', N'2021-04-13', N'111815', N'ESTO' ),
( N'111111 ', N'2021-05-10', N'111914', N'AVLB' ),
( N'111111 ', N'2021-08-27', N'225400', N'INST' )

DROP TABLE #temptable

解决方案 #

正确SQL #

使用OUTER APPLY对每条记录关联后续最早的AVLB状态记录，同时考虑同一天内的时间顺序：

SELECT 
    t.*,
    a.NEXT_AVLB_UPDATE_DATE
FROM #temptable t
OUTER APPLY (
    SELECT TOP 1 UPDATE_DATE AS NEXT_AVLB_UPDATE_DATE
    FROM #temptable t2
    WHERE t2.EQUNR = t.EQUNR
      -- 确保后续记录的时间晚于当前记录（日期+时间）
      AND (t2.UPDATE_DATE > t.UPDATE_DATE 
           OR (t2.UPDATE_DATE = t.UPDATE_DATE AND t2.UPDATE_TIME > t.UPDATE_TIME))
      AND t2.SYS_STATUS = 'AVLB'
    -- 按时间升序取最早的一条
    ORDER BY t2.UPDATE_DATE, t2.UPDATE_TIME
) a
ORDER BY t.UPDATE_DATE, t.UPDATE_TIME;

原写法问题说明 #

原SQL使用FIRST_VALUE时，会直接取窗口内第一行的CASE结果：如果窗口第一行不是AVLB状态，CASE返回NULL，FIRST_VALUE就会返回这个NULL，而不会自动跳过NULL去寻找后续的AVLB记录。此外，原排序仅用UPDATE_DATE，未考虑同一天内的UPDATE_TIME，会导致同一天内的记录顺序错误，比如2017-07-17的两条记录，INST的时间晚于AVLB，原排序会把INST排在AVLB前面，进一步导致结果错误。

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