SQL Server中如何单次扫描实现DISTINCT COUNT统计？

当然有更高效的单次扫描方案！你可以利用窗口函数来实现需求，不需要创建临时表，只需要一次查询就能完成所有计算，避免多次扫描原表和中间表的额外开销。

解决方案代码 #

SELECT 
    [Region],
    [Country],
    [Manufacturer],
    [Brand],
    [Period],
    SUM([Spend]) AS [Spend],
    COUNT(DISTINCT [Brand]) OVER (
        PARTITION BY [Region], [Country], [Manufacturer], [Period]
    ) AS [UniqBrandCount]
FROM myTable
GROUP BY [Region], [Country], [Manufacturer], [Brand], [Period]
ORDER BY [Region], [Country], [Manufacturer], [Brand]

代码解释 #

1. 基础聚合部分：SUM([Spend]) AS [Spend] 配合 GROUP BY 子句，完成你原本在Query #2里的支出聚合逻辑，得到每个品牌在对应分组下的总支出。
2. 窗口函数部分：COUNT(DISTINCT [Brand]) OVER (...) 是核心优化点：
   • PARTITION BY [Region], [Country], [Manufacturer], [Period] 指定了计算去重品牌数的分组维度，和你在Query #1里的分组逻辑完全一致。
   • 窗口函数会在一次扫描原表的过程中，同时完成分组聚合和分组内的去重计数，不需要额外的临时表和JOIN操作。

为什么这是更优方案 #

• 减少IO开销：原方案需要三次访问数据（两次写入临时表+一次JOIN读取），而这个方案只需要单次扫描原表，在数据量较大时性能提升非常明显。
• 简化逻辑：不需要维护临时表，代码更简洁，可读性和可维护性更强。
• 结果一致：输出的UniqBrandCount值完全符合你预期的结果，比如R1、C1、M1在2016年的两条记录都会显示2，和你给出的最终示例完全匹配。

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