You need to enable JavaScript to run this app. SQL查询:行结束日期为下一行开始日期前1天时累加相邻行总天数
优惠活动
大模型
产品
解决方案
定价
更多

SQL查询:行结束日期为下一行开始日期前1天时累加相邻行总天数

连续日期区间有效天数统计实现

统计规则锚定

  • 严格按name维度分区处理,每个用户的所有orderID记录独立校验,绝不跨用户拼接日期区间,保障每个用户的订单数据完整性
  • 相邻订单记录的校验标准:仅当当前订单的end date = 相邻下一条订单Start date的前1天时,两条订单的覆盖日历天数才纳入求和范围
  • 相邻记录日期间隔不为1天时,这两条相邻的订单记录全部排除,不参与任何天数统计

示例结果校验

针对给出的Joe Smith的两条订单记录:

  1. 第一条订单:开始2020-01-01,结束2020-09-30
  2. 第二条订单:开始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.

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.28 21:27:04