1.分布函数 CUME_DIST 和 percent_rank
用途: 分组内小于，等于当前rank值的行数 / 分组内总行数 求比例
应用场景: 查询小于等于当前薪资(salary)的比例
use mydb4; select dname, ename, salary, cume_dist() over(order by salary) as rn1, cume_dist() over(partition by dname order by salary) as rn2 from employee; /* 0.25 3/12=0.25 0.416 5/12=0.416 */
select dname, ename, salary, rank() over(partition by dname order by salary) as rn1, PERCENT_RANK() over(partition by dname order by salary) as rn2 from employee;
2.前后函数 -LAG和LEAD
用途: 返回位于当前行的前n行(LAG(expr,n))或后n行(LEAD(expr,n))的expr的值
应用场景:查询前一名同学的成绩和当前同学成绩的差值
select dname, ename, salary, hiredate, lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) AS time1, lag(hiredate,2) over(partition by dname order by hiredate) as time1 from employee; select dname, ename, salary, hiredate, lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) AS time1, lead(hiredate,2) over(partition by dname order by hiredate) as time1 from employee;
3.头尾函数 -FIRST_VALUE和LAST_VALUE
用途: 返回第一个FIRST_VALUE(expr) 或最后一个LAST_VALUE(expr)的值
应用场景: 截止到目前，按照日期排序查询第1个入职和最后1个入职员工的薪资
select dname, ename, salary, hiredate, FIRST_VALUE(salary) over(partition by dname order by hiredate) , LAST_VALUE(salary) over(partition by dname order by hiredate) from employee;
4.其他函数 -NTH_VALUE(expr,n),NTILE(n)
-NTH_VALUE(expr,n)
用途: 返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
应用场景:截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资
select dname, ename, salary, hiredate, nth_VALUE(salary,2) over(partition by dname order by hiredate) , nth_VALUE(salary,3) over(partition by dname order by hiredate) from employee;
NTILE(n)
用途:讲分区中的有序数据分为n个等级，记录等级数
应用场景:将每个部门员工按照入职日期分成3组
select dname, ename, salary, hiredate, NTILE(4) over(partition by dname order by hiredate)
-- 取出每一个部门的第一组员工 SELECT * from( select dname, ename, salary, hiredate, NTILE(3) over(partition by dname order by hiredate ) as rn from employee ) t where t.rn=1;
