SQL组内排序

最新推荐文章于 2025-05-25 15:15:00 发布

原创最新推荐文章于 2025-05-25 15:15:00 发布 · 3.3k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

数据库专栏收录该内容

4 篇文章

订阅专栏

本文探讨了SQL中组内排序的使用场景，详细解析了row_number()、rank()、dense_rank()的区别，并通过实例展示了它们在数据排序中的应用。在多线程问题分析中，正确使用这些函数有助于清晰地查看每个线程的执行情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 使用场景

最近在调试一个多线程的问题，为了能够清晰的看出每个线程的执行情况，我们用日志简单的记录了下每一步的执行结果，进而分析问题出错的原因。

在执行成功的情况下，我们每个项目每次会记录6条日志，首先我们的需求是：

对项目执行结果进行分组，一个项目的结果在一起
分组后对组进行排序，按时间由近至远排序
组内排序，按照执行时间由远至近
组内排序后显示序号123…

SELECT 
	a.ID,a.PLAN_ID,b.PLAN_NAME,a.PRO_ID,c.PRO_NAME,a.PERFORM_ACTION,
	a.DURATION,a.PERFORM_DATE,a.IDENTIFICATION 
FROM SPKBKT_ROLL_PLAN_JOURNAL a 
JOIN SPKBKT_ROLL_PLAN b ON a.PLAN_ID = b.ID
JOIN SPKBKT_PRO_PROJECT c ON a.PRO_ID = c.ID;

在这里插入图片描述
这是什么什么都没加的情况，我们可以看到查出来的结果很乱，看不到我想要的东西。

接下来我们做一个简单的分组，为什么要用ORDER BY来做，因为是这样的，由于采用的多线程，各个线程触发时间十分相近，但是我们需要对每一个项目进行分组，所以在此处，我们做了一个唯一标识IDENTIFICATION，每个项目每次执行时记录的6条日志里都会存储这个唯一标识。

IDENTIFICATION的组成结构：前13位是一个精确到毫秒的时间戳，后4位是项目ID，中间的部分为计划ID（每个计划中有若干个项目）。

SELECT 
	a.ID,a.PLAN_ID,b.PLAN_NAME,a.PRO_ID,c.PRO_NAME,a.PERFORM_ACTION,
	a.DURATION,a.PERFORM_DATE,a.IDENTIFICATION 
FROM SPKBKT_ROLL_PLAN_JOURNAL a 
JOIN SPKBKT_ROLL_PLAN b ON a.PLAN_ID = b.ID
JOIN SPKBKT_PRO_PROJECT c ON a.PRO_ID = c.ID 
ORDER BY a.IDENTIFICATION;

在这里插入图片描述
分组后看起来清晰了很多，各项目执行结果都在一起。但是目前不太好识别各项目执行情况，于是我们继续加了一下组内排序，通过序号我们就可以看出哪个步骤出问题了，如下图，有2个项目执行结果不足6步。

SELECT 
	a.ID,a.PLAN_ID,b.PLAN_NAME,a.PRO_ID,c.PRO_NAME,a.PERFORM_ACTION,
	a.DURATION,
	row_number() over(partition BY a.IDENTIFICATION ORDER BY a.ID) AS NUM,
	a.PERFORM_DATE,a.IDENTIFICATION 
FROM SPKBKT_ROLL_PLAN_JOURNAL a 
JOIN SPKBKT_ROLL_PLAN b ON a.PLAN_ID = b.ID
JOIN SPKBKT_PRO_PROJECT c ON a.PRO_ID = c.ID 
ORDER BY a.IDENTIFICATION DESC;

在这里插入图片描述
我们这里使用的分组排序语句是：

row_number() over(partition BY a.IDENTIFICATION ORDER BY a.ID) AS NUM

我们来看一下各个部分都是什么含义：

--查询语句按照IDENTIFICATION字段进行分组
partition BY a.IDENTIFICATION

--组内按照ID字段进行排序
ORDER BY a.ID

--生成组内序号
row_number() over() AS NUM

2 分析函数中row_number()、rank()、dense_rank() 的区别

row_number()的用途非常广泛，排序最好用它，它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number()函数时必须要用over子句选择对某一列进行排序才能生成序号。

rank()函数用于返回结果集的分区内每行的排名，行的排名是相关行之前的排名数加一。简单来说rank()函数就是对查询出来的记录进行排名，与row_number()函数不同的是，rank()函数考虑到了over子句中排序字段值相同的情况，如果使用rank()函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。

dense_rank()函数的功能与rank()函数类似，dense_rank()函数在生成序号时是连续的，而rank()函数生成的序号有可能不连续。dense_rank()函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()函数是跳跃排序，有两个第一名时接下来就是第三名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。

为了更够看的更直观些，我们来看个例子：

假设现在有一张学生表STUDENT，学生表中有姓名、分数、课程编号。

SELECT * FROM STUDENT;

在这里插入图片描述

现在需要按照课程对学生的成绩进行排序：

2.1 row_number()

--顺序排序
SELECT 
	ID,STUDENT_NAME,ACHIEVEMENT,
	row_number() over(partition BY CURRICULUM_CODE ORDER BY ACHIEVEMENT DESC) AS NUM,
	CURRICULUM_CODE 
FROM STUDENT;

在这里插入图片描述

2.2 rank()

--跳跃排序，如果有两个第1时，接下来是3
SELECT 
	ID,STUDENT_NAME,ACHIEVEMENT,
	rank() over(partition BY CURRICULUM_CODE ORDER BY ACHIEVEMENT DESC) AS NUM,
	CURRICULUM_CODE 
FROM STUDENT;

在这里插入图片描述

2.3 dense_rank()

--连续排序，如果有两个第1时，接下来是第2。
SELECT 
	ID,STUDENT_NAME,ACHIEVEMENT,
	dense_rank() over(partition BY CURRICULUM_CODE ORDER BY ACHIEVEMENT DESC) AS NUM,
	CURRICULUM_CODE 
FROM STUDENT;

在这里插入图片描述

取得每门课程的第一名：

--row_number()
--每门课程第一名只取一个
SELECT a.* FROM (
	SELECT 
		ID,STUDENT_NAME,ACHIEVEMENT,
		row_number() over(partition BY CURRICULUM_CODE ORDER BY ACHIEVEMENT DESC) AS NUM,
		CURRICULUM_CODE 
	FROM STUDENT
) a WHERE a.NUM = 1;

--rank()
--每门课程第一名取所有
SELECT a.* FROM (
	SELECT 
		ID,STUDENT_NAME,ACHIEVEMENT,
		rank() over(partition BY CURRICULUM_CODE ORDER BY ACHIEVEMENT DESC) AS NUM,
		CURRICULUM_CODE 
	FROM STUDENT
) a WHERE a.NUM = 1;

--dense_rank()
--每门课程第一名取所有
SELECT a.* FROM (
	SELECT 
		ID,STUDENT_NAME,ACHIEVEMENT,
		dense_rank() over(partition BY CURRICULUM_CODE ORDER BY ACHIEVEMENT DESC) AS NUM,
		CURRICULUM_CODE 
	FROM STUDENT
) a WHERE a.NUM = 1;