sql - 获得有条件的连续天数

Question

有一个包含三列的表格:

CREATE TABLE #t1 ( Id INT
                  ,VisitDate DATE
                  ,Counter INT)

和测试数据:

INSERT INTO #t1 VALUES (1,'2019-01-01', 50)
INSERT INTO #t1 VALUES (2,'2019-01-02', 15)
INSERT INTO #t1 VALUES (3,'2019-01-03', 7)
INSERT INTO #t1 VALUES (4,'2019-01-04', 7)
INSERT INTO #t1 VALUES (5,'2019-01-05', 18)
INSERT INTO #t1 VALUES (6,'2019-01-06', 19)
INSERT INTO #t1 VALUES (7,'2019-01-07', 11)
INSERT INTO #t1 VALUES (8,'2019-01-08', 1)
INSERT INTO #t1 VALUES (9,'2019-01-09', 19)

需要找到连续三天以上的计数器大于或等于十的地方:

Id  VisitDate   Counter
5   2019-01-05  18
6   2019-01-06  19
7   2019-01-07  11

我的SELECT语句是

;WITH cte AS 
(
    SELECT *
    ,IIF(Counter > 10, 1,0) AS MoreThanTen
    FROM   #t1
), lag_lead_cte AS
(
    SELECT *
    ,LAG(MoreThanTen) OVER (ORDER BY VisitDate) AS LagShift
    ,(LAG(MoreThanTen) OVER (ORDER BY VisitDate) +  MoreThanTen ) AS LagMoreThanTen
    ,LEAD(MoreThanTen) OVER (ORDER BY VisitDate) AS LeadShift
    ,(LEAD(MoreThanTen) OVER (ORDER BY VisitDate) +  MoreThanTen ) AS LeadMoreThanTen
    FROM cte
)
 SELECT *
 FROM    lag_lead_cte
 WHERE   LagMoreThanTen = 2 OR LeadMoreThanTen = 2

但结果并不完全一致

Id  VisitDate   Counter
1   2019-01-01  50
2   2019-01-02  15
5   2019-01-05  18
6   2019-01-06  19
7   2019-01-07  11

Answer 1

这看起来像是一个缺口和孤岛问题。这是一种方法。我假设 SQL Server 基于 T-SQL 标记。

逐个 CTE 运行此查询并检查中间结果以了解其工作原理。

查询

WITH
CTE_rn
AS
(
    SELECT *
        ,CASE WHEN Counter>10 THEN 1 ELSE 0 END AS MoreThanTen
        ,ROW_NUMBER() OVER (ORDER BY VisitDate) AS rn1
        ,ROW_NUMBER() OVER (PARTITION BY CASE WHEN Counter>10 THEN 1 ELSE 0 END ORDER BY VisitDate) AS rn2
    FROM #t1
)
,CTE_Groups
AS
(
    SELECT
        *
        ,rn1-rn2 AS Diff
        ,COUNT(*) OVER (PARTITION BY MoreThanTen, rn1-rn2) AS GroupLength
    FROM CTE_rn
)
SELECT
    ID
    ,VisitDate
    ,Counter
FROM CTE_Groups
WHERE
    GroupLength >= 3
    AND Counter > 10
ORDER BY VisitDate
;

结果

+----+------------+---------+
| ID | VisitDate  | Counter |
+----+------------+---------+
|  5 | 2019-01-05 |      18 |
|  6 | 2019-01-06 |      19 |
|  7 | 2019-01-07 |      11 |
+----+------------+---------+