count( *{【distinct】| all | expr } ) over( analytic_clause (分区子句)) ;
sum() | avg() | max和min | rank() 和 dense_rank() | first()和last() | lag() 和 lead()
dense_rank() 和 rank() 的区别:
1.rank() 1,2,2,4 ;
2.dense_rank() 1,2,2,3
总结:rank() 遇到重复的会跳号 ,而dense_rank()不跳号;
1.keep()
练习题1.查询员工薪资中最高和最低的员工编号?
SELECT MIN(EMPNO) KEEP(DENSE_RANK FIRST ORDER BY SUM(SAL) DESC NULLS LAST) AS 首位,
MAX(EMPNO) KEEP(DENSE_RANK LAST ORDER BY SUM(SAL) DESC NULLS LAST) AS 末尾
FROM EMP
WHERE SAL IS NOT NULL
AND DEPTNO IS NOT NULL
GROUP BY EMPNO;
(1)min(empno) 用来限制first和last出现多个值得情况下返回唯一记录,与group by empno 对应;
(2)first 与 last 与dense_rank 搭配使用,返回排列规则下的首条和末条记录。
(3)order sum(sal) 按薪资大小进行排序,nulls last 指定null 列排在最后 ;
(4)keep 用于告诉oracle用于保存符合keep后面的语句记录 ;
2.LAG() 和 LEAD() ;
LAG(col【,n】【,n】)用于返回指定列前n行的值,如果前n行已经超过了比较的范围,则返回n2,如果不指定n2,则默认返回 null ,如果不指定n 则默认为1 ;
LEAD() 后n行 ;
练习题2.使用LAG和LEAD函数查找当前员工的前一个员工的薪水和后一个员工的薪水 ?
SELECT ENAME,
HIREDATE,
SAL,
DEPTNO,
LAG(SAL, 1, 0) OVER(ORDER BY HIREDATE) AS 前一个员工,
LEAD(SAL, 1, 0) OVER(ORDER BY HIREDATE) AS 后一个员工
FROM EMP
WHERE DEPTNO = 30
说明:lag()表示上一个 ,lead() 表示下一个 ;因为JAMES的sal(950)没有后一个了所以是0 ;