Oracle分析函数学习总结(完成版)

1.rollup函数和Cube函数

1.1.rollup函数事例1

1.1.1.事例功能说明

功能：完成XX市公司各县合同种植面积、合同量统计功能。

优点：SQL简单，减少合计列的平凑工作

1.1.2.SQL语句

select nvl(substr(t.dept_code,1,6), 'Total') Dept_Code,

       sum(t.zzmj) ,

       sum(t.amount1) ,

       sum(t.amount2) ,

       sum(t.amount_sum) 

  from tb_yy_contract t

 where t.dept_code like '5304%'

 group by rollup(nvl(substr(t.dept_code,1,6), 'Total'))

1.1.3.输出结果

1.1.4.使用说明

1、在使用过程中先通过部门编码查出分析数据然后在和部门表关联获得部门名称完成该功能；

2、部门和区域分别写SQL语句；

1.2.rollup函数和cube函数的另外用法

1.2.1.事例功能说明

功能：完成XX市公司各县烤烟小等级收购情况

优点：SQL简单，查询效率高，并能减少代码工作量，易维护。

1.2.2.rollup函数实现SQL语句

select t.ddj_code,

       t.DJ_CODE,

       sum(t.amount),

       sum(t.money),

       round(case sum(t.amount)

               when 0 then

                0

               else

                sum(t.money) / sum(t.amount)

             end,

             2) price

  from TB_YY_PLANTER_SALE_SUM_D t

 where t.nd = 2008

   and t.dept_code like '5304%'

 group by rollup(t.ddj_code, t.DJ_CODE)

1.2.3.rollUp函数输出结果

耗时22.985秒

说明：rollup函数只对DDJ_CODE做汇总而我们下面介绍cube函数则是分别对DDJ_CODE和DJ_CODE都进行汇总

1.2.4.cube函数实现SQL语句

select t.ddj_code,

       t.DJ_CODE,

       sum(t.amount),

       sum(t.money),

       round(case sum(t.amount)

               when 0 then

                0

               else

                sum(t.money) / sum(t.amount)

             end,

             2) price

  from TB_YY_PLANTER_SALE_SUM_D t

 where t.nd = 2008

   and t.dept_code like '5304%'

 group by cube(t.ddj_code, t.DJ_CODE)

 order by t.ddj_code,t.dj_code nulls last

1.2.5.cube函数输出结果

耗时：23.561秒

说明：用Cube函数慢rollup函数慢1.4秒左右，其主要原因是Cube函数查询的数据行数比Rollup函数多了DJ_CODE汇总行，RollUp函数只针对DDJ_CODE汇总，而Cube函数则分别对DDJ_CODE和DJ_CODE汇总。

1.3.RollUp函数和Cube函数的深入

从上节小等级收购情况输出结果容易发现，每个汇总数据都会出现Null的情况，如果要区分哪个字段做的汇总，这时候,oracle的grouping函数就粉墨登场了。如果当前的汇总记录使用字段得出的，grouping函数就会返回1，否则返回0。

1.3.1.事例功能说明

与1.2一样

1.3.2.实现SQL语句

select 

decode(grouping(t.ddj_code),1,'所有大等级',to_char(ddj_code)) ddj_code,

decode(grouping(t.DJ_CODE),1,'所有小等级',to_char(DJ_CODE)) DJ_CODE,

       sum(t.amount),

       sum(t.money),

       round(case sum(t.amount)

               when 0 then

                0

               else

                sum(t.money) / sum(t.amount)

             end,

             2) price

  from TB_YY_PLANTER_SALE_SUM_D t

 where t.nd = 2008

   and t.dept_code like '5304%'

 group by cube(t.ddj_code, t.DJ_CODE)

 order by t.ddj_code,t.dj_code nulls last

1.3.3.输出结果

耗时24.672秒

说明：可以看到,所有的空值现在都根据grouping函数做出了很好的区分,这样利用rollup,cube和grouping函数,我们做数据统计的时候就可以轻松很多了.

2.rank函数

2.1.rank函数使用

2.1.1.功能说明

实现XX市公司2009年个县合同面积排名，排名要跳位（如：两个第2名，则没有第3名）

2.1.2.SQL语句

select t.dept_code,

       sum(t.zzmj) zzmj,

       rank() over(order by sum(t.zzmj) ASC) PaiMing

  from tb_yy_contract t

 where t.dept_code like '530425%'

   and t.nd = 2009

 group by t.dept_code

2.1.3.输出结果

说明：使用Rank函数排名，名次跳位。

2.2.Dense_Rank函数使用

2.2.1.功能说明

实现XX市公司2009年个县合同面积排名，排名不跳位（如：两个第2名，一个第3名的情况）

2.2.2.SQL语句

select t.dept_code,

       sum(t.zzmj) zzmj,

       dense_rank() over(order by sum(t.zzmj) ASC) PaiMing

  from tb_yy_contract t

 where t.dept_code like '530425%'

   and t.nd = 2009

 group by t.dept_code

2.2.3.输出结果

说明：使用Rank函数排名，名次不跳位。

2.3.Row_Number函数

2.3.1.动能说明

实现XX市公司2009年个县合同面积排名，排名不允许相同。

2.3.2.SQL语句

select t.dept_code,

       sum(t.zzmj) zzmj,

       row_number()  over(order by sum(t.zzmj) ASC) PaiMing

  from tb_yy_contract t

 where t.dept_code like '530425%'

   and t.nd = 2009

 group by t.dept_code

2.3.3.输出结果

说明：使用Rank函数排名，名次不允许相同。

2.3.4.总结

在这个例子中,出现了一个第三名,这就是rank和dense_rank的差别,

rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,而dense_rank则不会,

差别更大的是,row_number哪怕是两个数据完全相同,排名也会不一样,这个特性在我们想找出对应没个条件的唯一记录的时候又很大用处

2.4.Rank函数其他用法

2.4.1.取出某项排名前n的数据

2.4.1.1.功能说明

取出XX市公司2009年合同种植面积最大10个用户

2.4.1.2.SQL语句

select *

  from (select t.planter_name,

               t.zzmj,

               rank() over(order by t.zzmj desc) ZZMJ_PAIMING

          from TB_YY_CONTRACT t where t.nd='2009')

 where ZZMJ_PAIMING <= 10 

2.4.1.3.输出结果

2.5.partition函数用法

2.5.1.功能说明

取出XX市公司各县公司2008年合同种植面积前2的合同。

2.5.2.SQL语句

select * From

(select t.planter_name,t.zzmj,t.dept_code,rank()over(partition by substr(t.dept_code,1,6) order by t.zzmj desc) mm

from tb_yy_contract2 t where t.nd=2008 ) t

where t.mm<=2

2.5.3.输出结果

3.Lag和Lead函数介绍

3.1.应用说明

查询出XX市2009年某月份各县卷烟销售情况，要求查询数据项本期、上期、下期。

3.2.SQL语句

select *

  from (select a.dept_code,

               a.month,

               a.Amount 本月,

               lag(a.amount, 1, 0) over(partition by a.dept_code order by a.month)上月,

               lead(a.amount, 1, 0) over(partition by a.dept_code order by a.month) 下月

          from (select t.dept_code,

                       t.indent_month Month,

                       sum(t.amount) Amount

                  from mv_jy_dept_sale_cig_month t

                 where t.nd = 2009

                 group by t.indent_month, t.dept_code) a) b

 where b.month = 08

3.3.执行结果

3.4.总结

利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据.

4.Sum、Avg、Max、Min移动计算数据

4.1.应用说明

可以查出连续几个周期对应指标的最大值、最小值、平均值、和，如：查询出XX市2009年10月各县销量及该县最近三月各月销量的最大值、最小值、平均值、和。

4.2.SQL语句

select *

  from (select a.dept_code,

               a.dept_name,

               a.indent_month,

               Amount,

               max(Amount) over(partition by dept_code order by to_number(indent_month) range between 2 preceding and 0 following) "最近三月最大销量

               min(Amount) over(partition by dept_code order by to_number(indent_month) range between 2 preceding and 0 following) "最近三月最小销量

               sum(Amount) over(partition by dept_code order by to_number(indent_month) range between 2 preceding and 0 following) "最近三月平均销量

               avg(Amount) over(partition by dept_code order by to_number(indent_month) range between 2 preceding and 0 following) "最近三月销量和"

          from (select t.dept_code,

                       t.dept_name,

                       t.indent_month,

                       sum(t.amount) Amount

                  from mv_jy_dept_sale_cig_month t

                 where t.nd = 2009

                 group by t.dept_code, t.dept_name, t.indent_month) a) b

 where b.indent_month = 10

4.3.输出结果

5.ratio_to_report函数的介绍

5.1.应用说明

可以快速查询出指标分布及其占比情况。如查询XX市2009年各县销量及其占比情况。

5.2.SQL

--ÓñϪÊÐ2009Äê¸÷ÏØÏúÁ¿¼°ÆäÕ¼±È

select t.dept_code,

                       t.dept_name,

                       sum(t.amount) Amount,

                       ratio_to_report(sum(amount)) over

                       () area_pct

                  from mv_jy_dept_sale_cig_month t

                 where t.nd = 2009 

                 group by t.dept_code, t.dept_name

5.3.输出结果

6.First和last函数使用介绍

6.1.应用说明

可以快速查询出指标分布及最大最小指标情况。如：查询XX市2009年12月份各县销量及销量最大最小的县和值。

6.2.SQL

 select t.indent_month,

       t.dept_code,

       t.dept_name,

       sum(t.amount) Amount_SUM,

       first_value(dept_name) over(order by sum(amount) desc rows unbounded preceding) Max_Dept,

       first_value(sum(amount)) over(order by sum(amount) desc rows unbounded preceding) Max_Value,

       first_value(dept_name) over(order by sum(amount) asc rows unbounded preceding) Min_DEPT,

       first_value(sum(amount)) over(order by sum(amount) asc rows unbounded preceding) Min_Value

  from mv_jy_dept_sale_cig_month t

 where t.nd = 2009

   and t.indent_month = 12

 group by t.indent_month, t.dept_code, t.dept_name

6.3.输出结果

6.4.深入

6.3的输出结果可能大家觉得意义不太大，但是进一步深入，如果要查询出2009年全年各月销量最大和最小的县公司及最大最小值。

SQL: 

select a.indent_month,a.Max_Dept,a.Max_Value,a.Min_DEPT,a.Min_Value from(

select t.indent_month,

       t.dept_code,

       t.dept_name,

       sum(t.amount) Amount_SUM,

       first_value(dept_name) over(partition by indent_month  order by sum(amount) desc rows unbounded preceding) Max_Dept,

       first_value(sum(amount)) over(partition by indent_month order by sum(amount) desc rows unbounded preceding) Max_Value,

       first_value(dept_name) over(partition by indent_month  order by sum(amount) asc rows unbounded preceding) Min_DEPT,

       first_value(sum(amount)) over(partition by indent_month  order by sum(amount) asc rows unbounded preceding) Min_Value

  from mv_jy_dept_sale_cig_month t

 where t.nd = 2009

 group by t.indent_month, t.dept_code, t.dept_name)a

 group by a.indent_month,a.Max_Dept,a.Max_Value,a.Min_DEPT,a.Min_Value

 order by a.indent_month

输出结果：