oracle 分析函数

2019独角兽企业重金招聘Python工程师标准>>>

1、count(1) over(partition by  列名  order by  列名)  ：统计分区中各组的行数，partition by 可选，order by 可选

2、sum(列名) over(partition by  列名  order by  列名)  ：统计分区中记录的总和，partition by 可选，order by 可选

3、avg(列名) over(partition by  列名  order by  列名)  ：统计分区中记录的平均值，partition by 可选，order by 可选

4、min(列名) over(partition by  列名  order by  列名) ：统计分区中记录的最小值，partition by 可选，order by 可选

5、max(列名) over(partition by  列名  order by  列名) ：统计分区中记录的最大值，partition by 可选，order by 可选

6、rank() over(partition by  列名  order by  列名)  ：对分区中记录排名，如遇到排名相同则下个会跳跃（例：1,2,2,4,5），partition by 可选，order by 必选

7、dense_rank(partition by  列名  order by  列名) ：对分区中记录排名,如遇到排名相同则连续排序（例：1,2,2,3,4），partition by 可选，order by 必选

8、row_number() over(partition by  列名  order by  列名) ：排序，无重复值，partition by 可选，order by 必选

9、ntile(平均分成分数) over(partition by  列名  order by  列名) ：partition by 可选，order by 必选 ，n表示将分区内记录平均分成n份，多出的按照顺序依次分给前面的组

10、first_value(列名) over(partition by  列名  order by  列名) ：取出分区中第一条记录的字段值，partition by 可选，order by 可选

       last_value(列名) over(partition by  列名  order by  列名) ：取出分区中最后一条记录的字段值，partition by 可选，order by 可选

11、first ：从DENSE_RANK返回的集合中取出排在最前面的一个值的行

       last ：从DENSE_RANK返回的集合中取出排在最后面的一个值的行

select job,max(salary) keep(dense_rank first order by salary desc),
max(salary) keep(dense_rank last order by salary desc) from emp
group by job;

keep前面可以是：min,max,sum,avg,count,variance,stddev

12、lag(列名，取当前上n行数据，当前数据没有上n行数据则返回的默认值) over(partition by  列名  order by  列名) ：取出前n行数据，partition by 可选，order by 必选

      lead(列名，取当前下n行数据，当前数据没有下n行数据则返回的默认值) over(partition by  列名  order by  列名) ：取出后n行数据，partition by 可选，order by 必选

13、ratio_to_report(a) over(partition by b) ：求按照b分组后a的值在所属分组中总值的占比,a的值必须为数值或数值型字段  partition by 可选，order by 不可选

14、percent_rank() over(partition by  列名  order by  列名)  ：partition by 可选，order by 必选 所在组排名序号-1除以该组所有的行数-1，排名跳跃排序

15、cume_dist() over(partition by  列名  order by  列名) ：partition by 可选，order by必选 所在组排名序号除以该组所有的行数，注意对于重复行，计算时取重复行中的最后一行的位置

16、precentile_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 ename,job,salary,percentile_cont(0.5) within group(order by salary) over() from emp;
select ename,job,salary,percentile_cont(0.5) within group(order by salary) over(partition by job) from emp;

17、stddev() over()：计算样本标准差，只有一行数据时返回0，partition by 可选，order by 可选

       stddev_samp() over()：计算样本标准差，只有一行数据时返回null，partition by 可选，order by 可选

       stddev_pop() over()：计算总体标准差，partition by 可选，order by 可选

select stddev(stu_age) over() from student; --计算所有记录的样本标准差
select stddev(stu_age) over(order by stu_age) from student; --计算递加的样本标准差
select stddev(stu_age) over(partition by stu_major) from student; --计算分组的样本标准差
select stddev(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本标准差
 
 
select stddev_samp(stu_age) over() from student; --计算所有记录的样本标准差
select stddev_samp(stu_age) over(order by stu_age) from student; --计算递加的样本标准差
select stddev_samp(stu_age) over(partition by stu_major) from student; --计算分组的样本标准差
select stddev_samp(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本标准差
 
 
select stddev_pop(stu_age) over() from student; --计算所有记录的总体标准差
select stddev_pop(stu_age) over(order by stu_age) from student; --计算递加的总体标准差
select stddev_pop(stu_age) over(partition by stu_major) from student; --计算分组的总体标准差
select stddev_pop(stu_age) over(partition by stu_major order by stu_age) from student;--计算分组递加的总体标准差

18、variance() over()：计算样本方差，只有一行数据时返回0，partition by 可选，order by 可选

       var_samp() over()：计算样本方差，只有一行数据时返回null，partition by 可选，order by 可选

       var_pop() over()：计算总体方差，partition by 可选，order by 可选

select variance(stu_age) over() from student; --计算所有记录的样本方差
select variance(stu_age) over(order by stu_age) from student; --计算递加的样本方差
select variance(stu_age) over(partition by stu_major) from student; --计算分组的样本方差
select variance(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本方差
 
 
select var_samp(stu_age) over() from student; --计算所有记录的样本方差
select var_samp(stu_age) over(order by stu_age) from student; --计算递加的样本方差
select var_samp(stu_age) over(partition by stu_major) from student; --计算分组的样本方差
select var_samp(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本方差
 
 
select var_pop(stu_age) over() from student; --记录所有就的总体方差
select var_pop(stu_age) over(order by stu_age) from student; --计算递加的总体方差
select var_pop(stu_age) over(partition by stu_major) from student; --计算分组的总体方差
select var_pop(stu_age) over(partition by stu_major order by stu_age) from student;--计算分组递加的样本方差

       stddev()=sqrt( variance() )     sqrt()--求开方

       stddev_samp()=sqrt( var_samp() )

       stddec_pop=sqrt( var_pop() )

19、covar_samp over()：返回一对表达式的样本协方差，partition by 可选，order by 可选

       covar_pop over()： 返回一堆表达式的总体协方差，partition by 可选，order by 可选

select covar_samp(stu_age,line) over() from student; --计算所有记录的样本协方差
select covar_samp(stu_age,line) over(order by stu_age) from student; --计算递加的样本协方差
select covar_samp(stu_age,line) over(partition by stu_major) from student; --计算分组的样本协方差
select covar_samp(stu_age,line) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本协方差
 
 
select covar_pop(stu_age,line) over() from student; --计算所有记录的总体协方差
select covar_pop(stu_age,line) over(order by stu_age) from student; --计算递加的总体协方差
select covar_pop(stu_age,line) over(partition by stu_major) from student; --计算分组的总体协方差
select covar_pop(stu_age,line) over(partition by stu_major order by stu_age) from student; --计算分组递加的总体协方差

20、corr() over() ：返回一对表达式的相关系数，partition by 可选，order by 可选

select corr(stu_age,line) over() from student; --计算所有记录的相关系数
select corr(stu_age,line) over(order by stu_age) from student; --计算递加的相关系数
select corr(stu_age,line) over(partition by stu_major) from student; --计算分组的相关系数
select corr(stu_age,line) over(partition by stu_major order by stu_age) from student; --计算分组递加的相关系数

21、REGR_ (Linear Regression) Functions：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用

转载于:https://my.oschina.net/u/3445245/blog/3015379