MySQL 8.2.12中统计客户订阅有效天数（去重重叠时段）

针对你的subscription表（含id、customer_id、start_at、end_at字段，无空end_at），要统计指定时段内每个客户的去重有效订阅天数（排除重叠时段重复计数），可以用MySQL 8.0+的窗口函数实现精准且高效的计算，避免之前方案的重复计数、误算间隔及超时问题。

核心思路 #

• 按客户分组，对订阅记录按start_at排序，识别出连续/重叠的时段组；
• 合并每个组的起始和结束时间，得到无重叠的有效时段；
• 计算每个合并后时段与指定统计时段的交集天数，最后按客户汇总总和。

具体SQL实现 #

假设要统计的指定时段是2023年全年（'2023-01-01'到'2023-12-31'），代码如下：

WITH ranked_subs AS (
    -- 按客户分组，标记重叠/连续的订阅时段组
    SELECT 
        customer_id,
        start_at,
        end_at,
        -- 当前订阅开始时间晚于上一条订阅结束时间时，标记为新组
        SUM(CASE WHEN start_at > LAG(end_at) OVER (PARTITION BY customer_id ORDER BY start_at) THEN 1 ELSE 0 END) OVER (PARTITION BY customer_id ORDER BY start_at) AS group_id
    FROM subscription
    -- 先过滤完全不在统计时段内的订阅，减少计算量
    WHERE end_at >= '2023-01-01' 
      AND start_at <= '2023-12-31'
),
merged_periods AS (
    -- 合并同组的订阅时段，得到无重叠的有效区间
    SELECT 
        customer_id,
        MIN(start_at) AS period_start,
        MAX(end_at) AS period_end
    FROM ranked_subs
    GROUP BY customer_id, group_id
)
-- 计算交集天数并汇总客户总有效天数
SELECT 
    customer_id,
    SUM(
        DATEDIFF(
            LEAST(period_end, '2023-12-31'),
            GREATEST(period_start, '2023-01-01')
        ) + 1  -- +1修正天数差计数，比如当日起止算1天
    ) AS valid_sub_days
FROM merged_periods
GROUP BY customer_id
ORDER BY customer_id;

代码说明 #

• 前置过滤：提前排除完全在统计时段外的订阅，减少后续计算数据量，避免504超时；
• 重叠分组：用LAG窗口函数对比当前与上一条订阅的时间边界，将重叠/连续的订阅归为同一组；
• 时段合并：对同组订阅取最早起始和最晚结束时间，消除重叠；
• 交集计算：用LEAST和GREATEST锁定合并时段与统计时段的重叠区间，再通过DATEDIFF计算有效天数。

灵活调整 #

若需统计其他时段，只需替换代码中所有的'2023-01-01'和'2023-12-31'为目标时段的起止日期即可。

内容的提问来源于stack exchange，提问作者Loïc Chabut