SQL查询:行结束日期为下一行开始日期前1天时累加相邻行总天数
连续日期区间有效天数统计实现
统计规则锚定
- 严格按
name维度分区处理,每个用户的所有orderID记录独立校验,绝不跨用户拼接日期区间,保障每个用户的订单数据完整性 - 相邻订单记录的校验标准:仅当当前订单的
end date= 相邻下一条订单Start date的前1天时,两条订单的覆盖日历天数才纳入求和范围 - 相邻记录日期间隔不为1天时,这两条相邻的订单记录全部排除,不参与任何天数统计
示例结果校验
针对给出的Joe Smith的两条订单记录:
- 第一条订单:开始
2020-01-01,结束2020-09-30 - 第二条订单:开始
2020-10-01,结束2020-12-30
- 间隔校验:上一条结束日期
2020-09-30和下一条开始日期2020-10-01刚好间隔1天,符合连续统计要求 - 天数求和:覆盖的总时长为从2020-01-01到2020-12-30的完整日历跨度,合计365天,和预期结果一致
线上可直接运行的查询逻辑
WITH order_sorted_by_user AS ( -- 按用户分组、按订单开始日期升序排列,提前获取相邻下一条订单的开始日期 SELECT name, orderID, `Start date`, `end date`, LEAD(`Start date`) OVER ( PARTITION BY name ORDER BY `Start date` ASC ) AS next_order_start FROM 线上业务订单表 ), valid_order_pairs AS ( -- 筛选满足日期连续性的有效订单行 SELECT name, orderID, -- 计算单条订单覆盖的日历天数(包含开始、结束当日) DATEDIFF(`end date`, `Start date`) + 1 AS single_order_days FROM order_sorted_by_user -- 核心判断:当前订单结束日期 与 下一条订单开始日期间隔刚好1天 WHERE DATEDIFF(next_order_start, `end date`) = 1 ) -- 对所有有效订单的覆盖天数做全局求和 SELECT SUM(single_order_days) AS total_valid_covered_days FROM valid_order_pairs;
执行注意事项
- 针对数千行规模的线上表,窗口函数的排序计算仅需单轮全表扫描,执行耗时通常在秒级,不会对线上业务造成额外压力
- 单个订单如果参与了任意一组不满足“间隔1天”要求的相邻配对,会被直接标记为排除状态,不会参与天数求和;仅当订单所在的相邻配对满足连续要求时,才会纳入统计
- 当单个用户下存在多段无断点的连续订单链时(比如A-B、B-C、C-D全链路相邻间隔都是1天),所有链上的订单都会被识别为有效行,逐行累加天数,不会出现重复计算
- 孤立订单(即用户下仅1条订单、没有可配对的相邻订单)会自动被过滤,完全符合统计规则的排除要求
内容的提问来源于stack exchange,提问作者Drew L.
相关产品推荐
相关产品推荐

