MySQL/Presto替代union all实现循环统计2021年起每月初持卡用户数

核心思路 #

• 第一步：生成2021-01-01到当前月的所有月初日期连续序列，替代手动写死的单月日期
• 第二步：将日期序列和用户开卡表做条件关联，过滤出每个月初对应的有效用户（匹配你原有的时间判断规则：开卡时间<=月初，到期时间>=月初+1天）
• 第三步：按国家、年月分组统计去重用户数即可，无需重复编写union all语句

Presto 语法版本 #

WITH month_list AS (
    -- 生成2021-01-01到当前月的月初日期数组
    SELECT sequence(
        DATE '2021-01-01',
        DATE_TRUNC('month', CURRENT_DATE),
        INTERVAL '1' MONTH
    ) AS month_arr
),
-- 把数组拆解为每行一个月初日期
stat_dates AS (
    SELECT month_start
    FROM month_list
    CROSS JOIN UNNEST(month_arr) AS t(month_start)
)
SELECT
    COALESCE(s.country_id, 1) AS country_id,
    YEAR(stat_dates.month_start) AS year,
    MONTH(stat_dates.month_start) AS month,
    COUNT(DISTINCT s.user) AS total_members
FROM stat_dates
LEFT JOIN your_table s
    ON DATE(CAST(s.start_at AS TIMESTAMP)) <= stat_dates.month_start
    AND DATE(CAST(s.end_at AS TIMESTAMP)) >= DATE_ADD('day', 1, stat_dates.month_start)
GROUP BY 1,2,3
ORDER BY year DESC, month DESC;

MySQL 8.0+ 语法版本 #

WITH RECURSIVE stat_dates AS (
    -- 统计起始月初
    SELECT DATE('2021-01-01') AS month_start
    UNION ALL
    -- 递归生成后续月份月初，直到当前月
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM stat_dates
    WHERE month_start < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
)
SELECT
    COALESCE(s.country_id, 1) AS country_id,
    YEAR(stat_dates.month_start) AS year,
    MONTH(stat_dates.month_start) AS month,
    COUNT(DISTINCT s.user) AS total_members
FROM stat_dates
LEFT JOIN your_table s
    ON DATE(CAST(s.start_at AS DATETIME)) <= stat_dates.month_start
    AND DATE(CAST(s.end_at AS DATETIME)) >= DATE_ADD(stat_dates.month_start, INTERVAL 1 DAY)
GROUP BY 1,2,3
ORDER BY year DESC, month DESC;

注意事项 #

• 替换代码中的your_table为你的实际表名即可直接使用，原有业务逻辑完全保留
• 如需调整统计的起止时间，仅修改CTE部分的日期参数即可，无需新增union语句
• 低于8.0的MySQL版本不支持递归CTE，可手动创建一张存储所有待统计月初日期的临时表，替代stat_dates部分的逻辑

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