如何对SQL查询结果分组并为组编号？PARTITION BY功能解析

我原本以为PARTITION BY会按指定列对查询结果进行分组（分区），配合row_number()使用会为每个分组（分区）统一编号，但实际结果并非如此。请解释PARTITION BY的真正作用是什么？我该如何实现预期结果——按X列分组并为每个组分配统一编号？

测试代码 #

WITH cte(X,Y) AS
(
 SELECT 10 AS X, 1 AS Y UNION ALL
 SELECT 10 AS X, 2 AS Y UNION ALL
 SELECT 10 AS X, 3 AS Y UNION ALL
 SELECT 10 AS X, 4 AS Y UNION ALL
 SELECT 10 AS X, 5 AS Y UNION ALL
 SELECT 20 AS X, 1 AS Y UNION ALL
 SELECT 20 AS X, 2 AS Y UNION ALL
 SELECT 20 AS X, 3 AS Y UNION ALL
 SELECT 20 AS X, 4 AS Y UNION ALL
 SELECT 20 AS X, 5 AS Y
)
SELECT cte.*, 
       ROW_NUMBER() OVER (PARTITION BY cte.X ORDER BY cte.X) AS [GROUP_NUMBER] 
  FROM cte

实际执行结果 #

X   Y   GROUP_NUMBER
10  2   1
10  3   2
10  4   3
10  5   4
10  1   5
20  1   1
20  2   2
20  3   3
20  4   4
20  5   5

预期结果 #

X   Y   GROUP_NUMBER
10  2   1
10  3   1
10  4   1
10  5   1
10  1   1
20  1   2
20  2   2
20  3   2
20  4   2
20  5   2

解答 #

1. PARTITION BY的真正作用 #

PARTITION BY是窗口函数的分区子句，核心作用是将整个查询结果集拆分为多个独立的"窗口"（分区），窗口函数（如ROW_NUMBER、RANK等）会在每个分区内独立计算，不会像GROUP BY那样将分组内的行聚合为单行。

你使用ROW_NUMBER() OVER (PARTITION BY X ORDER BY X)时，ROW_NUMBER会在每个X对应的分区内，为每行分配一个唯一的递增序号——这就是为什么同一个X的行得到了1、2、3...的连续编号，而非你预期的统一编号。

2. 实现分组统一编号的方案 #

要给同一X值的所有行分配相同的连续编号，你需要使用DENSE_RANK()窗口函数，且不需要PARTITION BY，只需要按X排序即可：

WITH cte(X,Y) AS
(
 SELECT 10 AS X, 1 AS Y UNION ALL
 SELECT 10 AS X, 2 AS Y UNION ALL
 SELECT 10 AS X, 3 AS Y UNION ALL
 SELECT 10 AS X, 4 AS Y UNION ALL
 SELECT 10 AS X, 5 AS Y UNION ALL
 SELECT 20 AS X, 1 AS Y UNION ALL
 SELECT 20 AS X, 2 AS Y UNION ALL
 SELECT 20 AS X, 3 AS Y UNION ALL
 SELECT 20 AS X, 4 AS Y UNION ALL
 SELECT 20 AS X, 5 AS Y
)
SELECT cte.*, 
       DENSE_RANK() OVER (ORDER BY cte.X) AS [GROUP_NUMBER] 
  FROM cte

原理说明：

• DENSE_RANK()会根据ORDER BY X的顺序，为相同X值的所有行分配同一个排名，并且排名是连续的（不会跳过序号），完全符合你的预期结果。
• 如果你使用RANK()，当X值存在间隔时会跳过序号（比如如果有X=10、10、30，RANK会给10分配1，30分配3），而DENSE_RANK()会分配1和2，更贴合"连续统一编号"的需求。

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