【Oracle 11g】(八) 分析函数、开窗函数

【Oracle 11g】(八) 分析函数、开窗函数

over()是开窗函数，

over()可以区分avg()是聚合函数还是分析函数。

分析函数由聚合函数和开窗函数组成的。

一、聚合函数+开窗函数

--需求-1：
--查询出员工的姓名、工资、所在部门的平均工资，
--要求显示出来的是工资大于部门平均工资的员工。

--1）连接查询的方式
SELECT e.empno, e.ename, e.sal, t.avg_sal
FROM emp e, (SELECT deptno, AVG(sal) as avg_sal 
             from emp
             GROUP BY deptno
             )t
WHERE e.deptno = t.deptno AND avg_sal < e.sal;

--2）分析函数--聚合函数+开窗函数
SELECT *
FROM(
SELECT empno, ename, sal, deptno, 
       AVG(sal)OVER(PARTITION BY deptno) as avg_sal 
FROM emp
ORDER BY deptno
)t
WHERE avg_sal < sal; 

        

------- -------- ------ ------- ------- ------- -------- ------ ------- -------

--需求-3：
--按照部门求出各部门员工总薪水、所有员工薪水总和、按部门累计求和员工薪水
SELECT empno,ename,sal,comm,deptno,
       SUM(sal)OVER(PARTITION BY deptno)sum_sal,
       SUM(sal)OVER()sum_sal,
       SUM(sal)OVER(PARTITION BY deptno ORDER BY sal) leiji_sumsal  --累计求和
FROM emp;

二、等级函数+开窗函数

--需求-3：
--查询出员工表中，工资排名前3位
--三种排名函数
SELECT * 
FROM(
SELECT e.empno, e.ename, e.sal, 
       rank()over(order by sal desc) rank_sal,             --排名可重复且有跳位   eg:1 2 2 4
       dense_rank()over(order by sal desc) dense_rank_sal, --排名可重复且没有跳位  eg:1 2 2 3
       row_number()over(order by sal desc) row_number_sal  --排名无重复情况       eg:1 2 3 4
FROM emp e
)t
WHERE dense_rank_sal <= 3;