Oracle SQL：生成日期范围并统计每日活跃订阅数的方法

核心问题是生成目标日期范围内的连续日期行，再关联订阅表统计每个日期的活跃订阅量。以下分数据库给出具体实现：

一、生成连续日期序列 #

不同数据库的实现方式略有差异，以下是主流数据库的常用方案：

MySQL/MariaDB（8.0+版本） #

使用递归CTE生成日期范围：

WITH RECURSIVE date_range AS (
    SELECT '2023-01-01' AS stat_date  -- 替换为你的起始日期
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE stat_date < '2023-01-07'  -- 替换为你的结束日期
)
SELECT * FROM date_range;

PostgreSQL #

推荐用generate_series函数，更简洁：

SELECT generate_series(
    '2023-01-01'::date, 
    '2023-01-07'::date, 
    '1 day'::interval
) AS stat_date;

也可以用递归CTE实现，适配低版本PostgreSQL：

WITH RECURSIVE date_range AS (
    SELECT '2023-01-01'::date AS stat_date
    UNION ALL
    SELECT stat_date + 1
    FROM date_range
    WHERE stat_date < '2023-01-07'::date
)
SELECT * FROM date_range;

SQL Server #

用递归CTE生成，注意添加递归次数限制：

WITH date_range AS (
    SELECT CAST('2023-01-01' AS DATE) AS stat_date
    UNION ALL
    SELECT DATEADD(DAY, 1, stat_date)
    FROM date_range
    WHERE stat_date < CAST('2023-01-07' AS DATE)
)
SELECT * FROM date_range
OPTION (MAXRECURSION 0);  -- 日期范围超过100天时必须添加

二、完整统计查询 #

以MySQL为例，假设订阅表名为subscriptions，包含active_date（生效日期）、inactive_date（失效日期）字段，统计指定日期范围的每日活跃订阅数：

WITH RECURSIVE date_range AS (
    SELECT '2023-01-01' AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE stat_date < '2023-01-07'
)
SELECT 
    dr.stat_date,
    COUNT(s.id) AS active_subscriptions
FROM date_range dr
LEFT JOIN subscriptions s 
    ON s.active_date <= dr.stat_date 
    AND (s.inactive_date >= dr.stat_date OR s.inactive_date IS NULL)
GROUP BY dr.stat_date
ORDER BY dr.stat_date;

关键逻辑说明 #

• 左连接保证所有日期都会被输出，即使当天没有活跃订阅，计数为0
• 订阅活跃的判定条件：生效日期≤统计日期，且失效日期≥统计日期（或失效日期为空，代表订阅仍在生效）

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