窗口函数

为计算一定的范围的累积和移动平均值，可以结合聚合函数使用：sum() avg() max() min() count() variance() stddev() 

first_value() last_value()结合使用。

select 
t.prd_type_id
,t.amount
,t.month
,sum(t.amount) over(order by t.prd_type_id desc rows between UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
 from all_sales t
where t.year=2003
and t.month =1
and t.amount is not null
and t.emp_id=21
order by t.prd_type_id desc
============================
1	4	3034.84	1	3034.84
2	3	1034.84	1	4069.68
3	2	1034.84	1	5104.52
4	1	10034.84	1	15139.36

 

 

select 
t.prd_type_id
,t.amount
,t.month
,avg(t.amount) over(order by t.prd_type_id desc rows between 3 PRECEDING AND current row) AS cumulative_amount
 from all_sales t
where t.year=2003
and t.month =1
and t.amount is not null
and t.emp_id=21
order by t.prd_type_id desc
=========================
1	4	3034.84	1	3034.84
2	3	1034.84	1	2034.84
3	2	1034.84	1	1701.50666666667
4	1	10034.84	1	3784.84

 

select 
t.prd_type_id
,t.amount
,t.month
,avg(t.amount) over(order by t.prd_type_id desc rows between 1 PRECEDING AND 1 following) AS cumulative_amount
 from all_sales t
where t.year=2003
and t.month =1
and t.amount is not null
and t.emp_id=21
order by t.prd_type_id desc
=====================
1	4	3034.84	1	2034.84
2	3	1034.84	1	1701.50666666667
3	2	1034.84	1	4034.84
4	1	10034.84	1	5534.84

 

select 
t.prd_type_id
,t.amount
,t.month
,first_value(t.amount) over(order by t.prd_type_id desc rows between 1 PRECEDING AND 1 following) AS cumulative_amount
,last_value(t.amount) over(order by t.prd_type_id desc rows between 1 PRECEDING AND 1 following) AS cumulative_amount
 from all_sales t
where t.year=2003
and t.month =1
and t.amount is not null
and t.emp_id=21
order by t.prd_type_id desc
==========================
1	4	3034.84	1	3034.84	1034.84
2	3	1034.84	1	3034.84	1034.84
3	2	1034.84	1	1034.84	10034.84
4	1	10034.84	1	1034.84	10034.84