SQL分析函数
[JAVA]-SQL分析函数
[TOC]
1.ROLLUP和CUBE语句
要使用CUBE，首先要了解GROUP BY
其实CUBE和ROLLUP区别不太大，只是在基于GROUP BY 子句创建和汇总分组的可能的组合上有一定差别，CUBE将返回的更多的可能组合
CUBE和ROLLUP之间的区别在于：
CUBE生成的结果集显示了所选列中值的所有组合的聚合
ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合
ROLLUP就是将GROUP BY后面的第一列名称求总和，而其他列并不要求，而CUBE则会将每一个列名称都求总和
示例分析：
- GROUP BY ROLLUP(A, B, C)
首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作
- GROUP BY CUBE(A, B, C)
首先对全表进行group by，然后会对(A、B、C)进行GROUP BY，，然后依次是(A、B)，(A、C)，(B、C)，(A)，(B)，(C) 进行group by，最后对全表进行GROUP BY操作
-- 一、分组统计 group by rollup 先详细列出然后再进行统计
select *from emp
select deptno,ename,count(*)
from emp
group by rollup(deptno, ename)
--等价于
--1.首先会对(A、B)进行GROUP BY，
select deptno,ename,count(*)
from emp
group by (deptno, ename)
--2.然后对(A)进行GROUP BY，
select deptno,count(*)
from emp
group by deptno
--3最后对全表进行GROUP BY操作。
select count(*) from emp
--二、分组统计 cube 先进行统计 然后罗列详细
select deptno,ename,count(*)
from emp
group by cube(deptno, ename)
每部门的平均薪资，总平均薪资
select deptno, round(avg(sal),2)
from emp
group by rollup(deptno)
每部门每年每种工作的平均薪资，总平均薪资
select deptno,to_char(hiredate,'yyyy'),job,avg(sal)
from emp
group by cube(deptno,to_char(hiredate,'yyyy'),job)
2.分析函数语法
FUNCTION_NAME(<argument>,<argument>...)
OVER
(<Partition-Clause><Order-by-Clause><Windowing Clause>)
【1】over()
over()指定参数：partition by与order by
如果sum(sal) over() 如果over里面什么都不写，就相当于sum(sal)本身（在功能上，），不完全对（在语法格式上可以当做列出现）
| 指定参数 | 说明 |
|---|---|
| partition by | 按照指定的字段分区，如果没有则针对全体数据 |
| order by | 按照指定字段进行连续操作（如求和(sum),排序(rank()等)， 如果没有指定，就相当于对指定分区集合内的数据进行整体sum操作 |
- order by
--参数 order by
select deptno,
ename,
sal,
sum(sal) over() sum1, -- over()不写任何参数相当于求和sum(sal)
sum(sal) over( order by ename) sum2 --表示连续的求和 就是对之前的结果不断叠加
from emp;
- partition by
--参数partition by
select deptno,
ename,
sal,
sum(sal) over(partition by deptno) sum1 --表示的安装指定的分组进行部分叠加总和
from emp;
- 两者结合
select deptno,
ename,
sal,
--表示按照部门分组 然后安装姓名排序求总和
sum(sal) over(partition by deptno order by ename) sum,
-- 表示部门分组求和
sum(sal) over(partition by deptno) sum2，
-- 表示按照部门分组，按照薪水排序 ，在部门内求和叠加
sum(sal) over(partition by deptno order by sal) sum3
from emp;
【2】rank函数
rank()函数主要用于排序，并给出序号
ROW_NUMBER：
row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
DENSE_RANK：
dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的
RANK：
rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名
示例数据：
1，2，2，4，5，6，...... 这是rank()的形式
1，2，2，3，4，5，...... 这是dense_rank()的形式
1，2，3，4，5，6，...... 这是row_number()的形式
【3】窗口函数
select ename,
sal,
sum(sal) over( order by sal rows between 1 preceding and 1 following) res
from emp;
3.案例分析
【1】SQL分析函数案例分析
查询每个部门中每个岗位收入和
select deptno,job,sum(sal)
from emp
group by job,deptno
order by deptno
查询每个部门中每个岗位收入和，和每个部门收入和
select deptno,
job,
sum(sal) sum_dept_job,
sum(sum(sal)) over(partition by deptno) sum_dept
from emp
group by job,deptno
order by deptno
查询每个部门中岗位总工资占部门总工资40%的部门
select *
from(
select deptno,
job,
sum(sal) sum_dept_job,
sum(sum(sal)) over(partition by deptno) sum_dept
from emp
group by job,deptno
order by deptno) t
where t.sum_dept_job>t.sum_dept*0.4
查询每个部门中岗位总工资占部门总工资40%的部门，并求出比例
select t.*, 100* round(sum_dept_job/ sum_dept,2)||'%' prec
from(
select deptno,
job,
sum(sal) sum_dept_job,
sum(sum(sal)) over(partition by deptno) sum_dept
from emp
group by job,deptno
order by deptno) t
where t.sum_dept_job>t.sum_dept*0.4
查询工资最低排名前4的员工信息
select *
from (
select e.*, rank() over(order by sal) r from emp e
)
where r<=4;
查询工资最高的前两名
select * from emp order by sal desc
create table myemp as select * from emp;
update myemp set sal='' where empno=7369;
select * from myemp
select *
from (
select e.*, rank() over(order by sal desc) r from myemp e
)
where r<=2;
--如果涉及到空值这种情况就不正确
select *
from (
select e.*, rank() over(order by sal desc nulls last) r from myemp e
)
查询每个部门发的工资，并同时查询所有部门发的总工资
select deptno,
sum(sal),
sum(sum(sal)) over() all_dept
from emp
group by deptno
查询每个部门发的工资，并同时查询截止当前部门所有部门发的总工资
select deptno,
sum(sal),
sum(sum(sal)) over(order by deptno rows between unbounded preceding and current row) all_dept --了解
from emp
group by deptno
返回每个员工的姓名和工资以及低于自己的最高工资和高于自己的最低工资
select ename,
sal,
nvl(lead(sal) over(order by sal),min(sal) over()),
nvl(lag(sal) over(order by sal),max(sal) over())
from emp