select d.subject,d.stu_name,d.score,"temp".avg_score from score_demo d
LEFT JOIN (
select subject,avg(score) avg_score from score_demo
group by subject
) temp on d.subject = temp.subject
-----
subject stu_name score avg_score
Chinese francs 70 73.3333333333333
Chinese matiler 70 73.3333333333333
Chinese tutu 80 73.3333333333333
English matiler 75 75
English francs 90 75
English tutu 60 75
Math francs 80 81.3333333333333
Math matiler 99 81.3333333333333
Math tutu 65 81.3333333333333
而使用窗口函数，基本上一句搞掂:
select subject,stu_name,score,avg(score)over(partition by subject) from score_demo
subject stu_name score avg
Chinese francs 70 73.3333333333333
Chinese matiler 70 73.3333333333333
Chinese tutu 80 73.3333333333333
English matiler 75 75
English francs 90 75
English tutu 60 75
Math francs 80 81.3333333333333
Math matiler 99 81.3333333333333
Math tutu 65 81.3333333333333
sum() over()
演示sum()聚合函数后接over属性的窗口函数，此窗口函数用来计算分组后数据的总分值
例如：查询每名学生学习成绩并且显示课程的总分
select subject,stu_name,score,sum(score)over(partition by subject) from score_demo
subject stu_name score sum
Chinese francs 70 220
Chinese matiler 70 220
Chinese tutu 80 220
English matiler 75 225
English francs 90 225
English tutu 60 225
Math francs 80 244
Math matiler 99 244
Math tutu 65 244
count() over()
演示count()聚合函数后接over属性的窗口函数，此窗口函数用来计算分组后数据的总条数值
select subject,stu_name,score,COUNT(score)over(partition by subject) from score_demo
subject stu_name score count
Chinese francs 70 3
Chinese matiler 70 3
Chinese tutu 80 3
English matiler 75 3
English francs 90 3
English tutu 60 3
Math francs 80 3
Math matiler 99 3
Math tutu 65 3
max() over()
演示max()聚合函数后接over属性的窗口函数，此窗口函数用来计算分组后数据的最大值
select subject,stu_name,score,MAX(score)over(partition by subject) from score_demo
subject stu_name score max
Chinese francs 70 80
Chinese matiler 70 80
Chinese tutu 80 80
English matiler 75 90
English francs 90 90
English tutu 60 90
Math francs 80 99
Math matiler 99 99
Math tutu 65 99
min() over()
演示min()聚合函数后接over属性的窗口函数，此窗口函数用来计算分组后数据的最小值
select subject,stu_name,score,min(score)over(partition by subject) from score_demo
subject stu_name score min
Chinese francs 70 70
Chinese matiler 70 70
Chinese tutu 80 70
English matiler 75 60
English francs 90 60
English tutu 60 60
Math francs 80 65
Math matiler 99 65
Math tutu 65 65
row_number()
row_number()窗口函数对结果集分组后的数据标注行号，从1开始的..
select row_number() over(partition by subject order by score desc),* from score_demo
row_number id subject stu_name score
1 3 Chinese tutu 80
2 1 Chinese francs 70
3 2 Chinese matiler 70
1 5 English francs 90
2 4 English matiler 75
3 6 English tutu 60
1 8 Math matiler 99
2 7 Math francs 80
3 9 Math tutu 65
如果不指定partition属性，row_number()就类似于oracle的rownum记录所有行号。如：
select row_number() over(order by id) rownum,* from score_demo
1 1 Chinese francs 70
2 2 Chinese matiler 70
3 3 Chinese tutu 80
4 4 English matiler 75
5 5 English francs 90
6 6 English tutu 60
7 7 Math francs 80
8 8 Math matiler 99
9 9 Math tutu 65
rank()
rank()窗口函数和row_number()函数类似， 主要区分为当组内某行字段值相同，行号重复并且行号产生间隙，如下:
select rank() over(partition by subject order by score) ,* from score_demo
-----
rank id subject stu_name score
1 2 Chinese matiler 70
1 1 Chinese francs 70
3 3 Chinese tutu 80
1 6 English tutu 60
2 4 English matiler 75
3 5 English francs 90
1 9 Math tutu 65
2 7 Math francs 80
3 8 Math matiler 99
从返回结果看到..第一二行都是1，第三行就3了，产生了间隙...
dense_rank()
dese_rank()函数和rank()函数类似，主要区别为当组内某个字段值相同时，虽然行号重复，但行号不产生间隙。
select dense_rank() over(partition by subject order by score),* from score_demo
dense_rank id subject stu_name score
1 2 Chinese matiler 70
1 1 Chinese francs 70
2 3 Chinese tutu 80
1 6 English tutu 60
2 4 English matiler 75
3 5 English francs 90
1 9 Math tutu 65
2 7 Math francs 80
3 8 Math matiler 99
percent_rank()
percent_rank()函数：当前行的相对排名： (rank- 1) / (总行数 - 1)，得到的数在0-1之间[0,1)， 例如：限制序号在0~1之间(0作为第一个序)-->[0,1):
select percent_rank() over(partition by subject order by score),* from score_demo
percent_rank id subject stu_name score
0 2 Chinese matiler 70
0 1 Chinese francs 70
1 3 Chinese tutu 80
0 6 English tutu 60
0.5 4 English matiler 75
1 5 English francs 90
0 9 Math tutu 65
0.5 7 Math francs 80
1 8 Math matiler 99
cume_dist()
cume_dist()函数：当前行的相对排名： (当前行前面的行数 或 与当前行同等的行的行数)/(总行数)
注意:上面的percernt_rank()函数默认是从0开始排序的，如果需要使用相对0~1之间(0,1)的排名 例如，限制序号在0~1之间相对排名(0,1)
select cume_dist() over(partition by subject order by score),* from score_demo
cume_dist id subject stu_name score
0.666666666666667 2 Chinese matiler 70
0.666666666666667 1 Chinese francs 70
1 3 Chinese tutu 80
0.333333333333333 6 English tutu 60
0.666666666666667 4 English matiler 75
1 5 English francs 90
0.333333333333333 9 Math tutu 65
0.666666666666667 7 Math francs 80
1 8 Math matiler 99
first_value()
first_value()函数用来取结果集每一个分组的第一行数的字段值。如：
select first_value(score) over(partition by subject ),* from score_demo
first_value id subject stu_name score
70 1 Chinese francs 70
70 2 Chinese matiler 70
70 3 Chinese tutu 80
75 4 English matiler 75
75 5 English francs 90
75 6 English tutu 60
80 7 Math francs 80
80 8 Math matiler 99
80 9 Math tutu 65
所以通过first_value()函数很容易查询到分组数据的最大值和最小值。例如查询成绩表按课程分组同时取每门课程的最高分，如：
select first_value(score) over(partition by subject order by score desc) max_score,* from score_demo
max_score id subject stu_name score
80 3 Chinese tutu 80
80 1 Chinese francs 70
80 2 Chinese matiler 70
90 5 English francs 90
90 4 English matiler 75
90 6 English tutu 60
99 8 Math matiler 99
99 7 Math francs 80
99 9 Math tutu 65
last_value()
last_value函数用来取结果集每一个分组的最后一行数据的字段值。 如：
select last_value(score) over(partition by subject ),* from score_demo
last_value id subject stu_name score
80 1 Chinese francs 70
80 2 Chinese matiler 70
80 3 Chinese tutu 80
60 4 English matiler 75
60 5 English francs 90
60 6 English tutu 60
65 7 Math francs 80
65 8 Math matiler 99
65 9 Math tutu 65
nth_value()
nth_value()函数用来取结果集每一个分组的指定行数据的字段值，语法：
nth_value(value any,nth integer)
1.value 指定表的字段
2.nth 指定结果集分组数据的第几行，如不存在则返回空..
例如成绩表按课程分组后去分组的第三行分数，如:
select nth_value(score,3) over(partition by subject),* from score_demo
nth_value id subject stu_name score
80 1 Chinese francs 70
80 2 Chinese matiler 70
80 3 Chinese tutu 80
60 4 English matiler 75
60 5 English francs 90
60 6 English tutu 60
65 7 Math francs 80
65 8 Math matiler 99
65 9 Math tutu 65
窗口函数别名的使用
如果sql中需要多次使用到窗口函数，可以使用窗口函数别名，语法如：
select .. from .. WINDOW window_name as (window_definiton)
WINDOW 属性指定表的别名为window_name,可以给over属性引用，如下:
select avg(score) over(r),sum(score) over(r) ,* from score_demo WINDOW r as (PARTITION by subject)
avg sum id subject stu_name score
73.3333333333333 220 1 Chinese francs 70
73.3333333333333 220 2 Chinese matiler 70
73.3333333333333 220 3 Chinese tutu 80
75 225 4 English matiler 75
75 225 5 English francs 90
75 225 6 English tutu 60
81.3333333333333 244 7 Math francs 80
81.3333333333333 244 8 Math matiler 99
81.3333333333333 244 9 Math tutu 65
好了终于写完常用的窗口函数了啊呜...要不打个赏?
