分析函数介绍

–分析函数 OVER 是关键字
SQL语句SELECT 语句执行顺序：
1.select … from t where ? group by… having … order by …;
分析层级顺序：1.首先from 字句 2.where字句 3.group by 字句 4.having 5.select 展现 6.展现结果进行ORDER BY
重点：分析函数基于SELECT 展现结果集再次处理，它实现单行函数聚合函数功能，它不改变返回结果集行数只是在结果集后加一列汇总.over（partition by…）和group by 出现时
一定是group by 返回后又进行了分组。
分析函数有两种: 1.聚合函数 sum\counct\max\min\avg 2.排序函数： rownum 、rank（排序函数必须和ORDER BY 组合使用）。
Administrator 00:49:04
------分析函数:单行函数+聚合函数
------重点：针对结果集的再次处理
------需求场景:明细汇总+大汇总(over)
分析函数:sum,count,min,max,avg
排序函数:row_number ,rank

–语法：sum(字段) over([partition by 字段] [order by 字段] )

------分析函数语法sum,count,min,max,avg
select t.*,sum(sal) over() from emp t;—针对整个结果集的汇总

select t.*，sum(sal) over(partition by job ) from emp t ;

select t.job,sum(sal),sum(sum(sal)) over() from emp t group by t.job;

select ename,job,sum(sal),sum(sum(sal)) over(partition by job) from emp group by ename,job ;

-----排序函数：row_number() ,rank()
–语法：row_number() over(order by 字段)
–注意事项：row_number() ,rank() 前提要有order by 关键字
select t.*,
row_number() over(order by deptno ),
–rank() over(order by deptno ),

    row_number() over(partition by job order by deptno  ),
     rank() over(partition by job order by deptno  )            

from emp t;
1.实例：
select t.*,avg(sal) over(PARTITION BY deptno) avg_bm,
SUM(sal) over(PARTITION BY deptno) sum_bm,
count(sal) over(PARTITION BY deptno) cnt_bm,
max(sal) over(PARTITION BY deptno) max_bm
from emp t;

2.实例
分析函数功能：SELECT明细汇总后+ 结果集之后的大汇总
select job,count(1),sum(count(1))over() from emp1 group by job;

3.实例
select job,count(1) from emp1 group by rollup (job);