查找每個id的空值后的第一次出現-有解無憂

有兩個表：A）時間范圍內的所有有效日期 B）事件 id 串列及其發生的相應日期。

dt_util
2022-05-01
2022-05-02
2022-05-04
2022-05-05
2022-05-06
2022-05-07

id_event	dt_event
1	2022-05-01
2	2022-05-01
3	2022-05-01
1	2022-05-02
3	2022-05-02
1	2022-05-04
2	2022-05-04
3	2022-05-04
1	2022-05-05
2	2022-05-05
1	2022-05-07
2	2022-05-07

期望的結果是找到每個 ID 的最后一個“缺失”（NULL）日期之后的最小日期，如下所示：

id_event	min_dt_event
1	2022-05-07
2	2022-05-07
3	2022-05-01

我一直在嘗試使用 FIRST_VALUE 和 ROW_NUMBER 但到目前為止沒有運氣。我知道這可能會為兩個表都回圈，但對于真實的資料集是不可行的。

    -- Creating temp tables
    DROP TABLE IF EXISTS #TEMP_EVENTS
    DROP TABLE IF EXISTS #TEMP_DATES
    CREATE TABLE #TEMP_EVENTS (
        id_event smallint,
        dt_event date
    )
    
    CREATE TABLE #TEMP_DATES (
        dt_util date
    )

    INSERT INTO #TEMP_EVENTS VALUES 
            (1, '2022-05-01'),
            (2, '2022-05-01'),
            (3, '2022-05-01'),
            (1, '2022-05-02'),
            (3, '2022-05-02'),
            (1, '2022-05-04'),
            (2, '2022-05-04'),
            (3, '2022-05-04'),
            (1, '2022-05-05'),
            (2, '2022-05-05'),
            (1, '2022-05-07'),
            (2, '2022-05-07')
            
    INSERT INTO #TEMP_DATES VALUES 
            ('2022-05-01'),
            ('2022-05-02'),
            ('2022-05-04'),
            ('2022-05-05'),
            ('2022-05-06'),
            ('2022-05-07')

    -- Creating fictional table where all ids matches all dates
    DROP TABLE IF EXISTS #TEMP_ID_EVENTS
    SELECT DISTINCT ID_EVENT, DT_UTIL 
    INTO #TEMP_ALL_DATES 
    FROM #TEMP_EVENTS A 
    LEFT JOIN #TEMP_DATES B
    ON 1 = 1
    
    -- Minimum date after a null date
    SELECT 
         A.ID_EVENT, [DT_UTIL], [DT_EVENT],
         FIRST_VALUE([DT_UTIL]) OVER (PARTITION BY A.[ID_EVENT] ORDER BY [DT_EVENT] DESC) AS MIN_DATE
    FROM 
        #TEMP_ALL_DATES A
    LEFT JOIN 
        #TEMP_EVENTS B
    ON  
        A.id_event = B.id_event
    AND A.dt_util  = B.dt_event
    WHERE dt_event IS NULL

uj5u.com熱心網友回復：

子查詢row_number()用于為#TEMP_DATES.

然后通過加入該子查詢date并使用lag()來檢查row_number rn，如果沒有任何缺失的日期，它應該不同于 1。

最后只需獲取max“最后一個缺失的空值”的日期

with cte as
(
    select *,
           flag = case when d.rn <> 1 
                       and  lag(d.rn) over(partition by e.id_event 
                                               order by e.dt_event) <> d.rn - 1
                       then 1
                       else 0
                       end
    from   #TEMP_EVENTS e
           inner join
           (
               select rn = row_number() over (order by dt_util),
                      dt_util
               from   #TEMP_DATES
           ) d             on e.dt_event = d.dt_util
)
select id_event, 
       dt_event = max(case when flag = 1 then dt_event end)
from   cte
group by id_event

結果：

id_event	dt_event
1	2022-05-07
2	2022-05-07
3	空值

db<>小提琴演示

轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/474597.html

標籤：tsql

上一篇：SQLServer2014JSON成表

下一篇：使用T-SQL決議XML資料