窗口函数(一)
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
运行结果为:
简单做人 从容做事 快乐生活