如何计算客户首月购买订单数？SQL实现求助

你可以通过两种常见方式实现FirstMonthPurchaseCount的统计，以下是具体实现：

方法一：子查询关联统计 #

先通过子查询获取每位客户的首次购买年月，再关联原订单表，筛选出该客户在首购年月内的所有订单并计数：

WITH CustomerFirstPurchase AS (
  SELECT 
    CustomerId,
    YEAR(MIN(OrderDate)) AS FirstOrderYear,
    MONTH(MIN(OrderDate)) AS FirstOrderMonth
  FROM Orders
  GROUP BY CustomerId
)
SELECT 
  c.CustomerId,
  c.FirstOrderMonth,
  c.FirstOrderYear,
  COUNT(o.OrderId) AS FirstMonthPurchaseCount
FROM CustomerFirstPurchase c
JOIN Orders o 
  ON c.CustomerId = o.CustomerId 
  AND YEAR(o.OrderDate) = c.FirstOrderYear 
  AND MONTH(o.OrderDate) = c.FirstOrderMonth
GROUP BY c.CustomerId, c.FirstOrderMonth, c.FirstOrderYear

方法二：聚合函数直接计算 #

利用CASE语句判断当前订单是否属于客户的首购年月，再通过SUM函数统计符合条件的订单数量：

SELECT 
  CustomerId,
  MONTH(MIN(OrderDate)) AS FirstOrderMonth,
  YEAR(MIN(OrderDate)) AS FirstOrderYear,
  SUM(
    CASE 
      WHEN YEAR(OrderDate) = YEAR(MIN(OrderDate)) 
        AND MONTH(OrderDate) = MONTH(MIN(OrderDate)) 
      THEN 1 
      ELSE 0 
    END
  ) AS FirstMonthPurchaseCount
FROM Orders
GROUP BY CustomerId

两种方法都能得到你期望的结果：方法一兼容性更强，适配多数SQL数据库；方法二更简洁，在支持聚合函数嵌套判断的数据库（如MySQL、PostgreSQL）中可直接使用。

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