SQL Server值组合全频次统计需求及现有方案优化问询

原数据集 #

[id]  [value]
--------------
A        15
A        11
A        11
B        13
B        15
B        12
C        12
C        13
D        13  
D        12

需求说明 #

需统计所有value组合的频次，规则如下：

• 组合为无序：12,13与13,12视为同一组合
• 重复值需区分：例如ID为A的两条11，需作为独立元素参与子组合生成
• 支持任意长度子组合：不仅统计每个ID的完整value组合，还要统计所有可能的非空子组合（如ID为B的12,13,15需拆解出12、13、15、12,13等子组合）

原方案仅能统计每个ID的完整组合频次，无法覆盖子组合需求——比如12,13组合应被统计3次（来自B的子组合、C的完整组合、D的完整组合）。

优化后的SQL方案 #

针对SQL Server 2012，可通过递归CTE生成所有非空子组合，统一格式后统计频次：

WITH ranked_values AS (
    -- 为每个ID的value按排序后添加序号，用于递归生成有序子组合
    SELECT 
        id, 
        value,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY value) AS rn,
        COUNT(*) OVER (PARTITION BY id) AS total_rows
    FROM t
),
subcombinations AS (
    -- 递归起始：单个元素的子组合
    SELECT 
        id,
        CAST(value AS VARCHAR(MAX)) AS combo,
        rn,
        total_rows
    FROM ranked_values
    UNION ALL
    -- 递归生成多元素子组合：将当前组合与后续元素拼接
    SELECT 
        rv.id,
        sc.combo + ',' + CAST(rv.value AS VARCHAR(MAX)) AS combo,
        rv.rn,
        sc.total_rows
    FROM subcombinations sc
    JOIN ranked_values rv 
        ON rv.id = sc.id 
        AND rv.rn > sc.rn
)
-- 统计每个组合的出现频次
SELECT 
    combo AS vals,
    COUNT(DISTINCT id) AS frequency -- 若需统计ID内重复子组合，去掉DISTINCT即可
FROM subcombinations
GROUP BY combo
ORDER BY frequency DESC, vals;

逻辑说明 #

1. ranked_values：为每个ID下的value排序并添加序号，确保子组合生成时按固定顺序拼接，避免同一无序组合出现多种格式（如13,12）
2. subcombinations：通过递归从单个元素开始，逐步拼接后续元素，生成所有长度的非空子组合
3. 统计规则：
   • COUNT(DISTINCT id)：每个ID内的同一子组合仅统计1次，符合示例中12,13统计3次的需求
   • 若需统计ID内的重复子组合（如A的两个11，单个11组合统计2次），移除DISTINCT即可

部分结果示例 #

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