SQL Server分组求最常出现值及黄金记录属性排名优化问询

嘿，我来帮你梳理下SQL Server里分组统计最常出现的值（也就是众数）、构建黄金记录的优化方案～

先从最基础的场景说起：假设你有一组属于同一个匹配组的人员属性记录，要统计每个属性下出现次数最多的值。

比如我们先模拟一个场景表，用来存同一个匹配组里的各类属性条目：

CREATE TABLE people_matches (
    match_group_id INT, -- 同一个人的匹配组ID
    attribute_name VARCHAR(50), -- 属性名：比如姓名、电话、邮箱
    attribute_value VARCHAR(200) -- 属性值
);

用窗口函数就能高效实现统计，核心思路是先给每个属性值计数，再按计数排序取第一：

WITH ranked_values AS (
    SELECT 
        match_group_id,
        attribute_name,
        attribute_value,
        -- 统计当前值在分组+属性里的出现次数
        COUNT(*) OVER (PARTITION BY match_group_id, attribute_name, attribute_value) AS value_count,
        -- 按出现次数降序排，次数相同就按值本身排序（避免随机选）
        ROW_NUMBER() OVER (PARTITION BY match_group_id, attribute_name ORDER BY COUNT(*) DESC, attribute_value) AS rn
    FROM people_matches
)
SELECT 
    match_group_id,
    attribute_name,
    attribute_value AS most_frequent_value
FROM ranked_values
WHERE rn = 1;

如果你的源数据是多列属性（比如人员表直接有name、phone、email列），先转成行格式再统计就行，后面会讲到。

假设你的人员表已经按匹配规则分好了组（有match_group_id标识同一个人的多条记录），要给每个组生成一条黄金记录，每个属性取组内最常出现的值。

先模拟源表：

CREATE TABLE people (
    person_id INT,
    name VARCHAR(100),
    phone VARCHAR(20),
    email VARCHAR(100),
    match_group_id INT -- 匹配组ID，同一个组是同一个人的不同记录
);

步骤分三步：列转行 → 统计众数 → 行转列，最终得到黄金记录：

WITH unpivoted AS (
    -- 把多列属性转成一行一条的格式，方便统一统计
    SELECT 
        match_group_id,
        attribute_name,
        attribute_value
    FROM people
    UNPIVOT (
        attribute_value FOR attribute_name IN (name, phone, email)
    ) AS up
    -- 先过滤空值，减少无效计算
    WHERE attribute_value IS NOT NULL
),
ranked_attributes AS (
    -- 给每个分组+属性的取值排名，取出现次数最多的
    SELECT 
        match_group_id,
        attribute_name,
        attribute_value,
        ROW_NUMBER() OVER (PARTITION BY match_group_id, attribute_name ORDER BY COUNT(*) DESC, attribute_value) AS rn
    FROM unpivoted
    GROUP BY match_group_id, attribute_name, attribute_value
)
-- 把行格式转回列，生成黄金记录
SELECT 
    match_group_id,
    MAX(CASE WHEN attribute_name = 'name' THEN attribute_value END) AS golden_name,
    MAX(CASE WHEN attribute_name = 'phone' THEN attribute_value END) AS golden_phone,
    MAX(CASE WHEN attribute_name = 'email' THEN attribute_value END) AS golden_email
FROM ranked_attributes
WHERE rn = 1
GROUP BY match_group_id;

如果你的数据量比较大，或者需要更灵活的规则，这些优化点可以帮到你：

• 提前过滤无效数据：在列转行之前就过滤掉空值、格式错误的属性值（比如无效的手机号），减少后续计算的数据量，这是最直观的性能提升手段。

• 简化聚合逻辑：如果用的是SQL Server 2012及以上版本，可以不用先GROUP BY，直接用窗口函数统计次数，减少一次聚合步骤：

ranked_attributes AS (
    SELECT 
        match_group_id,
        attribute_name,
        attribute_value,
        ROW_NUMBER() OVER (PARTITION BY match_group_id, attribute_name 
                           ORDER BY COUNT(*) OVER (PARTITION BY match_group_id, attribute_name, attribute_value) DESC, 
                                    attribute_value) AS rn
    FROM unpivoted
)

• 处理平局场景：如果有多个值出现次数相同（比如分组里两个姓名各出现2次），可以调整排序规则，比如优先选来自官方数据源的值（如果有source字段）：

ROW_NUMBER() OVER (PARTITION BY match_group_id, attribute_name 
                   ORDER BY COUNT(*) DESC, 
                            CASE WHEN source = 'official' THEN 1 ELSE 2 END, 
                            attribute_value) AS rn

• 索引优化：给分组列、属性列建包含索引，比如给people表建：

CREATE NONCLUSTERED INDEX IX_people_matchgroup_attr ON people(match_group_id) INCLUDE(name, phone, email);

这样数据库可以快速定位到每个分组的属性值，减少全表扫描的开销。

• 用临时表拆分计算：如果数据量极大，把中间结果存入带索引的临时表，可以大幅提升后续统计的速度：

-- 先把转好的行数据存入临时表
SELECT match_group_id, attribute_name, attribute_value
INTO #unpivoted_temp
FROM people
UNPIVOT (
    attribute_value FOR attribute_name IN (name, phone, email)
) AS up
WHERE attribute_value IS NOT NULL;

-- 给临时表建索引
CREATE CLUSTERED INDEX IX_temp_matchgroup_attr ON #unpivoted_temp(match_group_id, attribute_name);

-- 再进行统计排名
WITH ranked_attributes AS (
    SELECT 
        match_group_id,
        attribute_name,
        attribute_value,
        ROW_NUMBER() OVER (PARTITION BY match_group_id, attribute_name ORDER BY COUNT(*) DESC, attribute_value) AS rn
    FROM #unpivoted_temp
    GROUP BY match_group_id, attribute_name, attribute_value
)
SELECT ... FROM ranked_attributes WHERE rn=1;

-- 用完记得删临时表
DROP TABLE #unpivoted_temp;

如果你的黄金记录需要结合其他规则（比如优先取最新的记录、非空值），直接把规则加到排序逻辑里就行，比如：

ROW_NUMBER() OVER (PARTITION BY match_group_id, attribute_name 
                   ORDER BY 
                       -- 优先非空值
                       CASE WHEN attribute_value IS NOT NULL THEN 1 ELSE 2 END,
                       -- 然后按出现次数排序
                       COUNT(*) DESC,
                       -- 最后取最新的记录（如果有create_date字段）
                       create_date DESC) AS rn

这样就能让黄金记录的属性值完全符合你的业务优先级啦～

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