计算周期重叠的值

Question

我有一个查询，它生成驾驶员评估的月度报告。一些司机可能几个月都没有报告。报告包含各种违规行为，其中一项违规行为是每月累积的违规行为，并且每年重置，这在单独的查询中完成。一切都工作得很好，除了客户在这部分添加了新的需求。要求是将其从每年重置改为重置任何在一年中超过180天的违规行为。

下面是一个报告的示例(一个驱动程序报告):

RN                   ReportId             DriverId             StartDate               EndDate                 Level1Vio   Lv1YTD
-------------------- -------------------- -------------------- ----------------------- ----------------------- ----------- -----------
1                    64                   2073                 2020-10-21 00:00:00.000 2020-11-21 23:59:59.000 1           1
2                    65                   2073                 2020-11-24 05:13:04.133 2020-12-24 05:13:04.133 0           1
3                    67                   2073                 2020-12-23 06:53:52.870 2021-01-23 06:53:52.870 0           1
4                    68                   2073                 2021-01-22 06:33:43.127 2021-02-22 06:33:43.127 0           1
5                    69                   2073                 2021-02-23 04:02:58.680 2021-03-23 04:02:58.680 1           2
6                    70                   2073                 2021-03-22 23:39:33.570 2021-04-22 23:39:33.570 0           2
7                    71                   2073                 2021-04-22 00:28:35.230 2021-05-22 00:28:35.230 0           2
8                    72                   2073                 2021-05-22 15:46:21.767 2021-06-22 15:46:21.767 1           3
9                    73                   2073                 2021-06-25 06:42:02.130 2021-07-25 06:42:02.130 1           4
10                   76                   2073                 2021-07-23 17:42:01.533 2021-08-23 17:42:01.533 0           4

工作的旧查询是(生成上面的示例):

SELECT 
RN
,   ReportId
,   DriverId
,   StartDate
,   EndDate
,   Level1Vio
,   Lv1YTD = SUM(Level1Vio) OVER(PARTITION BY DriverId ORDER BY ReportId ROWS UNBOUNDED PRECEDING) 
FROM Report

例外的结果应该是(对于新的需求):

RN                   ReportId             DriverId             StartDate               EndDate                 Level1Vio   Lv1YTD
-------------------- -------------------- -------------------- ----------------------- ----------------------- ----------- -----------
1                    64                   2073                 2020-10-21 00:00:00.000 2020-11-21 23:59:59.000 1           1
2                    65                   2073                 2020-11-24 05:13:04.133 2020-12-24 05:13:04.133 0           1
3                    67                   2073                 2020-12-23 06:53:52.870 2021-01-23 06:53:52.870 0           1
4                    68                   2073                 2021-01-22 06:33:43.127 2021-02-22 06:33:43.127 0           1
5                    69                   2073                 2021-02-23 04:02:58.680 2021-03-23 04:02:58.680 1           2
6                    70                   2073                 2021-03-22 23:39:33.570 2021-04-22 23:39:33.570 0           2
7                    71                   2073                 2021-04-22 00:28:35.230 2021-05-22 00:28:35.230 0           1
8                    72                   2073                 2021-05-22 15:46:21.767 2021-06-22 15:46:21.767 1           2
9                    73                   2073                 2021-06-25 06:42:02.130 2021-07-25 06:42:02.130 1           3
10                   76                   2073                 2021-07-23 17:42:01.533 2021-08-23 17:42:01.533 0           3

SQL小提琴

如果您使用ReportId = 64(2020年11月报告)，则驾驶员有登记违规，应在180天后删除，然后在ReportId = 69(2021年3月报告)中再次违规。第一次违规应在ReportId = 71(2021年5月报告)处终止，第二次违规应在ReportId = 76(2021年8月报告)处解除。此原则适用于每一个违规。

尽量实际上，我正在努力避免cursor和while循环，但我找不到一种方法来在一个查询SELECT下做到这一点，就像我在上面的查询中所做的那样。我试过CTE和连接，但没有运气。一切都迫使我使用循环，我尽量避免(因为性能问题)。我确信有一种方法来解决它与简单的SELECT，但我的想法。所以，任何想法或解决方案将不胜感激。

Answer 1

不使用窗口函数，而是直接与重叠180天的记录合并。

更新的小提琴:工作测试用例

SELECT t1.rn, t1.ReportId, t1.DriverId, t1.StartDate, t1.EndDate, t1.Level1Vio
, SUM(t2.Level1Vio) AS Lv1YTD
FROM Report AS t1
JOIN Report AS t2
ON t1.StartDate BETWEEN t2.StartDate AND DATEADD(day, 180, t2.StartDate)
AND t1.DriverId = t2.DriverId
GROUP BY t1.rn, t1.DriverId, t1.ReportId, t1.StartDate, t1.EndDate, t1.Level1Vio
ORDER BY t1.StartDate
;

注:我使用的是自报告违规开始算起的180天。根据需要调整

还要注意:我包含了一个使用CTE术语的版本。如果您希望更方便地过滤连接两边感兴趣的行，将它们限制为特定的年份，或者仅将该年份限制添加到连接逻辑或添加WHERE子句，则可以使用该方法。

有很多方法可以在查询中包含年份逻辑，而不需要使用每年生成的单独查询。