Oracle分析函数

1、greatest(A,B,C)

     取A、B、C3列中最大的值，如：A列值为：1，B列值为2，C列值为3，则该函数返回3。

     least的意思和greatest相反。

2、row_number() over (partition by t.name order by t.age asc)

     按name列分类，同时按age排序，利用上面规则取得排序（此处取得的排序是不间断的即1、2、3·····）

     rank () over (partition by t.name order by t.age asc)

     与row_number()意思大致相同，唯一不同的是当age有相同的时候，序号会（1、2、2、4、5······）

     dense_rank () over (partition by t.name order by t.age asc)

     与row_number()意思大致相同，唯一不同的是当age有相同的时候，序号会（1、2、2、3、4······）

3、lag(salary,N) over (order by year)

     按year排序，取前面第 step 条记录的salary值(即取前N行的数据)

     数据表如下

     salary year

     2000 2013

     2001 2012

     2002 2011

     2003 2014

     2004 2015

     2005 2016

     2006 2017

    select salary as 本年薪水,lag(salary,1) over (order by year) as 去年薪水,year 本期,lag(year,1) over (order by year) as 同期 from baseinfo t ;

    执行上面查询sql后结果为：

    本年薪水 去年薪水   本期            同期 

    2002         2011             -->此处因是第一条数据因此为空

    2001 2002 2012 2011    -->此处去年薪水2002即为上一条数据的本年薪水，同期2011数据即为上一条数据本期的2011

    2000 2001 2013 2012

    2003 2000 2014 2013

    2004 2003 2015 2014

    2005 2004 2016 2015

    2006 2005 2017 2016

 

    lag(salary,N) over (partition by name order by year)

    先按name分组，然后再去name同组(即name相同)的前N行的数据。

4、lead(salary,N) over (order by year)

     与lag的意思相反，取后N行的数据。

 

     lead(salary,N) over (partition by name order by year)

     与lag的意思相反，先按name分组，然后再去name同组(即name相同)的前N行的数据。

5、rollup、cube、grouping sets函数

     数据如下：

     deptid   name     salary

        1  yao         2000

1  yao1 2001

2  yao2 2002

2  yao3 2003

1  yao4 2004

3  yao5 2005

3  yao         2006

     select deptid,name,sum(salary) from  baseinfo t group by rollup(deptid,name) order by deptid,name desc;

     得到结果如下：

     deptid  name      salary

        1 6005    -->此处我们是否就是deptid=1的salary汇总

1 yao4 2004    -->此后3条数据就是deptid=1的所有数据

1 yao1 2001

1 yao 2000

2 4005

2 yao3 2003

2 yao2 2002

3 4011

3 yao5 2005

3 yao 2006

14021  -->此数据就是所有部门的salary汇总

       从上面一个例子我们是否能发现当我们需要求一些先是一条汇总数据然后再是详细数据时就可以用rollup函数。

       下面是rollup函数的详解。

       其实rollup我们可以把它看成N+1个group by组成。（其中N是参数的个数）

       如：select A列,B列,C列,sum(salary) from table group by rollup(A列,B列,C列)，我们就可以看成是：

             select A列,B列,C列,sum(salary) from table group by A列,B列,C列

             union all

             select A列,B列,null,sum(salary) from table group by A列,B列

             union all

             select A列,null,null,sum(salary) from table group by A列

             union all

             select null,null,null,sum(salary) from table 

       group by rollup就是对选择的列从右到左以一次少一列的方式进行grouping直到所有列都去掉后的全表grouping

 

       而group by cube则是取值为参与grouping和不参与grouping两个值的一个维度，然后所有维度取值组合的集合就是grouping的集合，

       对于n个参数的cube，有2^n次的grouping。如果使用group by cube(A,B,C),，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，

       最后对全表进行GROUP BY操作，一共是2^3=8次grouping。

       如：select A列,B列,C列,sum(salary) from table group by cube(A列,B列,C列)，我们就可以看成是：

             select A列,B列,C列,sum(salary) from table group by A列,B列,C列

             union all

             select A列,B列,null,sum(salary) from table group by A列,B列

             union all

     select A列,null,C列,sum(salary) from table group by A列,C列

     union all

             select A列,null,null,sum(salary) from table group by A列

             union all

             select null,B列,C列,sum(salary) from table group by B列,C列

 

             union all

             select null,B列,null,sum(salary) from table group by B列

 

             union all

             select null,null,C列,sum(salary) from table group by C列

 

             union all

             select  null,null,null,sum(salary) from table

 

 

 

grouping sets就是对参数中的每个参数做grouping，也就是有几个参数做几次grouping,例如使用group by grouping sets(A,B,C)，则对(A),(B),(C)进行group by，

如果使用group by grouping sets((A,B),C),则对(A,B),(C)进行group by。甚至grouping by grouping set(A,A)都是语法允许的，也就是对(A)进行2次group by,grouping sets的参数允许重复

 

针对以上3个函数总结

        rollup         (N+1个分组)

cube         (2^N个分组)

grouping sets (自定义罗列出分组)

        其中rollup效率比cube效率高，rollup不需要多次扫描表，而cube需要多次扫描表