SQL按年月分组统计去重后的跟踪状态订单总数

问题描述 #

业务表共包含5个字段：TrackingStatus、Year、Month、Order、Notes，需要按年份、月份、TrackingStatus维度分组，统计各分组下的有效订单总数。
统计规则：同一TrackingStatus、同一年份、同一月份下，Order编号相同的记录属于重复数据，仅计数1次。

预期统计逻辑验证 #

基于测试数据的正确结果如下：

• TrackingStatus=F、Year=2020、Month=1：原始记录2条，Order均为33，去重后Total=1
• TrackingStatus=E、Year=2020、Month=2：原始记录1条，Order为36，去重后Total=1
• TrackingStatus=A、Year=2021、Month=2：原始记录1条，Order为45，去重后Total=1
• TrackingStatus=A、Year=2021、Month=3：原始记录3条，Order为34（重复2次）、88，去重后Total=2

最终输出需包含TrackingStatus、Year、Month、Total四个字段。

直接使用GROUP BY + COUNT(*)的写法会统计所有匹配行的数量，无法自动排除同组内重复的Order编号，不符合统计要求。

实现方法 #

直接使用COUNT(DISTINCT)函数即可实现指定字段去重计数，无需额外嵌套复杂子查询。假设业务表名为t_business_data，SQL语句如下：

SELECT
  TrackingStatus,
  Year,
  Month,
  COUNT(DISTINCT `Order`) AS Total
FROM t_business_data
GROUP BY TrackingStatus, Year, Month;

说明 #

1. COUNT(DISTINCT \Order`)`会在每个分组内对Order值去重后再计数，天然适配同组相同Order仅算1次的规则
2. 由于Order是SQL标准保留关键字，建议用反引号包裹避免语法报错
3. 所有支持SQL标准的数据库（MySQL、PostgreSQL、SQL Server、BigQuery等）都支持该写法，兼容性极强

如果面对超大规模数据集，部分数据库下先去重再聚合的写法性能更优，逻辑完全等价，写法如下：

SELECT
  TrackingStatus,
  Year,
  Month,
  COUNT(*) AS Total
FROM (
  SELECT DISTINCT TrackingStatus, Year, Month, `Order`
  FROM t_business_data
) AS dedup_data
GROUP BY TrackingStatus, Year, Month;

该写法先通过子查询剔除所有维度+Order维度完全重复的记录，再做分组计数，结果和第一种写法完全一致。

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