Oracle分析函数汇总（超全）

前半部分数据源为Oracle自带HR用户下的员工信息表：EMPLOYEES，若创建库的时候未勾选创建示例，可以在这下载sql文件，创建相关表及数据源。（百度网盘链接：https://pan.baidu.com/s/1axlyRjfEGi0pOi8xmRewrA   密码：t3yy）

中间部分的数据源为Oracle自带SCOTT用户下的员工表&部门表：EMP、DEPT

后半部分属于转载，暂无数据源，但是方便归纳总结：https://blog.csdn.net/cc_0101/article/details/80884076

 

--1、查询各个部门的平均工资，以及该部门的员工信息

SELECT 
	A.MANAGER_ID,A.EMPLOYEE_NAME,A.HIRE_DATE,A.SALARY,B.AVG_SALARY
FROM
	(SELECT MANAGER_ID,FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY FROM EMPLOYEES) A,
	(SELECT MANAGER_ID,AVG(SALARY) AVG_SALARY FROM EMPLOYEES GROUP BY MANAGER_ID) B
WHERE A.MANAGER_ID=B.MANAGER_ID
ORDER BY A.MANAGER_ID;

SELECT 
	MANAGER_ID,FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME,HIRE_DATE,SALARY,
	AVG(SALARY) OVER (PARTITION BY MANAGER_ID) AVG_SALARY
FROM EMPLOYEES;

--2、按照入职时间排序，并计算第一个员工到当前员工的平均工资（如果时间一样则相同时间一起计算）

SELECT 
	MANAGER_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY, 
	AVG(SALARY) OVER (ORDER BY HIRE_DATE)
FROM EMPLOYEES;

--3、按照入职时间排序，且按照部门经理进行分组，并计算该部门第一个员工到当前员工的平均工资（如果时间一样则相同时间一起计算）

SELECT 
	MANAGER_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY, 
	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE)
FROM EMPLOYEES;

--ROWS表示行
--4、按照入职时间排序，且按照部门经理进行分组，计算当前员工的前一个到后两个共四个员工的平均工资（如果时间一样，则默认按照先后顺序计算）

SELECT 
	MANAGER_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY, 
	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
FROM EMPLOYEES;

--RANGE表示取值范围（数字和日期可以取值）
--5、按照入职时间排序，且按照部门经理分组，计算当前员工雇佣时间之前的50天以内，之后的150天以内之间的平均工资（未验证前50，后150的边界问题）

SELECT 
	MANAGER_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY,
	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING)
FROM EMPLOYEES;

/*窗口子句中我们经常用到指定第一行，当前行，最后一行这样的三个属性：
第一行是 unbounded preceding，
当前行是 current row，
最后一行是 unbounded following*/

--求平均做一个总结，并展示第一个到最后一个的取值方法

SELECT 
	MANAGER_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY,
	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE) AVG_SALARY_PART_ORDER,--累计求平均，和第3个一样
	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ) AVG_SALARY_ORDER,--求整个部门的平均，和第1个一样
	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AVG_SALARY_UNBOUND1, --求整个部门的平均，表示取值范围第一个到最后一个，结果和上面一致
	AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AVG_SALARY_UNBOUND2--求整个部门的平均，行号取值范围第一个到最后一个，结果和上面一致
FROM EMPLOYEES;

--按照deptno分组，然后计算当前行至最后一行的汇总
SELECT 
	EMPNO,ENAME,DEPTNO,SAL,
	SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) MAX_SAL
FROM EMP;


--按照deptno分组，然后计算当前行的上一行(rownum-1)到当前行的汇总
SELECT 
	EMPNO,ENAME,DEPTNO,SAL,
    SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) MAX_SAL
FROM EMP;


--按照deptno分组，然后计算当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总
SELECT 
	EMPNO,ENAME,DEPTNO,SAL,
    SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) MAX_SAL
FROM EMP;
--注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总

 

--MIN、MAX、AVG、SUM、COUNT

SELECT 
	DEPARTMENT_ID, FIRST_NAME||' '||LAST_NAME EMPLOYEE_NAME, HIRE_DATE, SALARY,
	MIN(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) AS DEPT_MIN,--按照部门ID分组，然后按照入职时间排序，计算包括当前员工入职时间及之前的所有员工的最低薪资（如果时间一样，则一起计算）
	MAX(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) AS DEPT_MAX,--按照部门ID分组，然后按照入职时间排序，计算包括当前员工入职时间及之前的所有员工的最高薪资（如果时间一样，则一起计算）
	AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) AS DEPT_AVG,--按照部门ID分组，然后按照入职时间排序，计算该部门第一个员工到当前员工的平均工资（如果时间一样则相同时间一起计算）
	SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) AS DEPT_SUM,--按照部门ID分组，然后按照入职时间排序，计算该部门第一个员工到当前员工的工资和（如果时间一样则相同时间一起计算）
	COUNT(1) OVER (ORDER BY SALARY) AS COUNT_BY_SALARY,--按照工资进行排序，统计从第一个到当前工资的个数（如果工资一样，则一起统计）
	COUNT(1) OVER (ORDER BY SALARY RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS COUNT_BY_SALARY_RANGE--按照工资进行排序，统计比当前工资小50到比当前工资大150的个数
FROM EMPLOYEES;

--RANK、DENSE_RANK
/*row_number的用途非常广泛，排序最好用它，它会为查询出来的每一行记录