--AVG OVER
--按部门计算平均薪资
--每行显示每个员工的薪资、部门平均薪资及全公司薪资排名。
SELECTDEPARTMENT_ID,--部门IDEMPLOYEE_ID,--员工IDSALARY,--员工工资AVG(SALARY)OVER (PARTITION BY DEPARTMENT_ID)AS DEPTAVG,--部门平均工资RANK()OVER(ORDER BY SALARY DESC)AS SALARY_RANK --工资排名FROM DMHR.EMPLOYEE;
2.2 COUNT OVER
--COUNT OVER
--按部门统计员工数
--每行显示员工所属部门的员工总数
SELECTDEPARTMENT_ID,--部门IDEMPLOYEE_ID,--员工IDCOUNT(*)OVER(PARTITION BY DEPARTMENT_ID) AS DEPTEMP_COUNT --部门总人数
FROM DMHR.EMPLOYEE;
2.3 MIN OVER，MAX OVER，SUM OVER
--MIN OVER，MAX OVER，SUM OVER
--每行显示员工薪资及其所属部门的最低薪资，最高薪资，薪资总和，平均工资，总人数，工资排名SELECT DEPARTMENT_ID,--部门IDEMPLOYEE_ID,--员工IDSALARY,--员工薪资MIN(SALARY) OVER (PARTITION BY DEPARTMENT_ID)AS DEPTMIN_SALARY,--部门最低薪资MAX(SALARY) OVER (PARTITION BY DEPARTMENT_ID)AS DEPTMAX_SALARY,--部门最高薪资SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID)AS DEPTSUM_SALARY,--部门薪资总和AVG(SALARY)OVER (PARTITION BY DEPARTMENT_ID)AS DEPTAVG,--部门平均工资COUNT(*)OVER(PARTITION BY DEPARTMENT_ID)AS DEPTEMP_COUNT,--部门总人数RANK()OVER(ORDER BY SALARY DESC)AS SALARY_RANK --工资排名
FROM DMHR.EMPLOYEE ;
2.4 DENSE_RANK
--DENSE_RANK
--按部门统计工资出现次数，并生成密集排名
WITH SALARY_COUNT AS (SELECT DEPARTMENT_ID,SALARY,COUNT(*) AS OCCURRENCE -- 统计工资出现次数FROM DMHR.EMPLOYEEGROUP BY DEPARTMENT_ID, SALARY
)SELECT DEPARTMENT_ID,SALARY,OCCURRENCE,DENSE_RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY OCCURRENCE DESC) AS DENSERANKNUM -- 按出现次数降序生成密集排名
FROM SALARY_COUNT;
2.5 ROW_NUMBER
--ROW_NUMBER
--按部门统计工资出现次数，并生成顺序编号WITH SALARY_COUNT AS (SELECT DEPARTMENT_ID,SALARY,COUNT(*) AS OCCURRENCE -- 统计工资出现次数FROM DMHR.EMPLOYEEGROUP BY DEPARTMENT_ID, SALARY
)SELECT DEPARTMENT_ID,SALARY,OCCURRENCE,ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS DENSERANKNUM -- 按照顺序编号，不区分相同值，即从 1 开始编号
FROM SALARY_COUNT ORDER BY DEPARTMENT_ID ASC,SALARY DESC;
2.6 FIRST
--FIRST
--同时获取部门最低和最高薪资员工
SELECT DEPARTMENT_ID,MAX(EMPLOYEE_NAME) KEEP (DENSE_RANK FIRST ORDER BY SALARY)AS LOWEST_EMP,MAX(EMPLOYEE_NAME) KEEP (DENSE_RANK LAST ORDER BY SALARY)AS HIGHEST_EMP,MIN(SALARY)AS MIN_SALARY,MAX(SALARY) AS MAX_SALARY
FROM DMHR.EMPLOYEE
GROUP BY DEPARTMENT_ID;
2.7 LAG
--LAG
--按部门分区，获取部门内前一位员工薪资
SELECT DEPARTMENT_ID,EMPLOYEE_NAME,SALARY,LAG(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS PREV_SALARY
FROM DMHR.EMPLOYEE;
2.8 WM_CONCAT
--WM_CONCAT
--列转行，合并姓名列
SELECT DEPARTMENT_ID,TO_CHAR(WM_CONCAT(NVL(EMPLOYEE_NAME, '未知'))) AS EMPLOYEES
FROM DMHR.EMPLOYEE
GROUP BY DEPARTMENT_ID;
