oracle的窗口windowing函数

转载 2012年03月31日 11:55:02

目录
=========================================
1.窗口函数简介
2.窗口函数示例-全统计
3.窗口函数进阶-滚动统计(累积/均值)
4.窗口函数进阶-根据时间范围统计
5.窗口函数进阶-first_value/last_value
6.窗口函数进阶-比较相邻记录

一、窗口函数简介:

到目前为止,我们所学习的分析函数在计算/统计一段时间内的数据时特别有用,但是假如计算/统计需要随着遍历记录集的每一条记录而进行呢?举些例子来说:

①列出每月的订单总额以及全年的订单总额
②列出每月的订单总额以及截至到当前月的订单总额
③列出上个月、当月、下一月的订单总额以及全年的订单总额
④列出每天的营业额及一周来的总营业额
⑤列出每天的营业额及一周来每天的平均营业额

仔细回顾一下前面我们介绍到的分析函数,我们会发现这些需求和前面有一些不同:前面我们介绍的分析函数用于计算/统计一个明确的阶段/记录集,而这里有部分需求例如2,需要随着遍历记录集的每一条记录的同时进行统计。

也即是说:统计不止发生一次,而是发生多次。统计不至发生在记录集形成后,而是发生在记录集形成的过程中。

这就是我们这次要介绍的窗口函数的应用了。它适用于以下几个场合:

①通过指定一批记录:例如从当前记录开始直至某个部分的最后一条记录结束
②通过指定一个时间间隔:例如在交易日之前的前30天
③通过指定一个范围值:例如所有占到当前交易量总额5%的记录

二、窗口函数示例-全统计:

下面我们以需求:列出每月的订单总额以及全年的订单总额为例,来看看窗口函数的应用。

【1】测试环境:

SQL> desc orders;
 名称                    是否为空? 类型
 
----------------------- -------- ----------------
 MONTH                            NUMBER(2)
 TOT_SALES                    
NUMBER

SQL
> 


【2】测试数据:

SQL> select * from orders;

     
MONTH  TOT_SALES
---------- ----------
         1     610697
         
2     428676
         
3     637031
         
4     541146
         
5     592935
         
6     501485
         
7     606914
         
8     460520
         
9     392898
        
10     510117
        
11     532889
        
12     492458

已选择12行。


【3】测试语句:

回忆一下前面《Oracle开发专题之:分析函数(OVER)》一文中,我们使用了sum(sum(tot_sales)) over (partition by region_id) 来统计每个分区的订单总额。现在我们要统计的不单是每个分区,而是所有分区,partition by region_id在这里不起作用了。

Oracle为这种情况提供了一个子句:rows between ... preceding and ... following。从字面上猜测它的意思是:在XXX之前和XXX之后的所有记录,实际情况如何让我们通过示例来验证:

SQL> select month,
  
2         sum(tot_sales) month_sales,
  
3         sum(sum(tot_sales)) over (order by month
  
4            rows between unbounded preceding and unbounded following) total_sales
  
5    from orders
  
6   group by month;

     
MONTH MONTH_SALES TOTAL_SALES
---------- ----------- -----------
         1      610697     6307766
         
2      428676     6307766
         
3      637031     6307766
         
4      541146     6307766
         
5      592935     6307766
         
6      501485     6307766
         
7      606914     6307766
         
8      460520     6307766
         
9      392898     6307766
        
10      510117     6307766
        
11      532889     6307766
        
12      492458     6307766

已选择12行。


绿色高亮处的代码在这里发挥了关键作用,它告诉oracle统计从第一条记录开始至最后一条记录的每月销售额。这个统计在记录集形成的过程中执行了12次,这时相当费时的!但至少我们解决了问题。

unbounded preceding and unbouned following的意思针对当前所有记录的前一条、后一条记录,也就是表中的所有记录。那么假如我们直接指定从第一条记录开始直至末尾呢?看看下面的结果:

SQL> select month,
  
2         sum(tot_sales) month_sales,
  
3         sum(sum(tot_sales)) over (order by month
  
4            rows between 1 preceding and unbounded following) all_sales
  
5    from orders
  
6   group by month;

     
MONTH MONTH_SALES  ALL_SALES
---------- ----------- ----------
         1      610697    6307766
         
2      428676    6307766
         
3      637031    5697069
         
4      541146    5268393
         
5      592935    4631362
         
6      501485    4090216
         
7      606914    3497281
         
8      460520    2995796
         
9      392898    2388882
        
10      510117    1928362
        
11      532889    1535464
        
12      492458    1025347

已选择12行。

(可以明显的看到,他的sum(sum(total_sales))是从当前记录的前一条到底下所有记录的汇总值)
很明显这个语句错了。实际1在这里不是从第1条记录开始的意思,而是指当前记录的前一条记录。preceding前面的修饰符是告诉窗口函数执行时参考的记录数,如同unbounded就是告诉oracle不管当前记录是第几条,只要前面有多少条记录,都列入统计的范围。

三、窗口函数进阶-滚动统计(累积/均值):


考虑前面提到的第2个需求:列出每月的订单总额以及截至到当前月的订单总额。也就是说2月份的记录要显示当月的订单总额和1,2月份订单总额的和。3月份要显示当月的订单总额和1,2,3月份订单总额的和,依此类推。

很明显这个需求需要在统计第N月的订单总额时,还要再统计这N个月来的订单总额之和。想想上面的语句,假如我们能够把and unbounded following换成代表当前月份的逻辑多好啊!很幸运的是Oracle考虑到了我们这个需求,为此我们只需要将语句稍微改成: curreent row就可以了。

SQL> select month,
  
2         sum(tot_sales) month_sales,
  
3         sum(sum(tot_sales)) over(order by month
  
4           rows between unbounded preceding and current row) current_total_sales
  
5    from orders
  
6   group by month;

     
MONTH MONTH_SALES CURRENT_TOTAL_SALES
---------- ----------- -------------------
         1      610697              610697
         
2      428676             1039373
         
3      637031             1676404
         
4      541146             2217550
         
5      592935             2810485
         
6      501485             3311970
         
7      606914             3918884
         
8      460520             4379404
         
9      392898             4772302
        
10      510117             5282419
        
11      532889             5815308
        
12      492458             6307766

已选择12行。


现在我们能得到滚动的销售总额了!下面这个统计结果看起来更加完美,它展现了所有我们需要的数据:

SQL> select month,
  
2         sum(tot_sales) month_sales,
  
3         sum(sum(tot_sales)) over(order by month
  
4         rows between unbounded preceding and current row) current_total_sales,
  
5         sum(sum(tot_sales)) over(order by month
  
6         rows between unbounded preceding and unbounded following) total_sales
  
7    from orders
  
8   group by month;

     
MONTH MONTH_SALES CURRENT_TOTAL_SALES TOTAL_SALES
---------- ----------- ------------------- -----------
         1      610697              610697     6307766
         
2      428676             1039373     6307766
         
3      637031             1676404     6307766
         
4      541146             2217550     6307766
         
5      592935             2810485     6307766
         
6      501485             3311970     6307766
         
7      606914             3918884     6307766
         
8      460520             4379404     6307766
         
9      392898             4772302     6307766
        
10      510117             5282419     6307766
        
11      532889             5815308     6307766
        
12      492458             6307766     6307766

已选择12行。

 

 

 


在一些销售报表中我们会时常看到求平均值的需求,有时可能是针对全年的数据求平均值,有时会是针对截至到当前的所有数据求平均值。很简单,只需要将:

sum(sum(tot_sales))
换成avg(sum(tot_sales))即可。

四、窗口函数进阶-根据时间范围统计:


前面我们说过,窗口函数不单适用于指定记录集进行统计,而且也能适用于指定范围进行统计的情况,例如下面这个SQL语句就统计了当天销售额和五天内的平均销售额:

 select trunc(order_dt) day,
             
sum(sale_price) daily_sales,
             
avg(sum(sale_price)) over (order by trunc(order_dt)
                      range between interval '2' day preceding
 
                                     
and interval '2' day following) five_day_avg
   
from cust_order
 
where sale_price is not null 
     
and order_dt between to_date('01-jul-2001','dd-mon-yyyy')
     
and to_date('31-jul-2001','dd-mon-yyyy')

 

为了对指定范围进行统计,Oracle使用关键字rangeinterval来指定一个范围。上面的例子告诉Oracle查找当前日期的前2天,后2天范围内的记录,并统计其销售平均值。

五、窗口函数进阶-first_value/last_value

Oracle
提供了2个额外的函数:first_valuelast_value,用于在窗口记录集中查找第一条记录和最后一条记录。假设我们的报表需要显示当前月、上一个月、后一个月的销售情况,以及每3个月的销售平均值,这两个函数就可以派上用场了。

select month,
             first_value(
sum(tot_sales)) over (order by month 
                                    rows 
between 1 preceding and 1 following) prev_month,
 
             
sum(tot_sales) monthly_sales,
 
             last_value(
sum(tot_sales)) over (order by month 
                                  rows 
between 1 preceding and 1 following) next_month,
 
             
avg(sum(tot_sales)) over (order by month 
                                 rows between 1 preceding and 1 following
) rolling_avg
    
from orders
 
where year = 2001 
      
and region_id = 6
  
group by month
 
order by month;

 

首先我们来看:rows between 1 preceding and 1 following告诉Oracle在当前记录的前一条、后一条范围内查找并统计,而first_valuelast_value在这3条记录中至分别找出第一条、第三条记录,这样我们就轻松地得到相邻三个月的销售记录及平均值了!

六、窗口函数进阶-比较相邻记录:

通过第五部分的学习,我们知道了如何利用窗口函数来显示相邻的记录,现在假如我们想每次显示当月的销售额和上个月的销售额,应该怎么做呢?

从第五部分的介绍我们可以知道,利用first_value(sum(tot_sales) over (order by month rows between 1 preceding and 0 following))就可以做到了,其实Oracle还有一个更简单的方式让我们来比较2条记录,它就是lag函数。

leg
函数类似于precedingfollowing子句,它能够通过和当前记录的相对位置而被应用,在比较同一个相邻的记录集内两条相邻记录的时候特别有用。

select  month,            
          
sum(tot_sales) monthly_sales,
          lag(sum(tot_sales), 1over (order by month
prev_month_sales
   
from orders
 
where year = 2001
      
and region_id = 6
  
group by month
 
order by month;

 

lag(sum(tot_sales),1)中的1表示以1月为基准。

Oracle 学习之窗口函数(over)

在工作的过程中,我们有时候需要编写复杂的sql脚本.在这个时候,如果掌握一些数据库函数,对我们的工作可以事半功倍的作用. 这篇主要记录学习Oracle 的窗口函数的一些体会....

[Oracle] 分析函数(2)- 开窗(WINDOWING)

Oracle分析函数的开窗部分分为3个类型

oracle 每日一题-分析函数的RANGE/ROW窗口

作者:        Kim Berg Hansen   运行环境:SQLPLUS, SERVEROUTPUT已打开 我有一张表,登记着我在哪些日期有手机漫游(漫游的意思是我在国外)。我还有一个...

Oracle开发专题之:窗口函数

目录 ========================================= 1.窗口函数简介 2.窗口函数示例-全统计 3.窗口函数进阶-滚动统计(累积/均值) 4.窗口函数进...

oracle分析函数:一、窗口子句的使用

/************************ *标题:分析函数analytic functions *时间:2016-07-07 *作者:clark ******************...

Oracle开发专题之:窗口函数的用法

目录 ========================================= 1.窗口函数简介 2.窗口函数示例-全统计 3.窗口函数进阶-滚动统计(累积/均值) 4.窗口函数进...

ORACLE窗口函数over的使用

与聚集函数一样,窗口函数也针对定义的行集(组)执行聚集,但它不像聚集函数那样每组之返回一个值,窗口函数可以为每组返回多个值。实际上,DB2中称这种函数为联机分析处理OLAP函数,而Oracle把它们称...
  • wwbmyos
  • wwbmyos
  • 2012年03月15日 13:18
  • 4076

Oracle开发之:窗口函数 3( rows between unbounded preceding and current row)

目录 ========================================= 1.窗口函数简介 2.窗口函数示例-全统计 3.窗口函数进阶-滚动统计(累积/均值) 4.窗口函数进...

oracle窗口函数的使用

窗口函数可以计算一定 记录范围内、一定值域内、或者一段时间内的累计和以及移动平均值等等.之所以使用窗口这个术语,是因为对结果的处理使用了一个滑动的查询结果集范围。 窗口可以与下面这些函数结合使用:s...

oracle 窗口函数

http://www.blogjava.net/pengpenglin/archive/2008/06/28/211334.html#part5 目录 ====================...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle的窗口windowing函数
举报原因:
原因补充:

(最多只允许输入30个字)