如何用BigQuery单查询统计每月新增客户数量？

要实现按月份统计新增客户数并输出YYYY-MM, 数量的格式，可以通过子查询+分组统计的方式完成，以下是正确的查询语句：

SELECT
  FORMAT_DATE('%Y-%m', FirstOrderDate) AS month,
  COUNT(*) AS new_customers
FROM (
  -- 子查询：获取每个客户的首次下单日期（即成为新增客户的日期）
  SELECT emailAddress, MIN(date) AS FirstOrderDate
  FROM `order-data-360921.kk.customerOrders`
  GROUP BY emailAddress
) AS customer_first_orders
GROUP BY month
ORDER BY month;

语句说明： #

1. 内层子查询：按emailAddress分组，用MIN(date)找出每个客户的最早下单日期，这个日期就是该客户被算作新增客户的时间点。
2. 外层查询：
   • 用FORMAT_DATE('%Y-%m', FirstOrderDate)将首次下单日期格式化为YYYY-MM的月份格式。
   • 按格式化后的月份分组，用COUNT(*)统计每组的客户数量，即当月新增客户数。
   • 最后ORDER BY month保证结果按时间顺序排列。

你之前尝试的问题说明： #

你第二次尝试的查询存在两个核心问题：

• 错误使用了BigQuery不存在的yr()和mn()函数，处理日期应该用EXTRACT、FORMAT_DATE或DATE_TRUNC这类官方函数。
• 语法结构混乱，把COUNT(*)放在FROM子句后属于语法错误，分组逻辑也没有正确关联子查询的字段。

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