TSQL统计日期重叠场景下不同offer展示数对应的天数

基础规则对齐 #

先明确已知约束与计算规则，避免逻辑偏差：

• 业务表核心字段：ID（客户标识）、[FROM]（区间开始日期）、[TO]（区间结束日期）、[OFFER NUMBER]（优惠编号），其中[FROM]、[TO]为SQL关键字，查询时必须加方括号转义
• 日期计算规则：区间为左闭右开，即包含开始日期、不包含结束日期；标记值9999-12-31统一替换为当前季度最后一天，计算逻辑内联实现，无需声明变量
• 权限限制：不使用DECLARE声明变量、不创建临时表/存储过程，仅用基础T-SQL+通用窗口函数实现
• 统计目标：按ID分组，分别计算每个客户同时享受1/2/3个优惠的累计自然日天数

核心实现逻辑 #

采用事件点扫描法解决区间重叠计数问题，无需逐日期遍历、性能远高于日期表关联方案，步骤如下：

1. 区间标准化：内联计算当前季度最后一天，把所有区间结束时间为9999-12-31的记录替换为季度末，过滤掉开始时间大于等于结束时间的无效区间
2. 事件拆分：把每个有效区间拆成两个事件点：开始日期对应「优惠数+1」，结束日期对应「优惠数-1」
3. 事件排序：按ID分组、事件日期升序排序，同一天的事件优先处理「优惠数-1」的结束事件，匹配左闭右开规则避免重复计数
4. 累计计数：通过窗口函数计算每个时间点的在效优惠总数
5. 时长计算：用窗口函数取同组下一个事件的日期，两个日期的差值即为当前优惠数持续的天数
6. 结果汇总：按ID分组，分别累加优惠数为1/2/3时对应的时长，得到最终统计结果

完整T-SQL代码 #

WITH standard_interval AS (
    -- 标准化日期区间，替换9999-12-31为当前季度最后一天
    SELECT 
        ID,
        [FROM] AS valid_from,
        CASE 
            WHEN [TO] = '99991231' THEN DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 1, -1)
            ELSE [TO]
        END AS valid_to,
        [OFFER NUMBER]
    FROM 你的业务表名 -- 替换为实际业务表名即可
    WHERE [FROM] < CASE 
            WHEN [TO] = '99991231' THEN DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 1, -1)
            ELSE [TO]
        END -- 过滤无效区间
),
event_stream AS (
    -- 拆分开始/结束事件
    SELECT ID, valid_from AS event_date, 1 AS delta FROM standard_interval
    UNION ALL
    SELECT ID, valid_to AS event_date, -1 AS delta FROM standard_interval
),
event_with_cnt AS (
    -- 排序计算每个时间点的累计优惠数
    SELECT 
        ID,
        event_date,
        SUM(delta) OVER (
            PARTITION BY ID 
            ORDER BY event_date, delta -- 同一天先处理结束事件（delta=-1），匹配左闭右开规则
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS current_offer_cnt
    FROM event_stream
),
duration_calc AS (
    -- 计算每个优惠数状态的持续天数
    SELECT 
        ID,
        current_offer_cnt,
        DATEDIFF(
            DAY,
            event_date,
            LEAD(event_date) OVER (PARTITION BY ID ORDER BY event_date, delta)
        ) AS duration_days
    FROM event_with_cnt
)
-- 汇总得到最终结果
SELECT 
    ID,
    SUM(CASE WHEN current_offer_cnt = 1 THEN duration_days ELSE 0 END) AS days_with_1_offer,
    SUM(CASE WHEN current_offer_cnt = 2 THEN duration_days ELSE 0 END) AS days_with_2_offer,
    SUM(CASE WHEN current_offer_cnt = 3 THEN duration_days ELSE 0 END) AS days_with_3_offer
FROM duration_calc
WHERE duration_days > 0 -- 过滤无间隔的相邻事件
GROUP BY ID

ID=3计算逻辑示例 #

以ID=3的样例区间为例，标准化后区间如下：

• 优惠1：2024-01-01 ~ 2024-01-10
• 优惠2：2024-01-05 ~ 2024-01-15
• 优惠3：2024-01-12 ~ 2024-01-20

按上述逻辑计算过程：

最终ID=3的统计结果：1个优惠累计11天，2个优惠累计8天，3个优惠累计0天，完全匹配业务规则。

注意事项 #

• 代码中你的业务表名需要替换为实际环境中的业务表名称
• 如果业务中存在同一ID、同一优惠编号的重叠/相邻区间，可以在standard_interval CTE中提前做区间合并，避免重复计数
• 所有日期计算均采用SQL Server内置函数，无变量声明、无依赖外部对象，符合权限约束要求

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