0- 窗口大小控制
表名:t4
表字段及内容:
a b c
2014 A 3
2014 B 1
2014 C 2
2015 A 4
2015 D 3
1- 问题一
描述:按a分组按b字段排序,对c取前后各一行的和
输出结果如下所示:
a b sum_c
2014 A 1
2014 B 5
2014 C 1
2015 A 3
2015 D 4
参考答案:
select
a,
b,
lag(c,1,0) over(partition by a order by b)+lead(c,1,0) over(partition by a order by b) as sum_c
from t4;
2- 问题二
描述:按a分组按b字段排序,对c取平均值,前一行与当前行的均值!
输出结果如下所示:
a b avg_c
2014 A 3
2014 B 2
2014 C 1.5
2015 A 4
2015 D 3.5
参考答案:
select
a,
b,
case when lag_c is null then c
else (c+lag_c)/2 end as avg_c
from
(
select
a,
b,
c,
lag(c,1) over(partition by a order by b) as lag_c
from t4
)temp;
总结:以上主要考察相关窗口函数的深入理解。