新增维度列后Distinct Count统计总数异常偏高问题

需要通过SQL计算delivery stops指标，初始查询逻辑为按日期维度分组，对RATING_GROUP字段去重计数，查询语句如下：

SELECT 
    [EAR_START_DTTM_H],
    cast(year([EAR_START_DTTM_H]) as varchar(4))  +  right('0' + cast(month([EAR_START_DTTM_H]) as varchar(2)), 2) as calmonth,
    count(distinct[RATING_GROUP]) as delivery_stops
FROM [raw_abdc_operations].[sapbw_emanifest_data]
  where [EAR_START_DTTM_H] >= '2022-05-01' and [EAR_START_DTTM_H] < '2022-05-04' and org = '018'
  group by [EAR_START_DTTM_H]
  order by [EAR_START_DTTM_H]

该查询返回结果两日合计3344，符合预期：

EAR_START_DTTM_H    calmonth delivery_stops
2022-05-02T00:00:00 202205   1656
2022-05-03T00:00:00 202205   1688
                    **Total  3,344**

新增PRODUCT_CAT维度，调整为按EAR_START_DTTM_H、PRODUCT_CAT联合分组后，查询语句如下：

SELECT 
    [EAR_START_DTTM_H],
    cast(year([EAR_START_DTTM_H]) as varchar(4))  +  right('0' + cast(month([EAR_START_DTTM_H]) as varchar(2)), 2) as calmonth,
    [PRODUCT_CAT],
    count(distinct[RATING_GROUP]) as delivery_stops
FROM [raw_abdc_operations].[sapbw_emanifest_data]
  where [EAR_START_DTTM_H] >= '2022-05-01' and [EAR_START_DTTM_H] < '2022-05-04' and org = '018'
  group by [EAR_START_DTTM_H],[PRODUCT_CAT]
  order by [EAR_START_DTTM_H],[PRODUCT_CAT]

此时各分组结果累加总数达11878，远高于原统计值，存在明显数值虚高、重复计算问题，返回结果如下：

EAR_START_DTTM_H    calmonth PRODUCT_CAT delivery_stops
2022-05-02T00:00:00 202205   COLTOTL     1082
2022-05-02T00:00:00 202205   COLTOTS     742
2022-05-02T00:00:00 202205   DRPPKG      1031
2022-05-02T00:00:00 202205   LTOTE       1346
2022-05-02T00:00:00 202205   NC_CS       71
2022-05-02T00:00:00 202205   STOTE       1618
2022-05-03T00:00:00 202205   COLTOTL     1072
2022-05-03T00:00:00 202205   COLTOTS     816
2022-05-03T00:00:00 202205   DRPPKG      998
2022-05-03T00:00:00 202205   LTOTE       1392
2022-05-03T00:00:00 202205   NC_CS       69
2022-05-03T00:00:00 202205   STOTE       1641
                                **Total  11,878**

• 核心问题是同一个RATING_GROUP（即单个delivery stop）会关联多个PRODUCT_CAT值。
• 原查询仅按日期分组时，同一个RATING_GROUP无论对应多少个产品分类，count(distinct)只会在当日维度下计数1次，因此结果符合真实总stop数。
• 新增PRODUCT_CAT联合分组后，同一个RATING_GROUP如果关联N个不同的产品分类，就会在N个对应的日期+产品分类分组下各被计数1次。此时直接把各分组的delivery_stops值累加，相当于把跨分类重复出现的stop重复计算了多次，最终累加值远大于真实总stop数。

根据不同业务口径选择对应方案：

口径1：维度拆分后各分组值累加和需等于当日总stop数 #

这种口径要求每个delivery stop只能归属到一个PRODUCT_CAT下，需要先给每个RATING_GROUP匹配唯一的主属产品分类，再做分组统计。
主属分类的匹配规则按业务要求确定，比如取stop下关联记录数最多的分类、业务定义的优先级最高的分类均可，参考代码如下：

WITH stop_main_cat AS (
    SELECT 
        [RATING_GROUP],
        [EAR_START_DTTM_H],
        -- 示例规则：取该stop下出现次数最多的分类作为主分类，可按业务调整排序逻辑
        TOP 1 [PRODUCT_CAT] AS main_product_cat
    FROM [raw_abdc_operations].[sapbw_emanifest_data]
    WHERE [EAR_START_DTTM_H] >= '2022-05-01' AND [EAR_START_DTTM_H] < '2022-05-04' AND org = '018'
    GROUP BY [RATING_GROUP], [EAR_START_DTTM_H], [PRODUCT_CAT]
    ORDER BY COUNT(*) DESC
)
SELECT 
    t.[EAR_START_DTTM_H],
    cast(year(t.[EAR_START_DTTM_H]) as varchar(4))  +  right('0' + cast(month(t.[EAR_START_DTTM_H]) as varchar(2)), 2) as calmonth,
    s.main_product_cat AS [PRODUCT_CAT],
    count(distinct t.[RATING_GROUP]) as delivery_stops
FROM [raw_abdc_operations].[sapbw_emanifest_data] t
JOIN stop_main_cat s 
    ON t.[RATING_GROUP] = s.[RATING_GROUP] AND t.[EAR_START_DTTM_H] = s.[EAR_START_DTTM_H]
WHERE t.[EAR_START_DTTM_H] >= '2022-05-01' AND t.[EAR_START_DTTM_H] < '2022-05-04' AND t.org = '018'
GROUP BY t.[EAR_START_DTTM_H], s.main_product_cat
ORDER BY t.[EAR_START_DTTM_H], s.main_product_cat

该方案下每个stop只会在一个产品分类下被计数，各分组值累加和与原日期维度统计的总stop数完全一致。

口径2：允许单个stop被多个产品分类关联统计，仅需保证总数值准确 #

这种口径下原分组查询的单分组值是正确的，代表对应分类下关联的去重stop数，只是不能直接累加各分组值计算当日总stop数，总数值需要单独按日期维度去重统计，也可以通过窗口函数直接在查询中返回当日总数值做对照，参考代码如下：

SELECT 
    [EAR_START_DTTM_H],
    cast(year([EAR_START_DTTM_H]) as varchar(4))  +  right('0' + cast(month([EAR_START_DTTM_H]) as varchar(2)), 2) as calmonth,
    [PRODUCT_CAT],
    count(distinct[RATING_GROUP]) as delivery_stops_in_cat,
    -- 窗口函数直接返回当日总stop数，无需累加分组值
    count(distinct[RATING_GROUP]) OVER (PARTITION BY [EAR_START_DTTM_H]) as total_daily_delivery_stops
FROM [raw_abdc_operations].[sapbw_emanifest_data]
  where [EAR_START_DTTM_H] >= '2022-05-01' and [EAR_START_DTTM_H] < '2022-05-04' and org = '018'
  group by [EAR_START_DTTM_H],[PRODUCT_CAT]
  order by [EAR_START_DTTM_H],[PRODUCT_CAT]

提示：如果统计时需要跨维度做值的加总，必须提前确认维度和统计粒度的对应关系，存在多对一关联的维度直接加总去重指标必然会出现重复计算问题。

内容的提问来源于stack exchange，提问作者A.Cook