如何按多列分组/分区并为每个分组分配唯一编号？

问题描述 #

现有一张包含以下字段的表格：

• Roll_number：数字ID
• Col1：取值为A或B
• Col2：取值为P或Q
• Col3：取值为L、M或N

原始表数据如下：

Roll_number , Col1 , Col2, Col3
121212      ,    A ,    P,    L
131313      ,    A ,    P,    L
141414      ,    B ,    P,    M
525252      ,    A ,    Q,    L
626262      ,    B ,    P,    M
929292      ,    A ,    Q,    L
939393      ,    A ,    P,    N
232323      ,    B ,    Q,    L
090909      ,    B ,    P,    L
303030      ,    B ,    Q,    N
505050      ,    A ,    Q,    M
608960      ,    A ,    Q,    L

需求是按Col1、Col2、Col3分组后，为每个分组分配唯一的Group_Name编号，得到如下结果：

Roll_number , Col1 , Col2, Col3, Group_Name
121212      ,    A ,    P,    L,          1
131313      ,    A ,    P,    L,          1

141414      ,    B ,    P,    M,          2
626262      ,    B ,    P,    M,          2

525252      ,    A ,    Q,    L,          3
929292      ,    A ,    Q,    L,          3
608960      ,    A ,    Q,    L,          3

939393      ,    A ,    P,    N,          4
232323      ,    B ,    Q,    L,          5
090909      ,    B ,    P,    L,          6
303030      ,    B ,    Q,    N,          7
505050      ,    A ,    Q,    M,          8

解决方案 #

方法一：使用窗口函数 DENSE_RANK()（推荐） #

这是最简洁高效的实现方式，利用窗口函数按分组列排序，为每个唯一的Col1,Col2,Col3组合分配连续且唯一的编号：

SELECT 
    Roll_number,
    Col1,
    Col2,
    Col3,
    DENSE_RANK() OVER (ORDER BY Col1, Col2, Col3) AS Group_Name
FROM 
    your_table_name;

• 核心说明：DENSE_RANK()会自动识别相同的分组组合，为其分配相同编号，且编号是连续的（不会出现跳号），完全匹配需求中的编号规则。

方法二：分组映射关联法（兼容无窗口函数的SQL环境） #

如果你的SQL数据库版本不支持窗口函数，可以先通过分组生成每个唯一组合的编号，再关联回原表：

-- 先生成分组与编号的映射表
WITH group_mapping AS (
    SELECT 
        Col1,
        Col2,
        Col3,
        ROW_NUMBER() OVER (ORDER BY Col1, Col2, Col3) AS Group_Name
    FROM 
        your_table_name
    GROUP BY 
        Col1, Col2, Col3
)
-- 关联原表获取最终结果
SELECT 
    t.Roll_number,
    t.Col1,
    t.Col2,
    t.Col3,
    gm.Group_Name
FROM 
    your_table_name t
JOIN 
    group_mapping gm 
    ON t.Col1 = gm.Col1 
    AND t.Col2 = gm.Col2 
    AND t.Col3 = gm.Col3
ORDER BY 
    gm.Group_Name, t.Roll_number;

• 核心说明：先通过GROUP BY提取所有唯一的分组组合，给每个组合分配编号，再通过JOIN将编号映射到原表的每一行，同样能得到符合要求的结果。

方法三：使用ROW_NUMBER()（注意适用场景） #

如果直接按分组列排序使用ROW_NUMBER()，在分组组合连续排列的情况下，效果和DENSE_RANK()一致，但如果原表中同组数据不连续，会导致同组编号不同，因此仅推荐在数据已按分组列排序的场景使用：

SELECT 
    Roll_number,
    Col1,
    Col2,
    Col3,
    ROW_NUMBER() OVER (ORDER BY Col1, Col2, Col3) AS Group_Name
FROM 
    your_table_name;

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