MySQL按月份列统计新用户与复购用户数量实现

基础信息 #

现有订单表结构如下：

CREATE TABLE `order`(
id BIGINT, 
created TIMESTAMP,
user_id INT,
PRIMARY KEY(id)
)

字段说明：

• id：订单主键
• created：订单创建时间戳
• user_id：下单用户ID

需求规则 #

统计维度为自然月，输出两类用户各月数量：

• 新用户：用户首次下单所在自然月内，该用户记为当月新用户
• 复购用户：首次下单月份之后所有产生下单行为的月份，该用户记为当月复购用户
  预期输出格式：

原有初步实现存在明显逻辑缺陷：未做用户维度去重导致重复计数、HAVING子句使用不符合SQL语法逻辑、未关联用户首单时间无法区分用户类型，原计划使用NOT IN+UNION的方案存在多次扫表、匹配效率低的问题。

高效实现SQL #

核心思路：通过窗口函数一次计算出每个用户的首单月份，提前对「用户-月份」维度去重避免重复计数，再通过条件聚合一次性完成两类用户的统计，仅需扫描一次订单表，性能远高于子查询匹配方案。

注：以下示例默认统计单年度1-3月数据，若需跨年度统计，将月份计算逻辑替换为DATE_FORMAT(created, '%Y-%m')格式的年月值即可，避免不同年份同月份数据混淆。

WITH user_order_month AS (
    -- 按用户+下单月去重，同时计算每个用户的首单月份
    SELECT DISTINCT
        user_id,
        MONTH(created) AS order_month,
        MONTH(MIN(created) OVER(PARTITION BY user_id)) AS first_order_month
    FROM `order`
    -- 若需限定统计时间范围，在此加WHERE条件即可，例如统计2024年数据：WHERE YEAR(created) = 2024
)
-- 统计新用户数据
SELECT
    'new' AS `Customer Type`,
    SUM(CASE WHEN order_month = 1 AND order_month = first_order_month THEN 1 ELSE 0 END) AS month_1,
    SUM(CASE WHEN order_month = 2 AND order_month = first_order_month THEN 1 ELSE 0 END) AS month_2,
    SUM(CASE WHEN order_month = 3 AND order_month = first_order_month THEN 1 ELSE 0 END) AS month_3
FROM user_order_month
UNION ALL
-- 统计复购用户数据
SELECT
    'recurrent' AS `Customer Type`,
    SUM(CASE WHEN order_month = 1 AND order_month > first_order_month THEN 1 ELSE 0 END) AS month_1,
    SUM(CASE WHEN order_month = 2 AND order_month > first_order_month THEN 1 ELSE 0 END) AS month_2,
    SUM(CASE WHEN order_month = 3 AND order_month > first_order_month THEN 1 ELSE 0 END) AS month_3
FROM user_order_month;

方案优势 #

• 仅对订单表做一次全表扫描，窗口函数计算首单月份的效率远高于NOT IN/关联子查询的逐行匹配逻辑，数据量越大性能优势越明显
• 提前做用户-月份维度去重，避免同一用户单月多笔订单被重复计数，统计结果准确
• 结构清晰易扩展，后续需要新增统计月份时，仅需添加对应SUM(CASE...)列即可，维护成本低
• 适配性强，仅需调整时间字段的格式化逻辑，即可支持跨年度、季度等不同时间粒度的统计需求

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