OVER(PARTITION BY)函数demo

-- 建表
 create table EMP  
(  
  empno    NUMBER(4) not null,  
  ename    VARCHAR2(10),  
  job      VARCHAR2(9),  
  mgr      NUMBER(4),  
  hiredate DATE,  
  sal      NUMBER(7,2),  
  comm     NUMBER(7,2),  
  deptno   NUMBER(2)  
)  

alter table EMP   -- 修改表格
  add constraint PK_EMP primary key (EMPNO); -- 给EMPNO添加唯一约束
	 
-- 造数据	
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);  
			 
			 
SELECT * FROM  	emp	 

-- 一、rank()/dense_rank() over(partition by ...order by ...)

--  查询每个部门工资最高的雇员的信息：			
 
    -- 常用方法
select * from (select ename 姓名, job 职业, hiredate 入职日期, e.sal 工资, e.deptno 部门  
          from emp e,  
               (select deptno, max(sal) sal from emp group by deptno) t  
         where e.deptno = t.deptno  
           and e.sal = t.sal)  
 order by 部门; 

       -- rank() over(partition by...)或dense_rank() over(partition by...)语法
			 
			           -- over:  在什么条件之上。
                 -- partition by deptno:  按部门编号划分（分区）。
                 -- order by sal desc:  按工资从高到低排序（使用rank()/dense_rank() 时，必须要带order by否则非法）
								 -- rank()/dense_rank():  分级
                 -- 整个语句的意思就是：在按部门划分的基础上，按工资从高到低对雇员进行分级，“级别”由从小到大的数字表示（最小值一定为1）。 
								 -- rank():  跳跃排序，如果有两个第一级时，接下来就是第三级。
                 -- dense_rank():  连续排序，如果有两个第一级时，接下来仍然是第二级。
------ 最高工资								 
select empno, ename, job, hiredate, sal, deptno  
  from (select empno, ename, job, hiredate, sal, deptno, rank() over(partition by deptno order by sal desc) r from emp)  
 where r = 1;  
   
select empno, ename, job, hiredate, sal, deptno  
  from (select empno, ename, job, hiredate, sal, deptno, dense_rank() over(partition by deptno order by sal desc) r from emp)  
 where r = 1 
------ 最低工资	  
               desc 改成 asc

 
 --  二、min()/max() over(partition by ...)
 
     --查询雇员信息的同时算出雇员工资与部门最高/最低工资的差额。
 
  select ename 姓名, job 职业, hiredate 入职日期, e.deptno 部门, e.sal 工资, e.sal-me.min_sal 最低差额, me.max_sal-e.sal 最高差额  
  from emp e, (select deptno, min(sal) min_sal, max(sal) max_sal from emp group by deptno) me    
 where e.deptno = me.deptno order by e.deptno, e.sal; 
 
       --配合over(partition by ...)使用
			 
		 select ename 姓名, job 职业, hiredate 入职日期, deptno 部门,   
     min(sal) over(partition by deptno) 部门最低工资,   
     max(sal) over(partition by deptno) 部门最高工资   
     from emp order by deptno, sal;    
  
     select ename 姓名, job 职业, hiredate 入职日期, deptno 部门,   
     nvl(sal - min(sal) over(partition by deptno), 0) 部门最低工资差额,   
     nvl(max(sal) over(partition by deptno) - sal, 0) 部门最高工资差额  
     from emp order by deptno, sal;  
		 
		 
	-- 三、lead()/lag() over(partition by ... order by ...)
	   -- 这两个函数，是偏移量函数，其用途是：可以查出同一字段下一个值或上一个值。
		 -- 计算个人工资与比自己高一位/低一位工资的差额
		 -- lead(col_name,num,flag)
     -- col_name是列名；num是取向下第几个值；flag是一个标志，也就是如果向下第几个值是空值的话就取flag；
     -- 例如lead(login_time,1,null)这个是向下取一个值，如果这个值为空则按空算，当然也可以用其他值替换。
     -- lag(col_name,num,flag)
     -- 和lead类似，col_name是列名；num是取向上第几个值；flag是一个标志，也就是如果向上第几个值是空值的话就取flag；
     -- 例如lag(login_time,1,null)这个是向上取一个值，如果这个值为空则按空算，当然也可以用其他值替换。
		 
		 select ename 姓名, job 职业, sal 工资, deptno 部门,    
     lead(sal, 1, 0) over(partition by deptno order by sal)  比自己工资高的部门前一个,    
     lag(sal, 1, 0) over(partition by deptno order by sal)  比自己工资低的部门后一个,    
     nvl(lead(sal) over(partition by deptno order by sal) - sal, 0)  比自己工资高的部门前一个差额,     
     nvl(sal - lag(sal) over(partition by deptno order by sal), 0)   比自己工资高的部门后一个差额  
     from emp;  
		 
	-- 四 
	select ename 姓名, job 职业, sal 工资, deptno 部门,   
    first_value(sal) over(partition by deptno) first_sal,   
    last_value(sal) over(partition by deptno) last_sal,   
    sum(sal) over(partition by deptno) 部门总工资,   
    avg(sal) over(partition by deptno) 部门平均工资,   
    count(1) over(partition by deptno) 部门总数,   
    row_number() over(partition by deptno order by sal) 序号   
 from emp;