Oracle分析函数 OVER(PARTITION ORDER WINDOWING)（三）—— 排序顺序相关函数用法

需求一

求每个MGR管理的员工的薪资排名：

SELECT mgr, ename, sal,
             RANK() OVER(PARTITION BY mgr ORDER BY sal) AS nu1,
       DENSE_RANK() OVER(PARTITION BY mgr ORDER BY sal) AS nu2,
       ROW_NUMBER() OVER(PARTITION BY mgr ORDER BY sal) AS nu3
  FROM emp;
-- 求排名的三个函数RANK、DENSE_RANK和ROW_NUMBER必须使用ORDER BY
-- 否则记录分区后没有顺序的概念，无法进行排名
-- 当然PARTITION BY不是语法上必须的

-- 这三个函数是有区别的：
-- 函数RANK可以求出类似两个并列第2，然后下一个为排名第4，跳排名
-- 函数DENSE_RANK可以求出类似两个并列第2，然后下一个为排名第3，并不会跳排名
-- 函数ROW_NUMBER即便是并列的，也会有不同的排名，不存在排名并列的现象

需求二

求每个MGR管理的员工的入职顺序排名和最早/最晚入职员工的薪资：

SELECT mgr, ename, hiredate, sal,
       DENSE_RANK() OVER(PARTITION BY mgr ORDER BY hiredate) Nu,
       MIN(sal) KEEP(DENSE_RANK FIRST ORDER BY hiredate) OVER(PARTITION BY mgr) "First_Hire_Min_Sal",
       MAX(sal) KEEP(DENSE_RANK  LAST ORDER BY hiredate) OVER(PARTITION BY mgr) "Last_Hire_Max_Sal"
  FROM emp;
-- 根据MGR分区，根据入职时间排序，求出入职顺序排名
-- 根据MGR分区，根据入职时间排序，取出分区内排序后的记录的排名第一的记录的最小薪资
-- 根据MGR分区，根据入职时间排序，取出分区内排序后的记录的排名倒一的记录的最大薪资

-- 注意点：
-- 使用FIRST/LAST时 必须使用DENSE_RANK函数做排名，不可以使用RANK或ROW_NUMBER
-- 使用DENSE_RANK函数做排名时，存在并列现象，需要再次使用聚合对并列现象作筛选
-- 要考虑使用聚合函数对并列现象的筛选的意义，是否满足需求

需求三

求每个MGR管理的员工的薪资以及最低或最高薪资的员工名：

SELECT mgr, ename, sal,
       FIRST_VALUE(ename) OVER(PARTITION BY mgr ORDER BY sal) AS "Lowest_Sal_ENAME",
       LAST_VALUE (ename) OVER(PARTITION BY mgr ORDER BY sal) AS "Best_Sal_ENAME"
  FROM emp;
-- 根据mgr分区，根据sal排序，取出累积的第一条记录的ename列
-- 根据mgr分区，根据sal排序，取出累积的最后一条记录的ename列
-- 因为存在sal的并列现象，FIRST_VALUE会取并列的ename的第一个值
-- 而LAST_VALUE会取并列的ename的最后一个值
-- 累计的FIRST_VALUE永远是第一个值
-- 累计的LAST_VALUE 才是真正的计算累计

SELECT mgr, empno,ename, sal,
       FIRST_VALUE(ename) OVER(PARTITION BY mgr ) AS "Lowest_Sal_ENAME"
  FROM emp;

SELECT mgr, empno,ename, sal
  FROM emp
  order by mgr;

-- 使用FIRST_VALUE或者LAST_VALUE 不用强制ORDER BY进行排序
-- 如果不使用ORDER BY进行排序，那么取出的FIRST_VALUE或者LAST_VALUE就是根据mgr进行分区的默认顺序
-- 那么就不存在最高或者最低薪资的概念了
-- 比如上方两个SQL分区之后，区内的FIRST_VALUE和LAST_VALUE实际上就是同一条记录

需求四

求每个MGR管理的员工的薪资以及按入职时间排名的他之前的两名的员工的薪资和之后的两名的员工的薪资：

SELECT mgr,ename, hiredate, sal,
       LAG (sal, 2, 0) OVER(PARTITION BY mgr ORDER BY hiredate) prev2_sal,
       LEAD(sal, 2, 0) OVER(PARTITION BY mgr ORDER BY hiredate) next2_sal
  FROM emp;
-- 根据mgr分区，根据入职时间排序，求排序后，在他前面两名的员工的薪资
-- 根据mgr分区，根据入职时间排序，求排序后，在他后面两名的员工的薪资
-- 一般情况下是求该记录的上一条和下一条
-- 当排序后是第一名时，去他前面的几条记录都是不存在的，因此需要指定填充
-- 比如LAG (sal, 2, 0)，就是当他前面没有记录时，sal不存在时，填充0、
-- 设置的填充，要和取得字段类型相同，sal是number，不能设置一个非number的字符类填充

[TOC]