SQL统计近30天每日订单量时如何计算全周期去重客户数

问题核心 #

原SQL的逻辑漏洞来自GROUP BY的分组作用范围：当按order_date分组后，所有聚合函数的计算边界都会被限定在对应单日的数据集内，哪怕WHERE条件已经筛出了整整30天的有效订单，COUNT(DISTINCT customer_id)也只会统计当日的去重客户，最终出现跨天下单的客户被重复计数的问题，完全达不到统计整个周期唯一客户数的需求。

两种实际可用的实现写法 #

根据使用的数据库版本不同，可以按需选择：

写法1：窗口函数实现（数据库支持时优先用） #

窗口函数的计算范围不受普通GROUP BY的分组限制，可以独立在整个30天的筛选结果集上计算全局去重客户数，再对应到每一日的统计结果上，SQL逻辑如下：

SELECT 
  order_date,
  -- 统计对应日期的订单总量
  COUNT(order_id) AS daily_order_amount,
  -- 在全量30天数据上计算去重客户数，不受单日分组影响
  COUNT(DISTINCT customer_id) OVER () AS total_30d_unique_customer
FROM orders
WHERE 
  -- 注意先把斜杠格式的日期字符串转成标准日期类型，避免比较逻辑出错
  STR_TO_DATE(order_date, '%Y/%m/%d') BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
GROUP BY order_date

结合测试数据来看：如果统计窗口覆盖2021-02-05到2021-03-05的范围，窗口内涉及的客户为1、2、3共3个，不管客户1在3月1日、3月4日先后下单，客户3在2月27日、3月1日多次下单，total_30d_unique_customer列在每一行的返回值都会固定为3，不会出现重复计数。

写法2：子查询独立计算全局客户数 #

如果数据库版本不支持窗口函数，可以拆成两个独立的统计逻辑：先算每日的订单量，再单独算整个30天的全局去重客户数，最后用交叉关联（因为全局值是固定常量，不需要匹配字段）拼接结果即可，SQL逻辑如下：

SELECT 
  daily_stat.order_date,
  daily_stat.daily_order_amount,
  global_stat.unique_customer_amount
FROM (
  -- 子查询1：统计每日维度的订单总量
  SELECT 
    order_date,
    COUNT(order_id) AS daily_order_amount
  FROM orders
  WHERE STR_TO_DATE(order_date, '%Y/%m/%d') BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
  GROUP BY order_date
) daily_stat
CROSS JOIN (
  -- 子查询2：独立统计30天全周期的去重客户数
  SELECT COUNT(DISTINCT customer_id) AS unique_customer_amount
  FROM orders
  WHERE STR_TO_DATE(order_date, '%Y/%m/%d') BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
) global_stat

常见踩坑提醒 #

• 原SQL直接用斜杠格式的字符串日期做范围比较，会因为字符串字典序的比较逻辑和日期实际先后顺序不匹配，导致筛选结果出错，必须先做格式转换再做范围判断
• 如果需要按国家维度拆分统计30天内的去重客户数，只需要在窗口函数中加上分区规则即可，比如将全局统计的窗口调整为COUNT(DISTINCT customer_id) OVER (PARTITION BY order_country) AS country_30d_unique_customer，就能拿到对应国家周期内的去重客户数

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