SQL 查詢：連續三天某 TYPE 錯誤率 >0 (窗口函數/自連接)

需求

A 表：ID, TYPE, RATE。B 表：ID, TIME。
若 A 表某 ID 的 TYPE 有 RATE>0，查詢該 TYPE 是否**連續三天** (按 B 表 TIME) 出現 RATE>0。返回 1/0。

解法：窗口函數 (SQL Server 2012+)

WITH Filtered AS (
    SELECT a.TYPE, b.TIME
    FROM A a JOIN B b ON a.ID = b.ID
    WHERE a.RATE > 0
),
Flagged AS (
    SELECT TYPE, TIME,
           LAG(TIME, 1) OVER (PARTITION BY TYPE ORDER BY TIME) AS prev1,
           LAG(TIME, 2) OVER (PARTITION BY TYPE ORDER BY TIME) AS prev2
    FROM Filtered
)
SELECT CASE WHEN EXISTS (
    SELECT 1 FROM Flagged
    WHERE DATEDIFF(day, prev2, TIME) = 2  -- 連續三天
    AND DATEDIFF(day, prev1, TIME) = 1
) THEN 1 ELSE 0 END AS Result;

簡化版 (自連接)

SELECT CASE WHEN EXISTS (
    SELECT 1 FROM A a1 JOIN B b1 ON a1.ID=b1.ID
    JOIN A a2 JOIN B b2 ON a2.ID=b2.ID
    JOIN A a3 JOIN B b3 ON a3.ID=b3.ID
    WHERE a1.TYPE=a2.TYPE AND a2.TYPE=a3.TYPE
      AND a1.RATE>0 AND a2.RATE>0 AND a3.RATE>0
      AND DATEDIFF(day, b1.TIME, b2.TIME)=1
      AND DATEDIFF(day, b2.TIME, b3.TIME)=1
) THEN 1 ELSE 0 END;

注意

• B 表 TIME 唯一對應 ID，一天一條記錄。
• 窗口函數效率更高，代碼更清晰。