Oracle分析函数
最近频繁用到几个分析函数，就把分析函数过一遍整理记录
本文主要参考链接：https://blog.csdn.net/cc_0101/java/article/details/80884076
一、基本语法
function_name(<argument>,<argument>...) over(<partition-Clause><order-by-Clause><windowing-Clause>);
function_name()：函数名
argument：参数
over( )：一个用于标识分析函数关键字
partition-Clause：分区子句
order-by-Clause：排序子句，结果集排序，
windowing-Clause：开窗子句，定义分析函数在操作行的集合，三种开窗方式：rows、range、Specifying
注：使用开窗子句时一定要有排序子句！！！
一、基本语法
function_name(<argument>,<argument>...) over(<partition-Clause><order-by-Clause><windowing-Clause>);
function_name()：函数名
argument：参数
over( )：一个用于标识分析函数关键字
partition-Clause：分区子句
order-by-Clause：排序子句，结果集排序，
windowing-Clause：开窗子句，定义分析函数在操作行的集合，三种开窗方式：rows、range、Specifying
注：使用开窗子句时一定要有排序子句！！！
分析函数汇总
1.count() over() ：统计分区中各组的行数，partition by 可选，order by 可选 //当前记录与本组的记录总量
--按deptno分组统计各组总数
select a.deptno,a.empno,a.ename,count(1) over( partition by a.deptno) from scott.emp a;
--按empno排序递增，包含order by 的count() 展示效果同函数row_number()
select a.deptno,a.empno,a.ename,count(1) over( order by a.empno) from scott.emp a;
-------------
2.sum() over() ：统计分区中记录的总和，partition by 可选，order by 可选 //当前记录与本组的总和
--按deptno分组统计各组sal总和
select a.deptno,a.empno,a.ename,sum(sal) over( partition by a.deptno) from scott.emp a;
--按empno排序累加sal
select a.deptno,a.empno,a.ename,sum(sal) over( order by a.empno) from scott.emp a;
-----------------
3、avg() over() ：统计分区中记录的平均值，partition by 可选，order by 可选 //当前记录与本组的平均值
--按deptno分组统计各组sal平均值
select a.deptno,a.empno,a.ename,avg(sal) over(partition by a.deptno) from scott.emp a;
--按empno排序累加sal求平均值
select a.deptno,a.empno,a.ename,avg(sal) over(order by a.empno) from scott.emp a;
--------------
4、min() over() /max() over()：统计分区中记录的最小/大值，partition by 可选，order by 可选 //当前记录与组的最小/大值
--按deptno分组统计各组最小sal，max同理
select a.deptno,a.empno,a.ename,min(sal) over(partition by a.deptno) from scott.emp a;
--按empno降序统计当前遇到的最小sal（即分析排序后行号小于等于当前行的数据），max同理
select a.deptno,a.empno,a.ename,min(sal) over(order by a.empno desc) from scott.emp a;
-------------------
5、rank() over() ：跳跃排序，排序字段值相同序号相同，partition by 可选，order by 必选 //记录跳跃排序
--按deptno跳跃排序（例：112334序：113446）
select a.deptno,a.empno,a.ename,rank() over(order by a.deptno) from scott.emp a;
--按deptno分组后各组按empno跳跃排序，以下语句empno唯一看不出跳跃效果
select a.deptno,a.empno,a.ename,rank() over(partition by a.deptno order by a.empno) from scott.emp a;
-----------------
6、dense_rank() ：连续排序，排序字段值相同序号相同，partition by 可选，order by 必选 //记录连续排序,相同排序值序号相同
--按deptno连续排序（例：112667序：112334）
select a.deptno,a.empno,a.ename,dense_rank() over(order by a.deptno) from scott.emp a;
--按deptno分组后各组按empno连续排序
select a.deptno,a.empno,a.ename,dense_rank() over(partition by a.deptno order by a.empno) from scott.emp a;
--------------------
7、row_number() over() ：连续排序，不考虑排序字段值，partition by 可选，order by 必选 //记录连续排序，相同排序值序号不同
--按deptno连续排序（例：112667序：123456）
select a.deptno,a.empno,a.ename,dense_rank() over(order by a.deptno) from scott.emp a;
--按deptno分组后各组按empno连续排序
select a.deptno,a.empno,a.ename,dense_rank() over(partition by a.deptno order by a.empno) from scott.emp a;
--------------
8、ntile(n) over() ：将查出的数据分成n份，（例n=3: 12458序：11223）partition by 可选，order by 必选 //用于按记录比例取相关记录
--按deptno排序，将记录分成3份
select a.deptno,a.empno,a.ename,ntile(3) over(order by a.deptno) from scott.emp a;
--按deptno分组后各组按empno排序，将各组数据记录分成三份
select a.deptno,a.empno,a.ename,ntile(3) over(partition by a.deptno order by a.empno) from scott.emp a;
-------------------
9、first_value() over() /last_value() over()：取出分区中第一条/最后一条记录的字段值，partition by 可选，order by 可选 //当前记录与第一条/最后一条的某值
--按empno排序取出第一条记录的sal，last同理
select a.deptno,a.empno,a.ename,first_value(a.sal) over(order by a.empno desc) from scott.emp a;
--按deptno分组，（按查询默认排序）按组取出每组第一条记录的sal，last同理
select a.deptno,a.empno,a.ename,first_value(a.sal) over(partition by a.deptno) from scott.emp a;
------------
10、first /last：从DENSE_RANK返回的集合中取出排在最前/后面的一个值的行 //取各部门工资排名第一/最后的员工
--按deptno分组，各组按sal排序取出第一个max (sal)和最后一个max(sal)
select deptno,max(empno) keep(dense_rank first order by sal ),max(sal) keep(dense_rank last order by sal ) from scott.emp group by deptno;
select deptno,max(sal),min(sal) from scott.emp group by deptno;
有关keep语法：min | max(column1) keep (dense_rank first | last order by column2) over (partion by column3);
最前是聚合函数，可以是min、max、avg、sum等等
column1 要计算的列；
dense_rank first，dense_rank last为keep 函数的保留属性，表示分组、排序结果集中第一个、最后一个；
解释：返回按照column3分组后，按照column2排序的结果集中第一个或最后一个最小值或最大值column1。
---------------
11、lag() over() /lead() over()：取出前/后n行数据，partition by 可选，order by 必选 //值对比
--按部门分组后按sal排序，取上一条记录的sal。//lag(clo,n,m) over():以当前记录向前取第n条记录的clo值，如果没取到值填m
select empno,sal,lag(sal,1,0) over(partition by deptno order by sal) from scott.emp ;
---------------
12、ratio_to_report() over() ：当前记录值在本组中所占比例 partition by 可选，order by 不可选
--按deptno分组，取每条记录sal在本组总sal中的占比
select deptno,empno,sal,ratio_to_report(sal) over(partition by deptno ) from scott.emp ;
--------------
13、percent_rank() over() ：所在组排名序号-1除以该组所有的行数-1，排名跳跃排序 partition by 可选，order by 必选
--当前行号（rank() over()）减去1后除以所有行数减1
select deptno,empno,sal,percent_rank() over(order by empno) from scott.emp ;
-----------------
14、cume_dist() over() ： 所在组排名序号除以该组所有的行数，注意对于重复行，计算时取重复行中的最后一行的位置，partition by 可选，order by必选
--取本组序号，相同的deptno取其中最大序号除以总行数
select deptno,empno,sal,cume_dist() over(order by deptno) from scott.emp ;
------------------
15、percentile_cont( x ) within group(order by ...) over() ：over()中partition by可选，order by 不可选 //纯摘抄，暂不清楚其应用场景
x为输入的百分比，是0-1之间的一个小数，返回该百分比位置的数据，若没有则返回以下计算值（r）：a=1+( x *(N-1) ) x为输入的百分比，N为分区内的记录的行数b=ceil ( a ) 向上取整c = floor( a ) 向下取整r=a * 百分比位置上一条数据 + b * 百分比位置下一条数据
select deptno,empno,sal,percentile_cont(0.54) within group(order by sal) over(partition by deptno) from scott.emp where empno not in('7782','7934');
-----------------------
16、percentile_disc( x ) within group(order by ...) over() ：over()中partition by可选，order by 不可选
x为输入的百分比，是0-1之间的一个小数，返回百分比位置对应位置上的数据值，若没有对应数据值，就取大于该分布值的下一个值
--按sal排序，取组内cume_dist()值小于等于0.41的那条记录的sal
select deptno,empno,sal,cume_dist() over(partition by deptno order by sal), percentile_disc(0.41) within group(order by sal) over(partition by deptno) from scott.emp
-----------------------
17、variance() over()：计算样本方差，只有一行数据时返回0，partition by 可选，order by 可选
var_samp() over()：计算样本方差，只有一行数据时返回null，partition by 可选，order by 可选
var_pop() over()：计算总体方差，partition by 可选，order by 可选
--按deptno分组求sal样本方差
select deptno,empno,sal,variance(sal) over(partition by deptno) aa from scott.emp ;
--累加sal的样本方差
select deptno,empno,sal,variance(sal) over(order by sal) aa from scott.emp ;
1.count() over() ：统计分区中各组的行数，partition by 可选，order by 可选 //当前记录与本组的记录总量
--按deptno分组统计各组总数
select a.deptno,a.empno,a.ename,count(1) over( partition by a.deptno) from scott.emp a;
--按empno排序递增，包含order by 的count() 展示效果同函数row_number()
select a.deptno,a.empno,a.ename,count(1) over( order by a.empno) from scott.emp a;
-------------
2.sum() over() ：统计分区中记录的总和，partition by 可选，order by 可选 //当前记录与本组的总和
--按deptno分组统计各组sal总和
select a.deptno,a.empno,a.ename,sum(sal) over( partition by a.deptno) from scott.emp a;
--按empno排序累加sal
select a.deptno,a.empno,a.ename,sum(sal) over( order by a.empno) from scott.emp a;
-----------------
3、avg() over() ：统计分区中记录的平均值，partition by 可选，order by 可选 //当前记录与本组的平均值
--按deptno分组统计各组sal平均值
select a.deptno,a.empno,a.ename,avg(sal) over(partition by a.deptno) from scott.emp a;
--按empno排序累加sal求平均值
select a.deptno,a.empno,a.ename,avg(sal) over(order by a.empno) from scott.emp a;
--------------
4、min() over() /max() over()：统计分区中记录的最小/大值，partition by 可选，order by 可选 //当前记录与组的最小/大值
--按deptno分组统计各组最小sal，max同理
select a.deptno,a.empno,a.ename,min(sal) over(partition by a.deptno) from scott.emp a;
--按empno降序统计当前遇到的最小sal（即分析排序后行号小于等于当前行的数据），max同理
select a.deptno,a.empno,a.ename,min(sal) over(order by a.empno desc) from scott.emp a;
-------------------
5、rank() over() ：跳跃排序，排序字段值相同序号相同，partition by 可选，order by 必选 //记录跳跃排序
--按deptno跳跃排序（例：112334序：113446）
select a.deptno,a.empno,a.ename,rank() over(order by a.deptno) from scott.emp a;
--按deptno分组后各组按empno跳跃排序，以下语句empno唯一看不出跳跃效果
select a.deptno,a.empno,a.ename,rank() over(partition by a.deptno order by a.empno) from scott.emp a;
-----------------
6、dense_rank() ：连续排序，排序字段值相同序号相同，partition by 可选，order by 必选 //记录连续排序,相同排序值序号相同
--按deptno连续排序（例：112667序：112334）
select a.deptno,a.empno,a.ename,dense_rank() over(order by a.deptno) from scott.emp a;
--按deptno分组后各组按empno连续排序
select a.deptno,a.empno,a.ename,dense_rank() over(partition by a.deptno order by a.empno) from scott.emp a;
--------------------
7、row_number() over() ：连续排序，不考虑排序字段值，partition by 可选，order by 必选 //记录连续排序，相同排序值序号不同
--按deptno连续排序（例：112667序：123456）
select a.deptno,a.empno,a.ename,dense_rank() over(order by a.deptno) from scott.emp a;
--按deptno分组后各组按empno连续排序
select a.deptno,a.empno,a.ename,dense_rank() over(partition by a.deptno order by a.empno) from scott.emp a;
--------------
8、ntile(n) over() ：将查出的数据分成n份，（例n=3: 12458序：11223）partition by 可选，order by 必选 //用于按记录比例取相关记录
--按deptno排序，将记录分成3份
select a.deptno,a.empno,a.ename,ntile(3) over(order by a.deptno) from scott.emp a;
--按deptno分组后各组按empno排序，将各组数据记录分成三份
select a.deptno,a.empno,a.ename,ntile(3) over(partition by a.deptno order by a.empno) from scott.emp a;
-------------------
9、first_value() over() /last_value() over()：取出分区中第一条/最后一条记录的字段值，partition by 可选，order by 可选 //当前记录与第一条/最后一条的某值
--按empno排序取出第一条记录的sal，last同理
select a.deptno,a.empno,a.ename,first_value(a.sal) over(order by a.empno desc) from scott.emp a;
--按deptno分组，（按查询默认排序）按组取出每组第一条记录的sal，last同理
select a.deptno,a.empno,a.ename,first_value(a.sal) over(partition by a.deptno) from scott.emp a;
------------
10、first /last：从DENSE_RANK返回的集合中取出排在最前/后面的一个值的行 //取各部门工资排名第一/最后的员工
--按deptno分组，各组按sal排序取出第一个max (sal)和最后一个max(sal)
select deptno,max(empno) keep(dense_rank first order by sal ),max(sal) keep(dense_rank last order by sal ) from scott.emp group by deptno;
select deptno,max(sal),min(sal) from scott.emp group by deptno;
有关keep语法：min | max(column1) keep (dense_rank first | last order by column2) over (partion by column3);
最前是聚合函数，可以是min、max、avg、sum等等
column1 要计算的列；
dense_rank first，dense_rank last为keep 函数的保留属性，表示分组、排序结果集中第一个、最后一个；
解释：返回按照column3分组后，按照column2排序的结果集中第一个或最后一个最小值或最大值column1。
---------------
11、lag() over() /lead() over()：取出前/后n行数据，partition by 可选，order by 必选 //值对比
--按部门分组后按sal排序，取上一条记录的sal。//lag(clo,n,m) over():以当前记录向前取第n条记录的clo值，如果没取到值填m
select empno,sal,lag(sal,1,0) over(partition by deptno order by sal) from scott.emp ;
---------------
12、ratio_to_report() over() ：当前记录值在本组中所占比例 partition by 可选，order by 不可选
--按deptno分组，取每条记录sal在本组总sal中的占比
select deptno,empno,sal,ratio_to_report(sal) over(partition by deptno ) from scott.emp ;
--------------
13、percent_rank() over() ：所在组排名序号-1除以该组所有的行数-1，排名跳跃排序 partition by 可选，order by 必选
--当前行号（rank() over()）减去1后除以所有行数减1
select deptno,empno,sal,percent_rank() over(order by empno) from scott.emp ;
-----------------
14、cume_dist() over() ： 所在组排名序号除以该组所有的行数，注意对于重复行，计算时取重复行中的最后一行的位置，partition by 可选，order by必选
--取本组序号，相同的deptno取其中最大序号除以总行数
select deptno,empno,sal,cume_dist() over(order by deptno) from scott.emp ;
------------------
15、percentile_cont( x ) within group(order by ...) over() ：over()中partition by可选，order by 不可选 //纯摘抄，暂不清楚其应用场景
x为输入的百分比，是0-1之间的一个小数，返回该百分比位置的数据，若没有则返回以下计算值（r）：a=1+( x *(N-1) ) x为输入的百分比，N为分区内的记录的行数b=ceil ( a ) 向上取整c = floor( a ) 向下取整r=a * 百分比位置上一条数据 + b * 百分比位置下一条数据
select deptno,empno,sal,percentile_cont(0.54) within group(order by sal) over(partition by deptno) from scott.emp where empno not in('7782','7934');
-----------------------
16、percentile_disc( x ) within group(order by ...) over() ：over()中partition by可选，order by 不可选
x为输入的百分比，是0-1之间的一个小数，返回百分比位置对应位置上的数据值，若没有对应数据值，就取大于该分布值的下一个值
--按sal排序，取组内cume_dist()值小于等于0.41的那条记录的sal
select deptno,empno,sal,cume_dist() over(partition by deptno order by sal), percentile_disc(0.41) within group(order by sal) over(partition by deptno) from scott.emp
-----------------------
17、variance() over()：计算样本方差，只有一行数据时返回0，partition by 可选，order by 可选
var_samp() over()：计算样本方差，只有一行数据时返回null，partition by 可选，order by 可选
var_pop() over()：计算总体方差，partition by 可选，order by 可选
--按deptno分组求sal样本方差
select deptno,empno,sal,variance(sal) over(partition by deptno) aa from scott.emp ;
--累加sal的样本方差
select deptno,empno,sal,variance(sal) over(order by sal) aa from scott.emp ;
样本标准差：（（x1-x）^2+（x2-x）^2+......（xn-x）^2）/（n-1）
总体标准差：（（x1-x）^2+（x2-x）^2+......（xn-x）^2）/n
-----------------
18、stddev() over()：计算样本标准差，只有一行数据时返回0，partition by 可选，order by 可选
stddev_samp() over()：计算样本标准差，只有一行数据时返回null，partition by 可选，order by 可选
stddev_pop() over()：计算总体标准差（方差），partition by 可选，order by 可选
--按deptno分组求sal样本标准差
select deptno,empno,sal,stddev(sal) over(partition by deptno) aa from scott.emp ;
--累加sal的样本标准差
select deptno,empno,sal,stddev(sal) over(order by sal) aa from scott.emp ;
标准差：方差开根
样本标准差：sqrt（（（x1-x）^2+（x2-x）^2+......（xn-x）^2）/（n-1））
总体标准差：sqrt（（（x1-x）^2+（x2-x）^2+......（xn-x）^2）/n）
--------------------
19、covar_samp() over()：返回一对表达式的样本协方差，partition by 可选，order by 可选
covar_pop() over()： 返回一堆表达式的总体协方差，partition by 可选，order by 可选
20、corr() over() ：返回一对表达式的相关系数，partition by 可选，order by 可选
21、REGR_ (Linear Regression) Functions：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用
总体标准差：（（x1-x）^2+（x2-x）^2+......（xn-x）^2）/n
-----------------
18、stddev() over()：计算样本标准差，只有一行数据时返回0，partition by 可选，order by 可选
stddev_samp() over()：计算样本标准差，只有一行数据时返回null，partition by 可选，order by 可选
stddev_pop() over()：计算总体标准差（方差），partition by 可选，order by 可选
--按deptno分组求sal样本标准差
select deptno,empno,sal,stddev(sal) over(partition by deptno) aa from scott.emp ;
--累加sal的样本标准差
select deptno,empno,sal,stddev(sal) over(order by sal) aa from scott.emp ;
标准差：方差开根
样本标准差：sqrt（（（x1-x）^2+（x2-x）^2+......（xn-x）^2）/（n-1））
总体标准差：sqrt（（（x1-x）^2+（x2-x）^2+......（xn-x）^2）/n）
--------------------
19、covar_samp() over()：返回一对表达式的样本协方差，partition by 可选，order by 可选
covar_pop() over()： 返回一堆表达式的总体协方差，partition by 可选，order by 可选
20、corr() over() ：返回一对表达式的相关系数，partition by 可选，order by 可选
21、REGR_ (Linear Regression) Functions：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用
22、ROLLUP：配合group by 使用，group by (A,B) ,分别按a+b，A，非A非B分组
CUBE：配合group by 使用，group by (A,B) ,分别按A,b,A+B,非A非B分组
