62.Oracle数据库SQL开发之 高级查询——使用分析函数之窗口函数
欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/49847049
窗口函数可以计算一定的记录范围内、一定值域内、或者一段时间内的累积和以及移动平均值。查询返回一组记录，称为结果集。窗口这个术语用来描述结果集中记录的子集。
窗口可以与下面这些函数结合使用：SUM,AVG,MAX,MIN,COUNT,VARIANCE,STDDEV。也可以和FIRST_VALUE和LAST_VALUE结合使用。
1. 计算累积和store@PDB1> select month,sum(amount) as month_amount,sum(sum(amount))over (order by month rows between unbounded preceding and current row) ascumulative_amount from all_sales where year=2003 group by month order by month;
MONTH MONTH_AMOUNT CUMULATIVE_AMOUNT
---------- ------------ -----------------
1 95525.55 95525.55
2 116671.6 212197.15
3 160307.92 372505.07
4 175998.8 548503.87
5 154349.44 702853.31
6 124951.36 827804.67
7 170296.16 998100.83
8 212735.68 1210836.51
9 199609.68 1410446.19
10 264480.79 1674926.98
11 160221.98 1835148.96
12 137336.17 1972485.13
12 rows selected.
每月的销量都会加到累积和中，累积和在每个月结束时都会增长。
查询使用累积和来计算2003年6月到12月的累积销量如下：
store@PDB1> select month,sum(amount) as month_amount,sum(sum(amount))over (order by month rows between unbounded preceding and current row) ascumulative_amount from all_sales where year=2003 and month between 6 and 12group by month order by month;
MONTH MONTH_AMOUNT CUMULATIVE_AMOUNT
---------- ------------ -----------------
6 124951.36 124951.36
7 170296.16 295247.52
8 212735.68 507983.2
9 199609.68 707592.88
10 264480.79 972073.67
11 160221.98 1132295.65
12 137336.17 1269631.82
7 rows selected.
2. 计算移动平均值store@PDB1> select month,sum(amount) asmonth_amount,avg(sum(amount)) over (order by month rows between 3 preceding andcurrent row) as moving_average from all_sales where year=2003 group by monthorder by month;
MONTH MONTH_AMOUNT MOVING_AVERAGE
---------- ------------ --------------
1 95525.55 95525.55
2 116671.6 106098.575
3 160307.92 124168.357
4 175998.8 137125.968
5 154349.44 151831.94
6 124951.36 153901.88
7 170296.16 156398.94
8 212735.68 165583.16
9 199609.68 176898.22
10 264480.79 211780.578
11 160221.98 209262.033
12 137336.17 190412.155
12 rows selected.
计算本月与前3个月之间销量的移动平均值。
3. 计算中心平均值查询计算当前月份前、后各一个月内的销量移动平均值：
store@PDB1> select month,sum(amount) asmonth_amount,avg(sum(amount)) over (order by month rows between 1 preceding and1 following) as moving_average from all_sales where year=2003 group by monthorder by month;
MONTH MONTH_AMOUNT MOVING_AVERAGE
---------- ------------ --------------
1 95525.55 106098.575
2 116671.6 124168.357
3 160307.92 150992.773
4 175998.8 163552.053
5 154349.44 151766.533
6 124951.36 149865.653
7 170296.16 169327.733
8 212735.68 194213.84
9 199609.68 225608.717
10 264480.79 208104.15
11 160221.98 187346.313
12 137336.17 148779.075
12 rows selected.
4. 用FIRST_VALUE和LAST_VALUE获取第一条记录和最后一条记录FIRST_VALUE和LAST_VALUE函数可以获取窗口中的第一行和最后一行数据。获得前一个月和后一月的销量：
store@PDB1> select month,sum(amount) asmonth_amount,first_value(sum(amount)) over (order by month rows between 1preceding and 1 following) as previous_month_amount,last_value(sum(amount))over (order by month rows between 1 preceding and 1 following) asnext_month_amount
fromall_sales where year=2003 group by month order by month;
MONTH MONTH_AMOUNT PREVIOUS_MONTH_AMOUNT NEXT_MONTH_AMOUNT
---------- ------------ --------------------------------------
1 95525.55 95525.55 116671.6
2 116671.6 95525.55 160307.92
3 160307.92 116671.6 175998.8
4 175998.8 160307.92 154349.44
5 154349.44 175998.8 124951.36
6 124951.36 154349.44 170296.16
7 170296.16 124951.36 212735.68
8 212735.68 170296.16 199609.68
9 199609.68 212735.68 264480.79
10 264480.79 199609.68 160221.98
11 160221.98 264480.79 137336.17
12 137336.17 160221.98 137336.17
12 rows selected.
查询当前月份的销量除以前一个月的销量，并将当前月份的销量除以下一个月的销量：
store@PDB1> select month,sum(amount) asmonth_amount, sum(amount)/first_value(sum(amount)) over (order by month rowsbetween 1 preceding and 1 following) ascurr_div_prev,sum(amount)/LAST_VALUE(SUM(amount)) over (order by month rowsbetween 1 preceding and 1 following) as curr_div_next from all_sales whereyear=2003 group by month order by month;
MONTH MONTH_AMOUNT CURR_DIV_PREVCURR_DIV_NEXT
---------- ------------ --------------------------
1 95525.55 1 .818755807
2 116671.6 1.22136538 .727796855
3 160307.92 1.37400978 .910846665
4 175998.8 1.09787963 1.14026199
5 154349.44 .876991434 1.23527619
6 124951.36 .809535558 .733729756
7 170296.16 1.36289961 .800505867
8 212735.68 1.24921008 1.06575833
9 199609.68 .93829902 .754722791
10 264480.79 1.3249898 1.65071478
11 160221.98 .605798175 1.16664081
12 137336.17 .857161858 1
12 rows selected.
