按Campaign统计日期范围内唯一天数总和（排除重叠日期重复计算）

现有一组包含重复、重叠日期区间的数据集，需要按campaign维度统计每个活动的唯一天数总和——重叠的日期区间不能重复计算。比如campaign_a里2022/07/08~2022/07/15和2022/07/11~2022/07/13的重叠部分，只能算一次。

原始数据集 #

实现方案（SQL） #

以下以PostgreSQL为例，通过CTE逐步合并重叠区间，计算唯一天数：

-- 1. 去重并转换日期格式
WITH deduplicated AS (
    SELECT DISTINCT
        campaign,
        TO_DATE(line_start, 'DD/MM/YYYY') AS start_date,
        TO_DATE(line_end, 'DD/MM/YYYY') AS end_date
    FROM your_table
),
-- 2. 标记重叠区间分组
grouped_intervals AS (
    SELECT
        campaign,
        start_date,
        end_date,
        SUM(CASE WHEN start_date > LAG(end_date) OVER (PARTITION BY campaign ORDER BY start_date) THEN 1 ELSE 0 END) 
            OVER (PARTITION BY campaign ORDER BY start_date) AS interval_group
    FROM deduplicated
),
-- 3. 合并同组内的重叠区间
merged_intervals AS (
    SELECT
        campaign,
        start_date,
        MAX(end_date) OVER (
            PARTITION BY campaign, interval_group
            ORDER BY start_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS merged_end
    FROM grouped_intervals
),
-- 4. 去重合并后的区间
unique_intervals AS (
    SELECT DISTINCT campaign, start_date, merged_end
    FROM merged_intervals
)
-- 5. 计算每个活动的唯一天数总和
SELECT
    campaign,
    SUM(merged_end - start_date + 1) AS unique_total_days
FROM unique_intervals
GROUP BY campaign;

最终结果 #

执行后会得到：

• campaign_a：177天（合并后区间为2022-07-08至2022-12-31）
• campaign_b：177天（合并后区间与campaign_a一致）

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