確定給定視窗內的連續天數

鑒于以下兩個表：

CREATE TABLE #t1 (ID varchar(1), SpecDate date)
CREATE TABLE #t2 (ID varchar(1), Alert varchar(1), AlertDate date)

INSERT INTO #t1 (ID, SpecDate) VALUES ('A', '2021-05-10')
INSERT INTO #t1 (ID, SpecDate) VALUES ('B', '2021-05-10')
INSERT INTO #t1 (ID, SpecDate) VALUES ('B', '2021-05-12')

INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'N', '2021-05-06')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'Y', '2021-05-06')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'Y', '2021-05-07')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'Y', '2021-05-08')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'N', '2021-05-09')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'N', '2021-05-10')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('A', 'Y', '2021-05-11')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'N', '2021-05-07')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'Y', '2021-05-08')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'N', '2021-05-09')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'N', '2021-05-10')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'N', '2021-05-11')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'N', '2021-05-12')
INSERT INTO #t2 (ID, Alert, AlertDate) VALUES ('B', 'N', '2021-05-13')

#t1：

#t2：

我需要從#t2 中找到連續“Y”警報的計數，其中至少有 1 個“Y”在 2 天前到等于 #t1 的 SpecDate，忽略 SpecDate 之后的任何內容并按 ID 分組。每個 ID 和 AlertDate 可以有多個 Alert 值（如果傳感器被移動）。我正在尋找的最終輸出是：

我已經在指定的視窗中找到了“Y”，但無法弄清楚如何連續計數，尤其是當計數離開視窗時：

SELECT DISTINCT t1.ID
    , t1.SpecDate
    , t2.Alert
FROM #t1 AS t1
INNER JOIN #t2 AS t2
    ON t1.ID = t2.ID
WHERE t2.Alert = 'Y' AND
    t2.AlertDate BETWEEN  DATEADD(DAY, -2, t1.SpecDate) AND t1.SpecDate

Edit I was trying to keep the problem as simple and generic as possible, but I think some context might help. I'm working with healthcare data and, for a given day (#t1), I need to know if a patient has had a urinary catheter within the last two days. If yes, then I need to know how long they have had it. #t2 has a daily flag 'Y'/'N' indicating if they had one. There is always at least one daily record, but there can be multiple entries if the patient transfers or has a catheter status changes.

uj5u.com熱心網友回復：

縫隙和島嶼問題。如果每天可能有多行，聽起來您需要先聚合：

with t2 as (
    select ID, AlertDate, max(Alert) as Alert
    from #t2
    group by ID, AlertDate
), data as (
    select t1.ID, t1.SpecDate,
        row_number() over (partition by t1.ID order by t2.AlertDate) as rn,
        datediff(day, t1.SpecDate, t2.AlertDate) as dn,
        min(t2.AlertDate) over (partition by t1.ID, t1.SpecDate) as firstAlert  
    from #t1 t1 inner join #t2 t2
        on t2.ID = t1.ID
            and t2.Alert = 'Y'
            and t2.AlertDate >= dateadd(day, -2, t1.SpecDate)
), streaks as (
    select ID, SpecDate, count(*) as cnt
    from data
    where firstAlert <= SpecDate
    group by ID, SpecDate, dn - rn
)
select ID, SpecDate, max(cnt) as ConsecutiveAlertDays
from streaks
group by ID, SpecDate;

uj5u.com熱心網友回復：

我認為這就是您所追求的 - 感謝您提供表結構和示例資料，但我懷疑您會有其他未包括的邊緣情況：

;WITH src AS -- group in case more than one 'Y' on a day
(
  SELECT ID, AlertDate
    FROM #t2 
    WHERE Alert = 'Y'
    GROUP BY ID, AlertDate
),
groups AS -- make some islands of consecutive days
(
  SELECT ID, AlertDate, day_group = DATEADD(DAY, 
    1-ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AlertDate), AlertDate)
  FROM src
),
agg AS -- get the count and last day in each island
(
  SELECT ID, AlertDate, 
    c = COUNT(*)       OVER (PARTITION BY ID, day_group),
    m = MAX(AlertDate) OVER (PARTITION BY ID, day_group) 
  FROM groups
)
SELECT t1.ID, t1.SpecDate, ConsecutiveAlertDays = agg.c
  FROM agg
  INNER JOIN #t1 AS t1
    ON agg.m >= DATEADD(DAY, -2, t1.SpecDate)
   AND agg.m <= t1.SpecDate
   AND t1.ID = agg.ID
   AND agg.m = agg.AlertDate
ORDER BY t1.ID, t1.SpecDate; 

結果：

• 示例資料庫<>小提琴

標籤：sql sql-server tsql

上一篇：通過索引和/或ID有條件地連接到表

下一篇：您如何計算每天使用日期時間列創建的行數？