row_number()、rank()、dense_rank()排序方式的区别

row_number()、rank()、dense_rank()排序方式的区别

1.row_number() 排序策略，连续排序，它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，例如1,2,3,4

WITH workers AS (
SELECT ‘DOMA’ dept ,‘zhangsan’ names,23 age,4000 salaries FROM dual
UNION ALL
SELECT ‘DOMA’ dept ,‘lisi’ names,35 age,9000 salaries FROM dual
union all
SELECT ‘DOMB’ dept ,‘wangwu’ names,26 age,6500 salaries FROM dual
UNION ALL
SELECT ‘DOMB’ dept ,‘zhaoliu’ names,28 age,7000 salaries FROM dual
UNION ALL
SELECT ‘DOMB’ dept ,‘maqi’ names,26 age,6000 salaries FROM dual
UNION ALL
SELECT ‘DOMA’ dept ,‘fengba’ names,25 age,6500 salaries FROM dual
UNION ALL
SELECT ‘DOMB’ dept ,‘sujiu’ names,25 age,7000 salaries FROM dual
)
  SELECT names,dept,row_number() OVER(PARTITION BY dept ORDER BY age DESC) rank FROM workers;

结果如下图

2.dense_rank() 排序策略，连续排序，如果有两个同一级别时，接下来是第二级别 ，例如1,2,2,3

WITH workers AS (
SELECT ‘DOMA’ dept ,‘zhangsan’ names,23 age,4000 salaries FROM dual
UNION ALL
SELECT ‘DOMA’ dept ,‘lisi’ names,35 age,9000 salaries FROM dual
union all
SELECT ‘DOMB’ dept ,‘wangwu’ names,26 age,6500 salaries FROM dual
UNION ALL
SELECT ‘DOMB’ dept ,‘zhaoliu’ names,28 age,7000 salaries FROM dual
UNION ALL
SELECT ‘DOMB’ dept ,‘maqi’ names,26 age,6000 salaries FROM dual
UNION ALL
SELECT ‘DOMA’ dept ,‘fengba’ names,25 age,6500 salaries FROM dual
UNION ALL
SELECT ‘DOMB’ dept ,‘sujiu’ names,25 age,7000 salaries FROM dual
)
  select names,dept,dense_rank() over(partition by dept order by age desc) rank from workers;

结果如下图

3.rank() 排序策略， 跳跃排序，如果有两个同一级别时，接下来是第三级别,例如1,2,2,4

	WITH workers AS (
	SELECT ‘DOMA’ dept ,‘zhangsan’ names,23 age,4000 salaries FROM dual
	UNION ALL
	SELECT ‘DOMA’ dept ,‘lisi’ names,35 age,9000 salaries FROM dual
	union all
	SELECT ‘DOMB’ dept ,‘wangwu’ names,26 age,6500 salaries FROM dual
	UNION ALL
	SELECT ‘DOMB’ dept ,‘zhaoliu’ names,28 age,7000 salaries FROM dual
	UNION ALL
	SELECT ‘DOMB’ dept ,‘maqi’ names,26 age,6000 salaries FROM dual
	UNION ALL
	SELECT ‘DOMA’ dept ,‘fengba’ names,25 age,6500 salaries FROM dual
	UNION ALL
	SELECT ‘DOMB’ dept ,‘sujiu’ names,25 age,7000 salaries FROM dual
	)
select names,dept,rank() over(partition by dept order by age desc) rank from workers;

结果如下图

WITH workers AS (
SELECT 'DOMA' dept ,'zhangsan' names,23 age,4000 salaries FROM dual
UNION ALL
SELECT 'DOMA' dept ,'lisi' names,35 age,9000 salaries FROM dual
union all
SELECT 'DOMB' dept ,'wangwu' names,26 age,6500 salaries FROM dual
UNION ALL
SELECT 'DOMB' dept ,'zhaoliu' names,28 age,7000 salaries FROM dual
UNION ALL
SELECT 'DOMB' dept ,'maqi' names,26 age,6000 salaries FROM dual
UNION ALL
SELECT 'DOMA' dept ,'fengba' names,25 age,6500 salaries FROM dual
UNION ALL
SELECT 'DOMB' dept ,'sujiu' names,25 age,7000 salaries FROM dual
)
--row_number() 排序策略，连续排序，它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，例如1,2,3,4
SELECT names,dept,row_number() OVER(PARTITION BY dept ORDER BY age DESC) rank FROM workers;

–dense_rank() 连续排序，如果有两个同一级别时，接下来是第二级别
select names,dept,dense_rank() over(partition by dept order by age desc) rank from workers;

–rank() 跳跃排序，如果有两个第一级别时，接下来是第三级别
select names,dept,rank() over(partition by dept order by age desc) rank from workers;

关于Parttion by：
　　Parttion by关键字是Oracle中分析性函数的一部分，用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列，
能够返回一个分组中的多条记录（记录数不变），而Group by是对原始数据进行聚合统计，一般只有一条反映统计值的结果（每组返回一条）。