sql - 如何为 SQL 中的每个唯一 ID 查找至少发生 3 次、持续时间至少为 15 天但不超过 90 天的观察结果？

Question

假设我有这个表:

CREATE TABLE #t1
(
    PersonID int ,
    ExamDates date,
    Score varchar(50) SPARSE NULL,
);

SET dateformat mdy;

INSERT INTO #t1 (PersonID, ExamDates, Score)
VALUES    (1, '1.1.2018',70),
          (1, '1.13.2018', 100),
          (1, '1.18.2018', 85),
          (2, '1.1.2018', 90),
          (2, '2.1.2018', 95),
          (2, '3.15.2018', 95),
          (2, '7.30.2018', 100),
          (3, '1.1.2018', 80),
          (3, '1.2.2018', 80),
          (3, '5.3.2018', 50),
          (4, '2.1.2018', 90),
          (4, '2.20.2018', 100);

我想为每个唯一 ID 找到至少发生 3 次的观察，跨越至少 15 天但不超过 90 天。
我的决赛 table 应该是这样的:


人名
考试日期
分数


1
1/1/2018
70

1
1/13/2018
100

1
1/18/2018
85

2
1/1/2018
90

2
2/1/2018
95

2
2018/3/15
95


我们有使用 R 来处理此问题的代码，但希望避免将大型数据集拉入 R 中只是为了运行此代码。我们在一个非常大的数据集上这样做，并关注查询的效率。
谢谢!
-彼得

Answer 1

这是 Eli 的想法更简单一些，并将所有繁重的计算移到 cte，在那里它可能更有效:

With cte As (
    Select PersonID, ExamDates
          ,Case When Datediff(DAY,ExamDates, Lead(ExamDates,2,Null) Over (Partition by PersonID Order by ExamDates)) Between 15 and 90
                Then Lead(ExamDates,2,Null) Over (Partition by PersonID Order by ExamDates)
                Else NULL End as EndDateRange
    From #t1
)
Select Distinct B.*
From cte Inner Join #t1 B On B.PersonID=cte.PersonID
                         And B.ExamDates Between cte.ExamDates and cte.EndDateRange

CTE 中的 Case 语句仅在条目两项之后满足总体条件时才返回有效日期；该日期用于与当前记录的 ExamDate 形成一个范围。通过在非限定范围内返回 NULL，我们确保不满足 SQL 外部部分的连接。当在 15-90 天范围内有 4 个以上的连续观察时，需要 Distinct 子句来折叠重复项。