oracle窗口函数的使用.docVIP

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 monthorder by month结果集显示如下：d month month_amount cumulative_amount1??? 1??? 95525.55??? 95525.552??? 2??? 116671.6??? 212197.153??? 3??? 160307.92??? 372505.074??? 4??? 175998.8??? 548503.875??? 5??? 154349.44??? 702853.316??? 6??? 124951.36??? 827804.677??? 7??? 170296.16??? 998100.838??? 8??? 212735.68??? 1210836.519??? 9??? 199609.68??? 1410446.1910??? 10??? 264480.79??? 1674926.9811??? 11??? 160221.98??? 1835148.9612??? 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_amountfrom all_saleswhere year=2003 and month between 6 and 12group by monthorder by month oracle窗口函数的使用（二） 下面这个查询计算本月与前3个月之间销量的移动平均值select month,sum(amount) as month_amount,avg(sum(amount)) over (order by month rows between 3 preceding and current row) as moving_averagefrom all_saleswhere year=2003group by monthorder by month结果显示1??? 1??? 95525.55??? 95525.552??? 2??? 116671.6??? 106098.5753??? 3??? 160307.92??? 124168.3566666674??? 4??? 175998.8?