如何用集合操作替代STRING_AGG高效统计XRef表的列唯一组合数

核心需求 #

统计XRef表中，按列A分组后列B的唯一值组合总数（无需关联具体列A的值）。以家庭结构场景为例：即统计不同父亲（列A=FatherID）对应的母亲（列B=MotherID）组合有多少种不同的类型。

现有方案的问题 #

使用STRING_AGG+CTE的方式，本质是将每个分组的列B值拼接成字符串后去重计数。但在百万级数据下，该方案存在明显性能瓶颈：

• 字符串拼接的CPU开销大，尤其是排序和字符编码处理环节
• 长字符串的存储与去重对比需要更多IO资源
• 若列B包含分隔符，还需额外处理避免组合冲突

替代方案：基于二进制/哈希的集合操作 #

方案1：二进制拼接排重（性能最优） #

利用二进制数据的高效存储与对比特性，将每个分组的列B值排序后拼接成二进制数组，直接统计不同二进制数组的数量。该方案避免了字符串操作的额外开销，适合大数据量场景。

假设关联表结构为：

CREATE TABLE Father_Child_XRef (
    FatherID INT,
    ChildID INT,
    MotherID INT -- 每个孩子对应的母亲ID
);

实现代码：

WITH GroupedBinarySets AS (
    SELECT 
        FatherID,
        -- 将分组内的MotherID按顺序拼接成二进制数组
        CAST((
            SELECT CAST(MotherID AS BINARY(4))
            FROM Father_Child_XRef AS sub
            WHERE sub.FatherID = main.FatherID
            ORDER BY MotherID -- 排序保证相同组合的二进制一致
            FOR XML PATH(''), TYPE
        ).value('.', 'VARBINARY(MAX)') AS VARBINARY(MAX)) AS MotherCombinationBin
    FROM Father_Child_XRef AS main
    GROUP BY FatherID
)
-- 统计不同二进制数组的数量，即唯一组合数
SELECT COUNT(DISTINCT MotherCombinationBin) AS UniqueMotherCombinations
FROM GroupedBinarySets;

方案2：哈希值排重（平衡性能与兼容性） #

若担心二进制数组的存储长度问题，可对拼接后的二进制（或字符串）生成固定长度的哈希值，再统计不同哈希值的数量。哈希值长度固定，对比与存储效率更高，且几乎不会出现碰撞（推荐使用SHA2_256算法）。

实现代码：

WITH GroupedHashes AS (
    SELECT 
        FatherID,
        -- 对有序的MotherID组合生成SHA2_256哈希值
        HASHBYTES('SHA2_256', 
            CAST((
                SELECT CAST(MotherID AS BINARY(4))
                FROM Father_Child_XRef AS sub
                WHERE sub.FatherID = main.FatherID
                ORDER BY MotherID
                FOR XML PATH(''), TYPE
            ).value('.', 'VARBINARY(MAX)') AS VARBINARY(MAX))
        ) AS MotherCombinationHash
    FROM Father_Child_XRef AS main
    GROUP BY FatherID
)
SELECT COUNT(DISTINCT MotherCombinationHash) AS UniqueMotherCombinations
FROM GroupedHashes;

额外性能优化建议 #

1. 创建针对性索引：为分组列（FatherID）创建包含列B（MotherID）的非聚集索引，避免分组时的全表扫描：

CREATE NONCLUSTERED INDEX IX_Father_Child_XRef_FatherID 
ON Father_Child_XRef(FatherID) 
INCLUDE(MotherID);

2. 限制数据类型长度：若列B的取值范围固定（如INT类型的MotherID），避免使用VARBINARY(MAX)，改用VARBINARY(4 * N)（N为分组内最大列B数量），进一步降低内存开销。

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