如何用SQL计算每季度平均有效订阅数？

原SQL的问题 #

你的原查询仅按订阅的开始/结束季度分组统计，完全没考虑订阅跨季度的情况（比如一个订阅从2023Q1持续到2023Q3，那么Q1、Q2、Q3都要将该订阅计入有效），统计逻辑无法覆盖所有有效周期，所以结果不准确。

正确实现思路 #

1. 生成所有需要统计的季度维度（包含每个季度的开始、结束日期及天数）
2. 匹配每个订阅与它覆盖的所有季度，判断订阅在该季度是否处于有效状态
3. 按季度分组统计有效订阅总数，再计算季度内的日均有效订阅数（即每季度的平均有效订阅数）

示例SQL（MySQL环境） #

-- 生成所有需要统计的季度维度表
WITH quarters AS (
    SELECT 
        CONCAT(YEAR(q_date), ' Q', QUARTER(q_date)) AS quarter_name,
        DATE_FORMAT(q_date, '%Y-%m-01') AS quarter_start,
        LAST_DAY(STR_TO_DATE(CONCAT(YEAR(q_date), '-', QUARTER(q_date)*3, '-01'), '%Y-%m-%d')) AS quarter_end,
        DAY(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(q_date), '-', QUARTER(q_date)*3, '-01'), '%Y-%m-%d'))) AS quarter_days
    FROM (
        -- 生成从最早订阅开始到最晚订阅结束的所有月份，再聚合为季度
        SELECT DISTINCT DATE_ADD(MIN(start_date), INTERVAL m.month_num MONTH) AS q_date
        FROM work
        CROSS JOIN (
            SELECT 0 AS month_num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
            UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
        ) m
        WHERE DATE_ADD(MIN(start_date), INTERVAL m.month_num MONTH) <= COALESCE(MAX(end_date), CURDATE())
    ) date_range
    GROUP BY YEAR(q_date), QUARTER(q_date)
),
-- 匹配订阅与覆盖的季度，标记有效状态
subscription_quarter AS (
    SELECT 
        q.quarter_name,
        q.quarter_start,
        q.quarter_end,
        q.quarter_days,
        w.subscription_id
    FROM quarters q
    JOIN work w 
        ON w.start_date <= q.quarter_end
        AND (w.end_date >= q.quarter_start OR w.end_date IS NULL)
)
-- 计算每季度的日均有效订阅数
SELECT 
    quarter_name,
    COUNT(DISTINCT subscription_id) AS total_active_subs,
    ROUND(COUNT(DISTINCT subscription_id) / quarter_days, 2) AS avg_daily_active_subs
FROM subscription_quarter
GROUP BY quarter_name, quarter_days
ORDER BY quarter_name;

关键说明 #

• 如果end_date为NULL代表订阅仍在有效状态，SQL中用OR w.end_date IS NULL处理这种场景
• avg_daily_active_subs即为该季度内每天的平均有效订阅数；若需要多个季度之间的有效订阅总数平均值，可在外层再嵌套AVG(total_active_subs)计算
• 若使用PostgreSQL，只需调整日期函数（比如用DATE_TRUNC('quarter', q_date)生成季度开始，(DATE_TRUNC('quarter', q_date) + INTERVAL '3 months - 1 day')生成季度结束）

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