订阅事件表按月区分新客与续费用户的统计逻辑实现求解

你可以通过3步完成统计，以下是通用SQL实现逻辑，适配绝大多数关系型数据库：

• 第一步：提取每个用户的首次订阅时间
  先筛选所有类型为NEW的事件，按User_id分组，拿到每个用户最早的订阅日期，记为first_sub_date
• 第二步：标记每条事件的所属场景
  将原表和第一步拿到的用户首次订阅表关联，对比每条事件的日期和用户首次订阅日期：
  日期相等则标记为新订阅，否则标记为续费
• 第三步：按月份分组汇总统计
  按事件日期的年月维度分组，分别统计两类事件的数量即可

示例SQL（以MySQL为例） #

WITH user_first_sub AS (
    -- 拿到每个用户的首次订阅日期
    SELECT 
        User_id,
        MIN(STR_TO_DATE(Date, '%d/%m/%Y')) AS first_sub_date
    FROM 订阅事件表
    WHERE Event = 'NEW'
    GROUP BY User_id
),
event_with_type AS (
    -- 给每条NEW事件标记是新订阅还是续费
    SELECT
        STR_TO_DATE(t1.Date, '%d/%m/%Y') AS event_date,
        CASE WHEN STR_TO_DATE(t1.Date, '%d/%m/%Y') = t2.first_sub_date THEN 1 ELSE 0 END AS is_new,
        CASE WHEN STR_TO_DATE(t1.Date, '%d/%m/%Y') != t2.first_sub_date THEN 1 ELSE 0 END AS is_renewal
    FROM 订阅事件表 t1
    LEFT JOIN user_first_sub t2 ON t1.User_id = t2.User_id
    WHERE t1.Event = 'NEW'
)
-- 按月汇总输出
SELECT
    DATE_FORMAT(event_date, '%b %y') AS month,
    SUM(is_new) AS 新订阅数,
    SUM(is_renewal) AS 续费数
FROM event_with_type
GROUP BY DATE_FORMAT(event_date, '%Y-%m'), DATE_FORMAT(event_date, '%b %y')
ORDER BY event_date DESC;

结果验证 #

针对你给出的示例数据，执行上述SQL后会得到对应结果：

和你期望的输出结果完全匹配。

注：如果你使用的数据库不支持CTE语法（比如低版本MySQL），可以把CTE改成子查询实现，逻辑完全一致。

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