SQL --窗口函数
‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)
聚合函数的窗口函数中,加不加order by,order by的列名是否是用于分组的列名,这些情况都会影响到最终的结果,下面我们分别来讨论各种不同的情况。
count(grade)over(partition by subject,class order by number) total_classmate,
max(grade)over(partition by subject,class order by number) max_grade,
min(grade)over(partition by subject,class order by number) min_grade
from test_long_temp_table
按照 subject,class进行分类(group),顺序是按照number大小排序的。
有order by且order by的字段不是用于分组的字段
这种情况下得到的结果是每个partition的累加的结果
select *,sum(grade)over(partition by class,subject order by number) total_grade,
avg(grade)over(partition by subject,class order by number) avg_grade,
有order by且order by的字段是用于分组的字段
该情况下得到的数据是每个partition的总和而不是累加
select *,sum(grade)over(partition by class,subject order by class) total_grade,
avg(grade)over(partition by subject,class order by class) avg_grade,
有partition by无order by
该情况下,sum()over()得到的数据是每个partition的总和而不是累加,和第二种情况是一致的。
select sum(grade)over(partition by subject,class) total_grade,
select avg(grade)over(partition by subject,class) avg_grade,
select *, avg(成绩) over (order by 学号 rows 2 preceding) as current_avg from 班级表;
rows和preceding这两个关键字,是“之前~行”的意思,上面的句子中,是之前2行。也就是得到的结果是自身记录及前2行的平均(相对应的preceding是following)