oracle计算最大与最小之间数,oracle 分析函数

oracle分析函数，主要五大类

一等级函数:row_number();DENSE_RANK();Rank()

主要就是遇到相同排名时的区别，

查询各部门职位级别最高的2个人

select * from (select rybm,rymc,zzjgid,zwdj,row_number() over(partition by zzjgid order by zwjb desc ) rw  from t_rlzy_ry) where rw<2

二开窗(windowing)函数:用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上

1. order by 加则是当前分区的第一行到当前行的和，不加order by 则是当前分区的所有行的和

select zzjgid,rymc,df,sum(df) over(partition by zzjgid order by df) from t_rlzy_ry where df>0

2.rows窗口: "rows 5 preceding" 适用于任何类型而且可以order by多列。当前行之前第五行开始到当前行，一共6行

3.range窗口: "range 5 preceding" 适用于number和date类型，order by一列。当前行之前所有行的值大于当前行的值减去5的行

4.以下三条语句等效

over (order by salary rows between unbounded preceding and unbounded following)

over (order by salary range between unbounded preceding and unbounded following)

over (partition by null)

5.over (order by salary range between unbounded preceding and current row) 第一行到当前行

6.NULLS LAST/FIRST  控制排列顺序，最后或者最前

7.keep()取一个最大和最小值

select zzjgid,min(df) keep(dense_rank first order by df ) first,max(df) keep(dense_rank last order by df ) last from t_rlzy_ry group by zzjgid

8.ntile(N)将结果平均分成N等分

select zzjgid,rymc,df,ntile(5) over( order by df )  from t_rlzy_ry

9.first_value/last_value

SELECT ZZJGID,FIRST_value(sum(df) ) over (order by zzjgid rows between 1 preceding and 1 following ) pre,

sum(df) cur,

last_value(sum(df) ) over (order by zzjgid rows between 1 preceding and 1 following ) next

from t_rlzy_ry  group by zzjgid

10.CUME_DIST() OVER([partition_clause] order_by_clause) 返回该行在分组序列中的相对位置，返回值介于0到1之间

11.PERCENT_RANK() OVER([partition_clause] order_by_clause) 与CUME_DIST类似，本函数返回分组序列中各行在分组序列的相对位置。其返回值也是介于0到1之间，不过其起始值始终为0而终结值始终为1

三制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列

计算每条记录在其对应记录集或其子集中所占的比例。

select zzjgid,rymc, ratio_to_report(df) over (partition by zzjgid) from  t_rlzy_ry

四LAG,LEAD函数:这类函数允许在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常用用的.

比较相邻记录

SELECT ZZJGID,lag(sum(df) ,2) over (order by zzjgid  ) pre,sum(df) cur  from t_rlzy_ry  group by zzjgid --当前记录的前第二条

SELECT ZZJGID,LEAD(sum(df) ,2) over (order by zzjgid  ) pre,sum(df) cur  from t_rlzy_ry  group by zzjgid--当前记录的后第二条

五VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数:计算任何未排序分区的统计值

1.STDDEV ([distinct|all] col) OVER (analytic_clause)返回列的标准偏差。

2.STDDEV_POP(col) OVER (analytic_clause)返回该分组序列总体标准偏差

3.VAR_POP(col) OVER (analytic_clause)返回分组序列的总体方差，VAR_POP进行如下计算：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)

4.VAR_SAMP(col) OVER (analytic_clause)与上类似，该函数返回分组序列的样本方差，，其计算公式为：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / (COUNT(expr) - 1)

5.VARIANCE(col) OVER (analytic_clause)该函数返回分组序列方差，Oracle计算该变量如下：

如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMP