author:skate
time:2010-12-08
分析函数，最早是从ORACLE8.1.6开始出现的，它的设计目的是为了解决诸如“累计计算”，“找出分组内百分比”，“前-N条查询”，
“移动平均数计算”"等问题。其实大部分的问题都可以用PL/SQL解决，但是它的性能并不能达到你所期望的效果。分析函数是SQL
语言的一种扩充，它并不是仅仅试代码变得更简单而已，它的速度比纯粹的SQL或者PL/SQL更快
分析函数原理：
分析函数是在一个记录行分组的基础上计算它们的总值，与普通集合函数不同的是，他们在各组中返回多行，行的分组被称为“窗口”
窗口大小表示用于统计计算的行数，窗口的大小是基于物理行数或逻辑间隔（例如时间）。
除了ORDER BY（按…排序）语句外，分析函数是一条查询被执行的操作。所有合并、WHERE、GROUP BY、HAVING语句都是分析函数处理之前完成的。因此，分析函数只出现在select 列表或ORDER BY（按…排序）语句中。
一. 分组汇总函数rollup,cube
常规汇总方法
SQL> set timing on;
SQL>
SQL> select to_char(o.lottery_id),sum(o.money)
2 from tb_order o
3 where o.state=4
4 and o.buy_time>sysdate-30
5 group by o.lottery_id
6 union all
7 select '合计',sum(o.money)
8 from tb_order o
9 where o.state=4
10 and o.buy_time>sysdate-30
11 ;
TO_CHAR(O.LOTTERY_ID) SUM(O.MONEY)
---------------------------------------- ------------
14 8810
22 278
58 78
合计 9166
Executed in 0.032 seconds
总计信息：consistent gets：412
使用rollup进行汇总
SQL>
SQL> select decode(o.lottery_id,null,'所有彩种统计',o.lottery_id),sum(o.money)
2 from tb_order o
3 where o.state=4
4 and o.buy_time>sysdate-30
5 group by rollup(o.lottery_id)
6 ;
DECODE(O.LOTTERY_ID,NULL,'所有 SUM(O.MONEY)
---------------------------------------- ------------
14 8810
22 278
58 78
所有彩种统计 9166
Executed in 0.047 seconds
SQL>
总计信息：consistent gets：206
对比
传统的一致读：consistent gets：412
使用分析函数的一致读：consistent gets：206
从对比信息可以看到，如果数据量很大的话，对比效果就更明显
再深入的一点看看多个字段分组统计
SQL> select decode(grouping(o.lottery_id),1,'所有彩种',o.lottery_id),
2 decode(grouping(trunc(o.buy_time,'dd')),1,'所有天数',trunc(o.buy_time,'dd')),
3 sum(o.money)
4 from tb_order o
5 where o.state=4
6 and o.buy_time>sysdate-30
7 group by rollup(o.lottery_id,trunc(o.buy_time,'dd'))
8
9 order by o.lottery_id
10 ;
DECODE(GROUPING(O.LOTTERY_ID), DECODE(GROUPING(TRUNC(O.BUY_TI SUM(O.MONEY)
---------------------------------------- ------------------------------ ------------
14 08-11月-10 96
14 09-11月-10 156
14 10-11月-10 8544
14 11-11月-10 10
14 15-11月-10 4
14 所有天数 8810
22 09-11月-10 278
22 所有天数 278
58 08-11月-10 78
58 所有天数 78
所有彩种 所有天数 9166
11 rows selected
Executed in 0.109 seconds
SQL>
这里的rollup(o.lottery_id,trunc(o.buy_time,'dd'))是先对o.lottery_id 进行group by ,然后再对(o.lottery_id,trunc(o.buy_time,'dd')) 进行group by，两个字段比较好理解，那三个字段呢？
SQL> select decode(grouping(o.lottery_id),1,'所有彩种',o.lottery_id) lottery_id,
2 decode(grouping(trunc(o.buy_time,'dd')),1,'所有天数',trunc(o.buy_time,'dd')) buy_time,
3 sum(o.money) ,decode(grouping(o.ware_id),1,'所有wareid',o.ware_id) ware_id
4 from tb_order o
5 where o.state=4
6 and o.buy_time>sysdate-30
7 group by rollup(o.lottery_id,trunc(o.buy_time,'dd'),o.ware_id)
8
9 order by o.lottery_id,trunc(o.buy_time,'dd')
10 ;
LOTTERY_ID BUY_TIME SUM(O.MONEY) WARE_ID
---------------------------------------- -------------- ------------ ----------------------------------------
14 08-11月-10 96 47055
14 08-11月-10 96 所有wareid
14 09-11月-10 156 47055
14 09-11月-10 156 所有wareid
14 10-11月-10 8544 47059
14 10-11月-10 8544 所有wareid
14 11-11月-10 10 47059
14 11-11月-10 10 所有wareid
14 15-11月-10 4 47066
14 15-11月-10 4 所有wareid
14 所有天数 8810 所有wareid
22 09-11月-10 278 47057
22 09-11月-10 278 所有wareid
22 所有天数 278 所有wareid
58 08-11月-10 78 47043
58 08-11月-10 78 所有wareid
58 所有天数 78 所有wareid
所有彩种 所有天数 9166 所有wareid
18 rows selected
Executed in 0.187 seconds
说明：
从上面的结果中我们很容易发现,如果不用decode函数，每个统计数据所对应的行都会出现null,
并且我们如何来区分到底是根据那个字段做的汇总呢？这时候,oracle的grouping函数就派上用场了.
如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0
这里的rollup(o.lottery_id,trunc(o.buy_time,'dd'),o.ware_id)是先对o.lottery_id进行group by ，然后在对(o.lottery_id,trunc(o.buy_time,'dd'))进行group by,最后在对(o.lottery_id,trunc(o.buy_time,'dd'),o.ware_id)进行group by；每次的group by 都会进行数据汇总
从上面的结果我们可以看到，共有如下几项统计汇总
1. 按（LOTTERY_ID，BUY_TIME）进行的统计汇总，即统计每个彩种每天所有的商品（wareid）销售总额
2. 按 （LOTTERY_ID）进行汇总，即统计每个彩种在所有天里，所有wareid的销售总额
3. 所有彩种，在所有天里，销售所有的商品（wareid）的总销售额
那我们要如何统计如下的汇总呢？，难道还要在sql（估计很复杂）或pl？sql
所有彩种，每天，每个商品（wareid）的销售总额；
所有彩种，每天，所有商品（wareid）的销售总额；
所有彩种，所有天里，每个商品（wareid）的销售总额；
其实我们可以用cube来实现，如下所示
SQL>
SQL> select decode(grouping(o.lottery_id),1,'所有彩种',o.lottery_id) lottery_id,
2 decode(grouping(trunc(o.buy_time,'dd')),1,'所有天数',trunc(o.buy_time,'dd')) buy_time,
3 sum(o.money) ,decode(grouping(o.ware_id),1,'所有wareid',o.ware_id) ware_id
4 from tb_order o
5 where o.state=4
6 and o.buy_time>sysdate-30
7
8 group by cube(o.lottery_id,trunc(o.buy_time,'dd'),o.ware_id)
9 order by o.lottery_id,trunc(o.buy_time,'dd')
10 ;
LOTTERY_ID BUY_TIME SUM(O.MONEY) WARE_ID
---------------------------------------- -------------- ------------ ----------------------------------------
14 08-11月-10 96 所有wareid
14 08-11月-10 96 47055
14 09-11月-10 156 所有wareid
14 09-11月-10 156 47055
14 10-11月-10 8544 所有wareid
14 10-11月-10 8544 47059
14 11-11月-10 10 所有wareid
14 11-11月-10 10 47059
14 15-11月-10 4 所有wareid
14 15-11月-10 4 47066
14 所有天数 8810 所有wareid
14 所有天数 252 47055
14 所有天数 8554 47059
14 所有天数 4 47066
22 09-11月-10 278 所有wareid
22 09-11月-10 278 47057
22 所有天数 278 所有wareid
22 所有天数 278 47057
58 08-11月-10 78 所有wareid
58 08-11月-10 78 47043
LOTTERY_ID BUY_TIME SUM(O.MONEY) WARE_ID
---------------------------------------- -------------- ------------ ----------------------------------------
58 所有天数 78 所有wareid
58 所有天数 78 47043
所有彩种 08-11月-10 174 所有wareid
所有彩种 08-11月-10 78 47043
所有彩种 08-11月-10 96 47055
所有彩种 09-11月-10 434 所有wareid
所有彩种 09-11月-10 156 47055
所有彩种 09-11月-10 278 47057
所有彩种 10-11月-10 8544 所有wareid
所有彩种 10-11月-10 8544 47059
所有彩种 11-11月-10 10 所有wareid
所有彩种 11-11月-10 10 47059
所有彩种 15-11月-10 4 所有wareid
所有彩种 15-11月-10 4 47066
所有彩种 所有天数 9166 所有wareid
所有彩种 所有天数 78 47043
所有彩种 所有天数 252 47055
所有彩种 所有天数 278 47057
所有彩种 所有天数 8554 47059
所有彩种 所有天数 4 47066
40 rows selected
Executed in 0.234 seconds
SQL>
总结：通过以上对grouping，cube，rollup的例子介绍，我们就可以很好的做数据统计，最重要的是，语法简单效率极高
rollup（a,b,c）
汇总方式是按如下规律的
1.a （可以简单理解为，每个a总计情况）
2.a,b （可以简单理解为，每个（a,b）总计情况）
3.a,b,c （可以简单理解为，每个（a,b,c）总计情况）
如果是rollup（a,b,c,d）会是什么样呢？
cube(a,b,c)
1. a
2. a,b
3. a,b,c
4. b
5. b,c
6. c
7. a,c
cube和rollup不同的是，cube(a,b,c...)是汇总统计包含第一个元素a的所有子集，而rollup(a,b,c,...)是汇总统计所有子集的
使用环境：适用于分组统计，可以按着我们自己的要求进行各种复杂的统计
二.rank函数的介绍
rank()样例：
SQL> select o.lottery_id,trunc(o.buy_time,'dd'),sum(o.money)
2 ,rank() over( order by sum(o.money) desc) rank
3 from tb_order o
4 where o.state=4
5 and o.buy_time> sysdate -120
6
7 group by o.lottery_id,trunc(o.buy_time,'dd')
8 ;
LOTTERY_ID TRUNC(O.BUY_TIME,'DD') SUM(O.MONEY) RANK
--------------------- ---------------------- ------------ ----------
10 2010-10-13 19369444 1
49 2010-10-13 2089524 2
10 2010-10-22 750410 3
10 2010-10-21 370164 4
53 2010-10-13 131072 5
10 2010-10-20 73730 6
51 2010-10-13 62208 7
14 2010-11-10 8544 8
58 2010-8-23 6084 9
74 2010-9-16 3238 10
58 2010-8-24 3201 11
67 2010-8-11 2252 12
44 2010-8-25 1458 13
58 2010-11-4 1384 14
44 2010-8-19 736 15
22 2010-11-9 278 16
14 2010-11-9 156 17
58 2010-8-26 132 18
74 2010-9-17 130 19
44 2010-8-16 112 20
LOTTERY_ID TRUNC(O.BUY_TIME,'DD') SUM(O.MONEY) RANK
--------------------- ---------------------- ------------ ----------
47 2010-8-24 106 21
58 2010-8-20 102 22
67 2010-8-25 102 22
14 2010-11-8 96 24
58 2010-11-8 78 25
58 2010-8-17 40 26
74 2010-9-15 32 27
44 2010-8-24 30 28
67 2010-9-25 26 29
44 2010-8-17 20 30
14 2010-11-11 10 31
44 2010-8-18 10 31
74 2010-10-12 8 33
67 2010-8-10 6 34
14 2010-11-15 4 35
44 2010-8-23 4 35
58 2010-8-25 2 37
58 2010-8-27 2 37
38 rows selected
Executed in 0.156 seconds
SQL>
dense_rank()样例：
SQL> select o.lottery_id,trunc(o.buy_time,'dd'),sum(o.money)
2 ,dense_rank() over( order by sum(o.money) desc) rank
3 from tb_order o
4 where o.state=4
5 and o.buy_time> sysdate -120
6 --and o.buy_time< sysdate -30
7 group by o.lottery_id,trunc(o.buy_time,'dd')
8 ;
LOTTERY_ID TRUNC(O.BUY_TIME,'DD') SUM(O.MONEY) RANK
--------------------- ---------------------- ------------ ----------
10 2010-10-13 19369444 1
49 2010-10-13 2089524 2
10 2010-10-22 750410 3
10 2010-10-21 370164 4
53 2010-10-13 131072 5
10 2010-10-20 73730 6
51 2010-10-13 62208 7
14 2010-11-10 8544 8
58 2010-8-23 6084 9
74 2010-9-16 3238 10
58 2010-8-24 3201 11
67 2010-8-11 2252 12
44 2010-8-25 1458 13
58 2010-11-4 1384 14
44 2010-8-19 736 15
22 2010-11-9 278 16
14 2010-11-9 156 17
58 2010-8-26 132 18
74 2010-9-17 130 19
44 2010-8-16 112 20
LOTTERY_ID TRUNC(O.BUY_TIME,'DD') SUM(O.MONEY) RANK
--------------------- ---------------------- ------------ ----------
47 2010-8-24 106 21
58 2010-8-20 102 22
67 2010-8-25 102 22
14 2010-11-8 96 23
58 2010-11-8 78 24
58 2010-8-17 40 25
74 2010-9-15 32 26
44 2010-8-24 30 27
67 2010-9-25 26 28
44 2010-8-17 20 29
14 2010-11-11 10 30
44 2010-8-18 10 30
74 2010-10-12 8 31
67 2010-8-10 6 32
14 2010-11-15 4 33
44 2010-8-23 4 33
58 2010-8-25 2 34
58 2010-8-27 2 34
38 rows selected
Executed in 0.125 seconds
SQL>
row_number()样例：
SQL> select o.lottery_id,trunc(o.buy_time,'dd'),sum(o.money)
2 ,row_number() over( order by sum(o.money) desc) rank
3 from tb_order o
4 where o.state=4
5 and o.buy_time> sysdate -120
6 --and o.buy_time< sysdate -30
7 group by o.lottery_id,trunc(o.buy_time,'dd')
8 ;
LOTTERY_ID TRUNC(O.BUY_TIME,'DD') SUM(O.MONEY) RANK
--------------------- ---------------------- ------------ ----------
10 2010-10-13 19369444 1
49 2010-10-13 2089524 2
10 2010-10-22 750410 3
10 2010-10-21 370164 4
53 2010-10-13 131072 5
10 2010-10-20 73730 6
51 2010-10-13 62208 7
14 2010-11-10 8544 8
58 2010-8-23 6084 9
74 2010-9-16 3238 10
58 2010-8-24 3201 11
67 2010-8-11 2252 12
44 2010-8-25 1458 13
58 2010-11-4 1384 14
44 2010-8-19 736 15
22 2010-11-9 278 16
14 2010-11-9 156 17
58 2010-8-26 132 18
74 2010-9-17 130 19
44 2010-8-16 112 20
LOTTERY_ID TRUNC(O.BUY_TIME,'DD') SUM(O.MONEY) RANK
--------------------- ---------------------- ------------ ----------
47 2010-8-24 106 21
58 2010-8-20 102 22
67 2010-8-25 102 23
14 2010-11-8 96 24
58 2010-11-8 78 25
58 2010-8-17 40 26
74 2010-9-15 32 27
44 2010-8-24 30 28
67 2010-9-25 26 29
44 2010-8-17 20 30
14 2010-11-11 10 31
44 2010-8-18 10 32
74 2010-10-12 8 33
67 2010-8-10 6 34
14 2010-11-15 4 35
44 2010-8-23 4 36
58 2010-8-25 2 37
58 2010-8-27 2 38
38 rows selected
Executed in 0.172 seconds
SQL>
三个样例对比结果：
rank：如果出现两个相同的数据,那么后面的数据就会直接跳过这个排序号码
dense_rank：如果出现两个相同的数据,后面的数据不会跳过这个排序号码，仍然按顺序号码继续
row_number：哪怕是两个数据完全相同,排序号也会不一样,这个特性在我们想找出对应每个条件的唯一记录的时候有很大用处
取每个月每个彩种销售的前三名
SQL> select * from (
2 select o.lottery_id,trunc(o.buy_time,'mm'),sum(o.money)
3 ,rank() over(partition by trunc(o.buy_time,'mm') order by sum(o.money) desc) rank
4 from tb_order o
5 where o.state=4
6 and o.buy_time> sysdate -120
7 --and o.buy_time< sysdate -30
8 group by o.lottery_id,trunc(o.buy_time,'mm')
9 )
10 where rank<4
11 ;
LOTTERY_ID TRUNC(O.BUY_TIME,'MM') SUM(O.MONEY) RANK
--------------------- ---------------------- ------------ ----------
58 2010-8-1 9563 1
44 2010-8-1 2370 2
67 2010-8-1 2360 3
74 2010-9-1 3400 1
67 2010-9-1 26 2
10 2010-10-1 20563748 1
49 2010-10-1 2089524 2
53 2010-10-1 131072 3
14 2010-11-1 8810 1
58 2010-11-1 1462 2
22 2010-11-1 278 3
11 rows selected
Executed in 0.063 seconds
取最近销售的10个订单信息
SQL> select * from (
2 select o.id,o.lottery_id,o.buy_time,rank() over(order by o.buy_time desc ) rank
3 from tb_order o
4 where o.state=4
5 and o.buy_time > sysdate -30
6 ) where rank <11
7 ;
ID LOTTERY_ID BUY_TIME RANK
--------------------- --------------------- ----------- ----------
2224442592 14 2010-11-15 1
2224442577 14 2010-11-11 2
2224442552 14 2010-11-10 3
2224442539 14 2010-11-10 4
2224442526 14 2010-11-10 5
2224442513 14 2010-11-10 6
2224442510 14 2010-11-10 7
2224442507 14 2010-11-10 8
2224442504 14 2010-11-10 9
2224442501 14 2010-11-10 10
10 rows selected
Executed in 0.046 seconds
当然也可以用rownum实现
SQL>
SQL> select * from (
2 select o.id,o.lottery_id,o.buy_time,rownum rn
3 from tb_order o
4 where o.state=4
5 and o.buy_time > sysdate -30
6 order by o.buy_time desc
7 )
8 where rn<11
9 ;
ID LOTTERY_ID BUY_TIME RN
--------------------- --------------------- ----------- ----------
2224442592 14 2010-11-15 1
2224442577 14 2010-11-11 2
2224442552 14 2010-11-10 3
2224442539 14 2010-11-10 4
2224442526 14 2010-11-10 5
2224442513 14 2010-11-10 6
2224442510 14 2010-11-10 7
2224442507 14 2010-11-10 8
2224442504 14 2010-11-10 9
2224442501 14 2010-11-10 10
10 rows selected
Executed in 0.031 seconds
SQL>
从这两个sql的执行计划看，他们选择了不同索引，rownum选择的索引更高效一点
使用环境：rank分析函数主要用于分组统计排名或排序，取分组内前N行数据等场景
三. lag和lead函数介绍
取每个月的上个月和下个月的销售额度
SQL> select lottery_id,buy_time,money,
2 lag(money,2,0) over( partition by lottery_id order by buy_time) pre_month_money,
3 lag(money,1,0) over( partition by lottery_id order by buy_time) last_month_money,
4 lead(money,1,0) over( partition by lottery_id order by buy_time) next_month_money,
5 lead(money,2,0) over( partition by lottery_id order by buy_time) post_month_money
6 from
7 (
8 select o.lottery_id,trunc(o.buy_time,'dd') buy_time ,sum(o.money) money
9 from tb_order o
10 where o.state=4
11 and o.buy_time > sysdate -150
12 group by o.lottery_id,trunc(o.buy_time,'dd')
13 )
14 ;
LOTTERY_ID BUY_TIME MONEY PRE_MONTH_MONEY LAST_MONTH_MONEY NEXT_MONTH_MONEY POST_MONTH_MONEY
--------------------- ----------- ---------- --------------- ---------------- ---------------- ----------------
10 2010-10-13 19369444 0 0 73730 370164
10 2010-10-20 73730 0 19369444 370164 750410
10 2010-10-21 370164 19369444 73730 750410 0
10 2010-10-22 750410 73730 370164 0 0
14 2010-7-28 34 0 0 366 96
14 2010-7-29 366 0 34 96 156
14 2010-11-8 96 34 366 156 8544
14 2010-11-9 156 366 96 8544 10
14 2010-11-10 8544 96 156 10 4
14 2010-11-11 10 156 8544 4 0
14 2010-11-15 4 8544 10 0 0
22 2010-11-9 278 0 0 0 0
。。。
53 rows selected
Executed in 0.204 seconds
SQL>
说明：利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据.
LAG ( value_expr [, offset] [, default] ) OVER ( [query_partition_clause] order_by_clause )
LEAD ( value_expr [, offset] [, default] ) OVER ( [query_partition_clause] order_by_clause )
如果不指定offset（偏移量），默认值为1。如果offset（偏移量）超出窗口范围，则返回可指定默认值。如不指定默认值，则默认值为null。
使用环境：lag和lead函数主要用于把当前行和前后N行数据放在同一行里显示的场景
四. sum,avg,max,min移动计算数据介绍
取最近一个内，连续3天的销售总额，平均额，最大额，最小额
SQL> select lottery_id,buy_time,money,
2 sum(money) over (partition by lottery_id order by buy_time range between 1 preceding and 1 following) "3days_sum",
3 avg(money) over (partition by lottery_id order by buy_time range between 1 preceding and 1 following) "3days_avg",
4 max(money) over (partition by lottery_id order by buy_time range between 1 preceding and 1 following) "3days_max",
5 min(money) over (partition by lottery_id order by buy_time range between 1 preceding and 1 following) "3days_min"
6 from
7 (
8 select o.lottery_id,trunc(o.buy_time,'dd') buy_time ,sum(o.money) money
9 from tb_order o
10 where o.state=4
11 and o.buy_time > sysdate -30
12 group by o.lottery_id,trunc(o.buy_time,'dd')
13 )
14 ;
LOTTERY_ID BUY_TIME MONEY 3days_sum 3days_avg 3days_max 3days_min
--------------------- ----------- ---------- ---------- ---------- ---------- ----------
14 2010-11-8 96 252 126 156 96
14 2010-11-9 156 8796 2932 8544 96
14 2010-11-10 8544 8710 2903.33333 8544 10
14 2010-11-11 10 8554 4277 8544 10
14 2010-11-15 4 4 4 4 4
22 2010-11-9 278 278 278 278 278
58 2010-11-8 78 78 78 78 78
7 rows selected
Executed in 0.031 seconds
说明：
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
over（order by salary range between 50 preceding and 150 following）每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over（order by salary rows between 50 preceding and 150 following）每行对应的数据窗口是之前50行，之后150行
over（order by salary rows between unbounded preceding and unbounded following）每行对应的数据窗口是从第一行到最后一行
over（order by salary range between unbounded preceding and unbounded following） 每行对应的数据窗口是从最大值到最小值
五. ratio_to_report函数的介绍
取每天每个彩种销售占比的情况
SQL> select o.lottery_id,trunc(o.buy_time,'dd') buy_time ,sum(o.money) money,
2 ratio_to_report(sum(o.money)) over (partition by trunc(o.buy_time,'dd') )
3 from tb_order o
4 where o.state=4
5 and o.buy_time > sysdate -30
6 group by trunc(o.buy_time,'dd'),o.lottery_id
7 ;
LOTTERY_ID BUY_TIME MONEY RATIO_TO_REPORT(SUM(O.MONEY))O
--------------------- ----------- ---------- ------------------------------
14 2010-11-8 96 0.551724137931034
58 2010-11-8 78 0.448275862068966
14 2010-11-9 156 0.359447004608295
22 2010-11-9 278 0.640552995391705
14 2010-11-10 8544 1
14 2010-11-11 10 1
14 2010-11-15 4 1
7 rows selected
Executed in 0.031 seconds
SQL>
使用环境：适用计算每个分组里的每项占比的情况
转载自:http://blog.csdn.net/wyzxg/article/details/6063013
扫一扫
1156
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
