基于中断事件重置累计的SQL降雨间隔运行总计实现问题
业务场景与计算规则说明
- 数据来源:各城市每日上报天气状态,表中
flag字段用于标识当日天气:flag=1为晴天,flag=0为降雨事件 - 计算目标:逐行计算当前行距离上一次降雨事件的经过天数,计数范围包含当前降雨当日
- 核心规则:
- 每一次降雨事件都会重置累计值
- 每个降雨周期内的累计值独立计算,不累计之前降雨周期的结果
- 计算示例:城市A在2017年1月10日的预期计算结果为2,即统计上次降雨日(1月5日)到当前日期的区间天数
- 现存问题:尝试通过CTE实现该逻辑时,现有代码仅对部分行生效,无法得到全量正确结果
验证用样例表结构与测试数据
CREATE TABLE #Sample ([Date] datetime, [Name] char(1), [flag] bit, [expected_result] int ); GO --drop table #Sample INSERT INTO #Sample VALUES ('1/1/2017','A',0,1), ('1/2/2017','B',0,1), ('1/3/2017','C',1,0), ('1/4/2017','D',0,1), ('1/5/2017','A',1,0), ('1/6/2017','C',1,0), ('1/7/2017','C',1,0), ('1/8/2017','B',0,1), ('1/9/2017','D',1,0), ('1/11/2017','C',0,4), ('1/10/2017','A',0,2), ('1/11/2017','A',0,1), ('1/12/2017','A',1,0), ('1/13/2017','A',1,0), ('1/14/2017','A',0,3), ('1/15/2017','D',0,2), ('1/16/2017','D',1,0); GO SELECT * FROM #Sample order by Name,Date
存在逻辑缺陷的初始实现代码
with t as ( SELECT S1.Date, S1.Name, S1.flag, SUM(CAST(S1.flag AS INT)) OVER(PARTITION BY S1.Name,S1.flag Order BY s1.[Name], S1.Date) AS Sunny_Days, [expected_result] FROM #Sample S1 ) select t.*, CASE WHEN flag=0 AND LAG(flag,1,0) OVER(PARTITION BY Name Order BY [Name], Date)=1 THEN LAG(Sunny_Days,1,0) OVER(PARTITION BY Name Order BY [Name], Date)+1 ELSE 0 END as Days_Transpired_Before_Rain from t order by Name,Date
初始代码的核心问题
- 窗口分区逻辑错误:按
Name和flag双字段做窗口分区,导致同城市不同天气状态的记录被拆分到独立计算窗口,跨状态的累计计算完全失效 - 条件判断覆盖不全:仅当当前行
flag=0且上一行flag=1时才触发计算,其余场景(连续降雨、多日间隔的降雨记录)全部返回0,和预期逻辑不符 - 累计值计算偏差:没有基于降雨事件做分组边界切割,无法实现“降雨即重置累计”的规则,导致累计值跨周期累加
内容的提问来源于stack exchange,提问作者MasterShifu
相关产品推荐
相关产品推荐

