SQL实现滚动180天重置的司机违规累计值计算

解决方案 #

核心思路 #

使用滑动窗口统计逻辑替换原有全量累计逻辑，仅统计当前报告结束日期往前180天内的Level1Vio总和，自动排除超180天的违规记录，无需循环/游标，性能接近原有查询。

方案1：支持日期范围窗口函数的数据库（PostgreSQL、Oracle等） #

直接修改原有窗口函数的排序键和窗口范围即可：

SELECT 
    RN
,   ReportId
,   DriverId
,   StartDate
,   EndDate
,   Level1Vio
,   Lv1YTD = SUM(Level1Vio) OVER(
        PARTITION BY DriverId 
        ORDER BY EndDate 
        RANGE BETWEEN INTERVAL '180 days' PRECEDING AND CURRENT ROW
    )
FROM Report

方案2：全数据库兼容高效写法（支持SQL Server、MySQL 8.0+等不支持日期RANGE窗口的数据库） #

使用OUTER APPLY关联计算180天内的累计值，只要给DriverId、EndDate加联合索引，性能接近原生窗口函数：

SELECT 
    r1.RN
,   r1.ReportId
,   r1.DriverId
,   r1.StartDate
,   r1.EndDate
,   r1.Level1Vio
,   Lv1YTD = SUM(r2.Level1Vio)
FROM Report r1
OUTER APPLY (
    SELECT Level1Vio 
    FROM Report r2 
    WHERE r2.DriverId = r1.DriverId
    AND r2.EndDate >= DATEADD(day, -180, r1.EndDate)
    AND r2.EndDate <= r1.EndDate
) r2
GROUP BY r1.RN, r1.ReportId, r1.DriverId, r1.StartDate, r1.EndDate, r1.Level1Vio

说明 #

• 缺月报告不影响计算逻辑，仅按实际存在的报告记录的日期判断是否在180天范围内
• 完全匹配需求：某条违规对应的报告EndDate超过180天后，后续报告的累计值会自动扣除该条违规数
• 无额外性能损耗，两种方案均为O(n)时间复杂度，远优于循环/游标实现

内容的提问来源于stack exchange，提问作者iSR5