Azure Synapse中多列场景下COUNT(DISTINCT)的性能优化咨询

方案1：使用窗口函数（全局聚合场景） #

如果你的需求是保留原表所有行，同时每行附带全表范围的去重计数，直接用COUNT(DISTINCT ...) OVER ()窗口函数替代聚合查询，无需GROUP BY：

SELECT *
    ,COUNT(DISTINCT XXX) OVER () AS Invoice_Count
    ,COUNT(DISTINCT YYY) OVER () AS PO_Count
    ,COUNT(DISTINCT ZZZ) OVER () AS PO_Item_Count
    ,'INV_WT_PO' AS Source_Identifier
    ,CONCAT(XXX, YYY) AS Reference_Key
FROM [XXX].[YYY]

该方案会一次性计算全表的去重计数并附加到每一行，性能和原无聚合的查询接近，避免了大列数分组的高计算开销。

方案2：预计算聚合后关联（分组聚合场景） #

如果你的真实需求是按部分列分组（而非保留所有行），可以先单独计算聚合结果，再和原表关联，避免GROUP BY 150+列：

WITH Aggregated AS (
    SELECT 
        -- 仅填写实际需要分组的列，无需包含所有150+列
        GroupCol1, GroupCol2,
        COUNT(DISTINCT XXX) AS Invoice_Count,
        COUNT(DISTINCT YYY) AS PO_Count,
        COUNT(DISTINCT ZZZ) AS PO_Item_Count
    FROM [XXX].[YYY]
    GROUP BY GroupCol1, GroupCol2
)
SELECT 
    t.*,
    a.Invoice_Count,
    a.PO_Count,
    a.PO_Item_Count,
    'INV_WT_PO' AS Source_Identifier,
    CONCAT(t.XXX, t.YYY) AS Reference_Key
FROM [XXX].[YYY] t
JOIN Aggregated a ON t.GroupCol1 = a.GroupCol1 AND t.GroupCol2 = a.GroupCol2

这种方式将聚合计算与全表查询分离，大幅降低分组操作的性能消耗。

补充：强制分组场景的索引优化 #

如果确实需要按所有150+列分组，可以创建覆盖索引包含分组列和聚合用到的字段，提升GROUP BY性能：

CREATE NONCLUSTERED INDEX IX_GroupedColumns ON [XXX].[YYY]
(Col1, Col2, ... -- 所有需要分组的150+列)
INCLUDE (XXX, YYY, ZZZ);

注意：创建覆盖索引需权衡存储成本和维护开销，适合高频查询的场景。

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