代码之家 › 专栏 › 技术社区 › ojek

如何对SQL结果进行分组和编号?

partition-by sql

1

ojek · 技术社区 · 5 月前

我显然不明白为什么PARTITION BY存在。我认为它应该按特定列对结果进行分组(分区),这样如果对该列应用row_number(),它将对项目的组(分区)进行编号。事实显然并非如此。有人能向我解释一下,分区应该做什么吗?

在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 回复 | 直到 5 月前

1

4

Barbaros Özhan 5 月前

自从 ROW_NUMBER() 每次分组都是递增的,你实际上似乎在使用 DENSE_RANK() 功能达到预期效果。您还需要按以下方式订购 X 不要被它分割。

SELECT cte.*, DENSE_RANK() OVER (ORDER BY X) AS group_number
  FROM cte