0- 累计求值
表名:t3
表字段及内容:
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 3
2014 B 4
2014 C 6
2015 A 4
2015 D 7
参考答案:
select
a,
b,
c,
sum(c) over(partition by a order by b) as sum_c
from t3;
2- 问题二
描述:按a分组按b字段排序,对c取累计平均值
输出结果如下所示:
a b avg_c
2014 A 3
2014 B 2
2014 C 2
2015 A 4
2015 D 3.5
参考答案:
select
a,
b,
c,
avg(c) over(partition by a order by b) as avg_c
from t3;
3- 问题三
描述:按a分组按b字段排序,对b取累计排名比例
输出结果如下所示:
a b ratio_c
2014 A 0.33
2014 B 0.67
2014 C 1.00
2015 A 0.50
2015 D 1.00
参考答案:
select
a,
b,
c,
round(row_number() over(partition by a order by b) / (count(c) over(partition by a)),2) as ratio_c
from t3
order by a,b;
4- 问题四
描述:按a分组按b字段排序,对b取累计求和比例
输出结果如下所示:
a b ratio_c
2014 A 0.50
2014 B 0.67
2014 C 1.00
2015 A 0.57
2015 D 1.00
参考答案:
select
a,
b,
c,
round(sum(c) over(partition by a order by b) / (sum(c) over(partition by a)),2) as ratio_c
from t3
order by a,
总结:以上主要考察相关窗口函数的深入理解。