基于月度的沙龙订阅总数、新增及流失订阅统计SQL查询优化请求

嘿，你的现有查询已经实现了想要的统计需求，但确实存在可以优化的点——比如重复生成日期维度、多次重复调用日期函数，还有冗余的CASE判断逻辑。下面我来分享几个关键优化方向，以及重构后的高效实现：

1. 先搞定更靠谱的日期维度表 #

原查询用两次UNION从订阅表取日期，不仅会产生重复数据，还会漏掉那些没有订阅的空白月份（比如某个月既没有新增也没有流失，但需要统计累计订阅数）。推荐用**递归CTE（MySQL 8.0+支持）**生成连续的月度序列，覆盖从最早订阅开始到当前月份的所有月份，这样统计结果更完整。

2. 预计算重复用到的日期字段 #

把每个订阅的start_month（订阅起始年月）和lost_month（订阅流失年月，也就是end_date加1个月）提前计算好，避免在聚合阶段反复调用DATE_FORMAT和DATE_ADD，能大幅减少函数调用开销，提升查询速度。

3. 简化条件聚合逻辑 #

用SUM(CASE WHEN ... THEN 1 ELSE 0 END)代替COUNT(CASE...)（两者逻辑等价，但前者更直观），同时把套餐ID的判断逻辑统一规整，减少冗余代码。

重构后的完整SQL（MySQL 8.0+） #

WITH RECURSIVE date_dim AS (
    -- 从订阅表取最早的月份作为递归起点
    SELECT DATE_FORMAT(MIN(start_date), '%Y-%m') AS month_date
    FROM subscriptions
    UNION ALL
    -- 递归生成后续月份，直到当前月
    SELECT DATE_FORMAT(DATE_ADD(month_date, INTERVAL 1 MONTH), '%Y-%m')
    FROM date_dim
    WHERE DATE_ADD(month_date, INTERVAL 1 MONTH) <= CURDATE()
),
subscription_monthly AS (
    -- 预计算每个订阅的关键月份字段
    SELECT 
        s.subscription_plan_id,
        DATE_FORMAT(s.start_date, '%Y-%m') AS start_month,
        DATE_FORMAT(DATE_ADD(s.end_date, INTERVAL 1 MONTH), '%Y-%m') AS lost_month
    FROM subscriptions s
)
SELECT 
    dd.month_date AS date,
    -- 累计订阅总数（当月仍在有效期内的订阅）
    SUM(CASE WHEN sm.start_month <= dd.month_date AND sm.lost_month > dd.month_date THEN 1 ELSE 0 END) AS total_subs,
    -- 当月新增订阅数
    SUM(CASE WHEN sm.start_month = dd.month_date THEN 1 ELSE 0 END) AS new_subs,
    -- 当月流失订阅数
    SUM(CASE WHEN sm.lost_month = dd.month_date THEN 1 ELSE 0 END) AS lost_subs,
    -- 各套餐累计订阅总数
    SUM(CASE WHEN sm.start_month <= dd.month_date AND sm.lost_month > dd.month_date AND sm.subscription_plan_id = 4 THEN 1 ELSE 0 END) AS total_standard_subs,
    SUM(CASE WHEN sm.start_month <= dd.month_date AND sm.lost_month > dd.month_date AND sm.subscription_plan_id = 3 THEN 1 ELSE 0 END) AS total_grow_subs,
    SUM(CASE WHEN sm.start_month <= dd.month_date AND sm.lost_month > dd.month_date AND sm.subscription_plan_id = 2 THEN 1 ELSE 0 END) AS total_basic_subs,
    SUM(CASE WHEN sm.start_month <= dd.month_date AND sm.lost_month > dd.month_date AND sm.subscription_plan_id = 1 THEN 1 ELSE 0 END) AS total_free_subs,
    -- 各套餐当月新增订阅数
    SUM(CASE WHEN sm.start_month = dd.month_date AND sm.subscription_plan_id = 4 THEN 1 ELSE 0 END) AS new_standard_subs,
    SUM(CASE WHEN sm.start_month = dd.month_date AND sm.subscription_plan_id = 3 THEN 1 ELSE 0 END) AS new_grow_subs,
    SUM(CASE WHEN sm.start_month = dd.month_date AND sm.subscription_plan_id = 2 THEN 1 ELSE 0 END) AS new_basic_subs,
    SUM(CASE WHEN sm.start_month = dd.month_date AND sm.subscription_plan_id = 1 THEN 1 ELSE 0 END) AS new_free_subs,
    -- 各套餐当月流失订阅数
    SUM(CASE WHEN sm.lost_month = dd.month_date AND sm.subscription_plan_id = 4 THEN 1 ELSE 0 END) AS lost_standard_subs,
    SUM(CASE WHEN sm.lost_month = dd.month_date AND sm.subscription_plan_id = 3 THEN 1 ELSE 0 END) AS lost_grow_subs,
    SUM(CASE WHEN sm.lost_month = dd.month_date AND sm.subscription_plan_id = 2 THEN 1 ELSE 0 END) AS lost_basic_subs,
    SUM(CASE WHEN sm.lost_month = dd.month_date AND sm.subscription_plan_id = 1 THEN 1 ELSE 0 END) AS lost_free_subs
FROM date_dim dd
LEFT JOIN subscription_monthly sm ON 
    (sm.start_month <= dd.month_date AND sm.lost_month > dd.month_date)
    OR sm.start_month = dd.month_date
    OR sm.lost_month = dd.month_date
GROUP BY dd.month_date
ORDER BY dd.month_date DESC;

额外优化小贴士 #

• 如果有专门的subscription_plans表（存储套餐ID和对应名称），可以JOIN该表，用套餐名称代替硬编码的ID，让查询更易维护；
• 要是需要兼容MySQL 5.x（不支持CTE），可以用临时表生成日期维度：

  -- 创建临时日期表
  CREATE TEMPORARY TABLE date_dim (month_date VARCHAR(7));
  SET @start_month = (SELECT DATE_FORMAT(MIN(start_date), '%Y-%m') FROM subscriptions);
  SET @end_month = DATE_FORMAT(CURDATE(), '%Y-%m');
  
  WHILE @start_month <= @end_month DO
      INSERT INTO date_dim VALUES (@start_month);
      SET @start_month = DATE_FORMAT(DATE_ADD(@start_month, INTERVAL 1 MONTH), '%Y-%m');
  END WHILE;
  

• 给subscriptions表加个复合索引，能进一步提升聚合查询的性能：

  CREATE INDEX idx_subs_date_plan ON subscriptions(start_date, end_date, subscription_plan_id);
  

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