如何使用SQL DENSE_RANK函数按原始顺序为重复数据排名

现有test_table表数据如下：

id | col1 | col2 | col3
---+------+------+------
1  |  3   |  a   |  x 
2  |  2   |  a   |  y 
3  |  1   |  b   |  y
4  |  2   |  c   |  z 
5  |  3   |  a   |  x 
6  |  3   |  a   |  x
7  |  2   |  a   |  y

需求是：所有col1、col2、col3完全相同的行排名一致，排名按数据首次出现的原始顺序从1开始递增，期望输出如下：

id | col1 | col2 | col3 | rank
---+------+------+------+-----
1  |  3   |  a   |  x   |  1
2  |  2   |  a   |  y   |  2
3  |  1   |  b   |  y   |  3
4  |  2   |  c   |  z   |  4
5  |  3   |  a   |  x   |  1
6  |  3   |  a   |  x   |  1
7  |  2   |  a   |  y   |  2

你之前尝试的DENSE_RANK() OVER (ORDER BY col1, col2, col3)是按列值的大小排序生成排名，不是按行的首次出现顺序，所以结果不符合预期。

解决方案 #

核心思路是先确定每个重复组的首次出现位置（用组内最小的id，因为id对应原始数据顺序），再基于这个首次位置生成排名，最后关联回原表。

写法一：分组+关联

SELECT
    t.id,
    t.col1,
    t.col2,
    t.col3,
    grp.rank
FROM test_table t
JOIN (
    -- 分组获取每个重复组的首次出现id，再按该id排序生成组排名
    SELECT
        col1,
        col2,
        col3,
        DENSE_RANK() OVER (ORDER BY MIN(id)) AS rank
    FROM test_table
    GROUP BY col1, col2, col3
) grp ON t.col1 = grp.col1 AND t.col2 = grp.col2 AND t.col3 = grp.col3
ORDER BY t.id; -- 保持原始行顺序

写法二：窗口函数标记首次出现id

如果你的数据库支持窗口函数的PARTITION BY，可以用更简洁的写法：

SELECT
    id,
    col1,
    col2,
    col3,
    DENSE_RANK() OVER (ORDER BY first_occurrence_id) AS rank
FROM (
    -- 给每行标记所在组的首次出现id
    SELECT
        *,
        MIN(id) OVER (PARTITION BY col1, col2, col3) AS first_occurrence_id
    FROM test_table
) t
ORDER BY id;

说明 #

1. 两种写法都是先找到每个重复组的首次出现行id，这个id决定了组的排名顺序；
2. DENSE_RANK()保证排名按首次出现顺序从1开始递增，且重复组排名一致；
3. 最后按id排序确保输出的行顺序和原始表一致。

执行以上任意一种SQL，都能得到你期望的结果。

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