如何用MySQL按国家按月统计首次购买用户数量？

要实现按月份统计各国家首次购买用户数的需求，需要分三步处理：先提取每个用户的首次有效下单记录，再按月份和国家统计用户数，最后将统计结果行转列展示。

完整查询语句 #

-- 第一步：获取每个用户的首次有效下单信息
WITH user_first_orders AS (
    SELECT 
        email,
        country,
        MIN(order_date) AS first_order_date
    FROM pinkpanda.shop_orders
    WHERE 
        deleted = 'false'
        AND shipping_date <> '0000-00-00 00:00:00'
        AND status = 'sent'
    GROUP BY email, country
),
-- 第二步：按月份和国家统计首次用户数
monthly_country_stats AS (
    SELECT
        DATE_FORMAT(first_order_date, '%b %Y') AS month,
        country,
        COUNT(DISTINCT email) AS user_count
    FROM user_first_orders
    GROUP BY month, country
    ORDER BY STR_TO_DATE(month, '%b %Y')
)
-- 第三步：行转列，将国家转为单独列展示
SELECT
    month,
    SUM(CASE WHEN country = '_it' THEN user_count ELSE 0 END) AS '_it',
    SUM(CASE WHEN country = '_hu' THEN user_count ELSE 0 END) AS '_hu',
    SUM(CASE WHEN country = '_ro' THEN user_count ELSE 0 END) AS '_ro'
FROM monthly_country_stats
GROUP BY month
ORDER BY STR_TO_DATE(month, '%b %Y');

语句拆解说明 #

1. 获取首次下单记录

   • 通过MIN(order_date)锁定每个用户的首次下单时间，同时过滤掉无效订单（已删除、未发货、状态非sent的订单）。
   • GROUP BY email, country：若同一邮箱在不同国家有首次下单，会分别统计（如果业务上同一邮箱跨国家算同一个用户，可去掉country，示例数据中每个邮箱对应唯一国家，不影响结果）。

2. 按月份和国家统计

   • 用DATE_FORMAT将日期转为Jan 2022的格式，方便展示。
   • STR_TO_DATE用于排序时将月份字符串转回日期，保证月份按时间顺序排列。

3. 行转列展示结果

   • 用CASE WHEN配合SUM将每个国家的统计数转为单独列，无数据的月份国家自动填充0，匹配预期输出格式。

适配示例数据的输出结果 #

执行上述语句后，会得到符合需求的结果：

兼容低版本MySQL（低于8.0） #

如果你的MySQL版本不支持CTE（WITH语句），可以替换为嵌套子查询：

SELECT
    month,
    SUM(CASE WHEN country = '_it' THEN user_count ELSE 0 END) AS '_it',
    SUM(CASE WHEN country = '_hu' THEN user_count ELSE 0 END) AS '_hu',
    SUM(CASE WHEN country = '_ro' THEN user_count ELSE 0 END) AS '_ro'
FROM (
    SELECT
        DATE_FORMAT(first_order_date, '%b %Y') AS month,
        country,
        COUNT(DISTINCT email) AS user_count
    FROM (
        SELECT 
            email,
            country,
            MIN(order_date) AS first_order_date
        FROM pinkpanda.shop_orders
        WHERE 
            deleted = 'false'
            AND shipping_date <> '0000-00-00 00:00:00'
            AND status = 'sent'
        GROUP BY email, country
    ) AS user_first_orders
    GROUP BY month, country
) AS monthly_country_stats
GROUP BY month
ORDER BY STR_TO_DATE(month, '%b %Y');

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