如何统计各月下单两次且未在之前月份被统计的客户数？

首先纠正原SQL的核心问题：原语句中GROUP BY 月份后用COUNT(o.customer_id) >1是判断当月总订单数大于1，而非单个客户当月下单次数≥2，这会导致统计错误。比如某月份有两个客户各下1单，总订单数是2，会被误判为符合条件，但实际没有客户满足当月下单两次的要求。

要实现「每个月统计首次出现当月下单≥2次的客户数量，且客户一旦被统计过，后续月份不再计入」的需求，可分三步实现：

步骤1：筛选出所有客户的达标月份（当月下单≥2次） #

先按客户+月份分组，计算每个客户每月的下单次数，筛选出下单次数≥2的记录：

SELECT 
  customer_id,
  DATE_FORMAT(date, '%Y-%m') AS order_month,
  COUNT(order_id) AS order_count
FROM `order`
GROUP BY customer_id, DATE_FORMAT(date, '%Y-%m')
HAVING order_count >= 2

步骤2：找出每个客户首次达标的月份 #

对上述结果按客户分组，取最早的达标月份（确保每个客户只被统计一次）：

SELECT 
  customer_id,
  MIN(order_month) AS first_qualified_month
FROM (
  -- 步骤1的子查询
  SELECT 
    customer_id,
    DATE_FORMAT(date, '%Y-%m') AS order_month,
    COUNT(order_id) AS order_count
  FROM `order`
  GROUP BY customer_id, DATE_FORMAT(date, '%Y-%m')
  HAVING order_count >= 2
) AS customer_month_orders
GROUP BY customer_id

步骤3：按月份统计首次达标客户数量 #

最后对首次达标月份分组，统计每个月的客户数：

SELECT 
  first_qualified_month AS month,
  COUNT(customer_id) AS qualified_customer_count
FROM (
  -- 步骤2的子查询
  SELECT 
    customer_id,
    MIN(order_month) AS first_qualified_month
  FROM (
    -- 步骤1的子查询
    SELECT 
      customer_id,
      DATE_FORMAT(date, '%Y-%m') AS order_month,
      COUNT(order_id) AS order_count
    FROM `order`
    GROUP BY customer_id, DATE_FORMAT(date, '%Y-%m')
    HAVING order_count >= 2
  ) AS customer_month_orders
  GROUP BY customer_id
) AS first_qualified
GROUP BY first_qualified_month
ORDER BY first_qualified_month;

效果说明 #

假设你的订单表中有以下数据：

最终查询结果会是：

456在3月的达标记录不会被计入，因为他首次达标是2023-02。

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