如何基于活动起止日期使用SQL统计每日活动数量？

需求 #

统计每日处于活跃状态的唯一活动数量，活跃状态定义为日期在活动的起止日期之间（含起止日）。

输入表（表名：campaigns） #

期望输出 #

SQL实现方案 #

要实现这个需求，核心步骤是：

1. 生成覆盖目标日期范围的连续日期序列；
2. 将日期序列与活动表关联，筛选出每个日期处于活跃期的活动；
3. 按日期分组，统计唯一活动的数量。

下面是不同主流数据库的具体实现代码：

PostgreSQL #

-- 生成2022-07-02到2022-07-18的连续日期
WITH date_series AS (
    SELECT generate_series(
        '2022-07-02'::DATE,
        '2022-07-18'::DATE,
        '1 day'::INTERVAL
    ) AS date
),
active_campaigns AS (
    SELECT 
        ds.date::DATE,
        c."Campaign name"
    FROM date_series ds
    LEFT JOIN campaigns c
        ON ds.date BETWEEN c."Start date" AND c."End date"
)
SELECT 
    date AS "Date",
    COUNT(DISTINCT "Campaign name") AS "Count unique campaigns"
FROM active_campaigns
GROUP BY date
ORDER BY date;

MySQL 8.0+ #

-- 递归生成连续日期序列
WITH RECURSIVE date_series AS (
    SELECT '2022-07-02' AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_series
    WHERE date < '2022-07-18'
),
active_campaigns AS (
    SELECT 
        ds.date,
        c.`Campaign name`
    FROM date_series ds
    LEFT JOIN campaigns c
        ON ds.date BETWEEN c.`Start date` AND c.`End date`
)
SELECT 
    date AS `Date`,
    COUNT(DISTINCT `Campaign name`) AS `Count unique campaigns`
FROM active_campaigns
GROUP BY date
ORDER BY date;

SQL Server #

-- 递归生成连续日期序列
WITH date_series AS (
    SELECT CAST('2022-07-02' AS DATE) AS date
    UNION ALL
    SELECT DATEADD(DAY, 1, date)
    FROM date_series
    WHERE date < CAST('2022-07-18' AS DATE)
),
active_campaigns AS (
    SELECT 
        ds.date,
        c.[Campaign name]
    FROM date_series ds
    LEFT JOIN campaigns c
        ON ds.date BETWEEN c.[Start date] AND c.[End date]
)
SELECT 
    date AS [Date],
    COUNT(DISTINCT [Campaign name]) AS [Count unique campaigns]
FROM active_campaigns
GROUP BY date
ORDER BY date
OPTION (MAXRECURSION 0); -- 解除递归层数限制，避免默认100层的限制

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