oracle窗口函数的使用

窗口函数可以计算一定 记录范围内、一定值域内、或者一段时间内的累计和以及移动平均值等等.之所以使用窗口这个术语，是因为对结果的处理使用了一个滑动的查询结果集范围。
窗口可以与下面这些函数结合使用：sum(),avg(),max(),min(),count(),variance()和stddev();窗口也可以和first_value()与last_value()结合使用，这时候返回窗口中的第一个值和最后一个值。
先看下面列子
1 计算累计和
  下面这个查询通过执行累计和操作计算出2003年从一月到12月的累计销量。注意每月的销量都会加到累计值中，这样累计和在每个月结束时都会增长
select month,

       sum(amount) as month_amount,

       sum(sum(amount)) over (order by month rows between unbounded preceding and current row)
as cumulative_amount

  from all_sales

 where year=2003

 group by month
 order by month

结果集显示如下：

id	month	month_amount	cumulative_amount
1	1	95525.55	95525.55
2	2	116671.6	212197.15
3	3	160307.92	372505.07
4	4	175998.8	548503.87
5	5	154349.44	702853.31
6	6	124951.36	827804.67
7	7	170296.16	998100.83
8	8	212735.68	1210836.51
9	9	199609.68	1410446.19
10	10	264480.79	1674926.98
11	11	160221.98	1835148.96
12	12	137336.17	1972485.13

现在分析一下这个表达式
1 sum(amount) 计算出销量的总和。外部的sum()计算累计销量。
2 order by month按照月份对查询读取的记录进行排序
3 rows between unbounded preceding and current row定义了窗口的行；窗口的终点是当前行。
 rows between unbounded preceding and current row也可以是rows unbounded preceding
因此整个表达式的意思是从查询读取的第一行开始，计算每月的销量的累计和
每次处理窗口的一行记录时，都是从该窗口的第一条记录开始。每一行记录出来完之后，就将当前行的数量加到累计和中，并向下移动窗口的终点到下一行。然后继续处理，直到查询读取的最后一行也被处理为止。

下面这个查询使用累计和来计算2003年6月到12月的累计销量。注意使用rows unbounded preceding 来隐式地说明窗口的终点是当前行：
select month,

       sum(amount) as mount_amount,

       sum(sum(amount)) over( order by month rows unbounded preceding) as cumulative_amount
  from all_sales
 where year=2003

   and month between 6 and 12
 group by month
 order by month

 

下面这个查询计算本月与前3个月之间销量的移动平均值

select month,

       sum(amount) as month_amount,

       avg(sum(amount)) over (order by month rows between 3 preceding and current row) as moving_average
  from all_sales
 where year=2003
 group by month
 order by month

结果显示

id	month	month_amount	moving_average
1	1	95525.55	95525.55
2	2	116671.6	106098.575
3	3	160307.92	124168.3567
4	4	175998.8	137125.9675
5	5	154349.44	151831.94
6	6	124951.36	153901.88
7	7	170296.16	156398.94
8	8	212735.68	165583.16
9	9	199609.68	176898.22
10	10	264480.79	211780.5775
11	11	160221.98	209262.0325
12	12	137336.17	190412.155

现在分解一下这个表达式：
1sum(amount) 计算出销量的总和。外部的avg()计算平均值
2 order by month按照月份对查询读出的记录进行排序
3 rows between 3 preceding and current row定义了窗口的起点为当前记录的前面第三条记录；窗口的终点为当前记录。也可以使用rows 3 preceding提前隐式的指定窗口大小，所得到的查询结果完全相同。
       因此，整个表达式的意思就是计算当前月份和此前三个月内的销量移动平均值。由于最开始的两个月可用的数据少于三个月，因此它们的移动平均值只是基于可用的月份计算的。
       该窗口的起点和终点都是始于查询读取的的行#1；每次处理一行时，窗口的终点就向下移动。但是只有当行#4处理完毕之后，窗口的起点才向下移动，从此之后，每当一条记录处理完毕时，窗口的起点也会向下移动。整个过程一直持续到查询读取的最后一条记录被处理为止。
---------------------------------------------------------------------------

 

3 计算中心平均值
下面这个查询计算当前月份前、后各一个月内的销量移动平均值：
select month,

       sum(amount) as month_amount,

       avg(sum(amount)) over (order by month rows between 1 preceding and 1 following) as moving_average
  from all_sales
 where year=2003
 group by month
 order by month
现在分析这个表达式
rows between 1 preceding and 1 following定义了窗口的起点是当前记录之前的那条记录。窗口的终点是当前记录之后的那条记录。
因此整个表达式的意思就是计算当前月、前一个月、后一个月的销量移动平均值。由于第一个月和最后一个月可以参与计算的数据都少于三个月，因此移动平均值的计算只基于可用的数据。
---------------------------------------------------------------------------

 

 

4 用first_value()和last_value()获取第一条记录和最后一条记录
  first_value()和last_value()函数可以获取第一行和最后一行的数据。下面这个查询用first_value()和last_value()获取前一个月和最后一个月的销量：

select month,

       sum(amount) as mount_amount,

       first_value(sum(amount) )over (order by month rows between 1 preceding and 1 following) as previous_month_amount,
       last_value(sum(amount)) over (order by month rows between 1 preceding and 1 following) as next_month_amount
  from all_sales
 where year=2003
 group by month
 order by month

结果集：

id	month	month_amount	previous_month_amount	next_month_amount
1	1	95525.55	9165525.55	1671.6
2	2	116671.6	9605525.55	1307.92
3	3	160307.92	17116671.6	5998.8
4	4	175998.8	11560307.92	4349.44
5	5	154349.44	12175998.8	4951.36
6	6	124951.36	1154349.44	70296.16
7	7	170296.16	2124951.36	12735.68
8	8	212735.68	1170296.16	99609.68
9	9	199609.68	2212735.68	64480.79
10	10	264480.79	199609.68	160221.98
11	11	160221.98	264480.79	137336.17
12	12	137336.17	160221.98	137336.17

下面这个查询将当前月份的销量除以前一个月的销量(保存在curr_div_prev列中),并将当前月份的销量除以下一个月的销量(保存在curr_dive_next列中)：
select month,

       sum(amount) as mount_amount,

       sum(amount)/first_value(sum(amount) )over (order by month rows between 1 preceding and 1 following) as curr_div_prev,
       sum(amount)/last_value(sum(amount)) over (order by month rows between 1 preceding and 1 following) as curr_div_next
  from all_sales
 where year=2003
 group by month
 order by month

 

结果集:

id	month	month_amount	curr_div_prev	curr_div_next
1	1	95525.55	1	0.818755807
2	2	116671.6	1.221365383	0.727796855
3	3	160307.92	1.374009785	0.910846665
4	4	175998.8	1.097879631	1.140261993
5	5	154349.44	0.876991434	1.235276191
6	6	124951.36	0.809535558	0.733729756
7	7	170296.16	1.362899611	0.800505867
8	8	212735.68	1.249210082	1.065758334
9	9	199609.68	0.93829902	0.754722791
10	10	264480.79	1.3249898	1.650714777
11	11	160221.98	0.605798175	1.166640806
12	12	137336.17	0.857161858	1