窗口函数(一)

 

 1 --窗口函数简介
 2 --用途：用于计算一定记录范围内,一定值域内,一定时间内的累计和或者移动平均值
 3 --能与窗口函数搭配的聚合函数为:sum avg min max first_value last_value 
 4 --                             count stddev variance
 5 --实例：1,计算1到12个月的累计和
 6 --计算累积销量,注意开窗语句为前面所有行到当前行
 7 select a.month,
 8        sum(a.amount),
 9        sum(sum(a.amount)) over(order by a.month rows between unbounded preceding and current row) as cumulative_amount
10   from all_sales a
11  where a.year = 2003
12    and a.amount is not null
13  group by a.month
14  order by a.month

运行结果为:

 

1 --返回1至6月的累计销量情况
2 select a.month,
3        sum(a.amount),
4        sum(sum(a.amount)) over(order by a.month rows between unbounded preceding and current row) as cumulative_row
5   from all_sales a
6  where a.year = 2003
7    and a.month < 7
8  group by a.month
9  order by a.month

运行结果为:

posted @ 2012-11-12 22:36  原想  阅读(142)  评论(0)  编辑  收藏  举报