SQL实现选取每个SiteID下计数最高的Group作为主分组

问题背景 #

现有SiteID表存在数据归属问题：单个SiteID对应多个Group值（注意：单个Group可对应多个SiteID），示例源数据如下：

SiteID   | AccountID | Group
--------------------------
6480021A | 64800211  | A01
6480021A | 64800212  | A01
6480021A | 64800213  | A01
6480021A | 64800214  | A01
6480021A | 64800215  | A02
6480021A | 64800216  | A02
6480021A | 64800217  | NULL

当前已完成两步前置处理：

• 筛选出存在多个Group的SiteID存入CountSite表
• 统计每个SiteID下各Group的记录数存入临时表#tmp2，统计结果示例：

SiteID   | Group | Count
--------------------------
6480021A | A01   | 4
6480021A | A02   | 2
6480021A | NULL  | 1

最终目标：选取每个SiteID下计数最高的Group作为主分组，将该SiteID下所有记录的Group值统一更新为主分组值。以上述示例数据为例，最终选取A01作为SiteID 6480021A的主分组，预期结果如下：

SiteID   | AccountID | Group
--------------------------
6480021A | 64800211  | A01
6480021A | 64800212  | A01
6480021A | 64800213  | A01
6480021A | 64800214  | A01
6480021A | 64800215  | A01
6480021A | 64800216  | A01
6480021A | 64800217  | A01

实现步骤 #

基于已经构建的CountSite和#tmp2临时表，分两步完成最终逻辑：

1. 提取每个SiteID对应的主分组 #

使用窗口函数ROW_NUMBER()按SiteID分区，按分组计数倒序排序，取每个分区排名第1的记录即为该SiteID计数最高的主分组，结果存入临时表#MainGroup：

处理规则说明：若同一SiteID下存在多个Group计数并列最高，会优先选择非NULL、编码字典序更小的Group作为主分组，可根据实际业务规则调整排序条件

---- 提取每个SiteID的主分组 ----
SELECT 
    SITE_ID,
    [GROUP] AS MAIN_GROUP
INTO #MainGroup
FROM (
    SELECT 
        SITE_ID,
        [GROUP],
        COUNT_GROUP,
        ROW_NUMBER() OVER (
            PARTITION BY SITE_ID 
            ORDER BY COUNT_GROUP DESC, ISNULL([GROUP], 'ZZZ') ASC
        ) AS row_rank
    FROM #tmp2
) ranked
WHERE row_rank = 1

2. 批量更新原表Group字段 #

关联主分组临时表，统一更新原表中问题SiteID的Group字段：

---- 批量更新原表Group值为主分组 ----
UPDATE origin
SET origin.[GROUP] = main.MAIN_GROUP
FROM SiteID origin
INNER JOIN #MainGroup main
    ON origin.SITE_ID = main.SITE_ID
-- 关联CountSite仅更新存在归属问题的SiteID，避免全表无效扫描
INNER JOIN CountSite cs
    ON origin.SITE_ID = cs.SITE_ID

简化写法（可选） #

如果不需要保留中间统计结果，可以通过CTE一步完成更新，无需提前创建CountSite和#tmp2表，执行效率更高：

;WITH GroupRank AS (
    SELECT 
        SITE_ID,
        [GROUP],
        ROW_NUMBER() OVER (
            PARTITION BY SITE_ID
            ORDER BY COUNT(*) DESC, ISNULL([GROUP], 'ZZZ') ASC
        ) AS row_rank
    FROM SiteID
    GROUP BY SITE_ID, [GROUP]
)
UPDATE origin
SET origin.[GROUP] = main.[GROUP]
FROM SiteID origin
INNER JOIN GroupRank main
    ON origin.SITE_ID = main.SITE_ID
WHERE main.row_rank = 1
-- 仅更新Group值和主分组不一致的记录，减少无效写入
AND ISNULL(origin.[GROUP], '') <> ISNULL(main.[GROUP], '')

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