oracle常用分析函数(一)

r囧r小猫 2022-09-27 00:29 223阅读 0赞

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  
\--------------------- ---------------------- ------------ ----------  
                   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  
\--------------------- ---------------------- ------------ ----------  
                   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>

使用环境：适用计算每个分组里的每项占比的情况

\------续--------