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;
在使用排名函数的时候需要注意以下三点：
- 排名函数必须有 over 子句。
- 排名函数必须有包含 ORDER BY 的 over 子句。
- 分组内从1开始排序。
除此之外，条件分析函数还有：
- count() over(partition by … order by …)
- max() over(partition by … order by …)
- min() over(partition by … order by …)
- sum() over(partition by … order by …)
- avg() over(partition by … order by …)
- first_value() over(partition by … order by …)
- last_value() over(partition by … order by …)
- lag() over(partition by … order by …)
- lead() over(partition by … order by …)
- ...
感兴趣的可以试一下，此处就不一一讲解了。
