SQL窗口函数实战-优快云博客

本文通过具体案例展示了如何使用SQL中的窗口函数来计算累积和及移动平均值等统计数据，包括sum(), avg()等函数的运用，并介绍了first_value()与last_value()函数的应用场景。

为计算一定的范围的累积和移动平均值，可以结合聚合函数使用：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