窗口函数的使用1

窗口函数的基本格式为：

<窗口函数> OVER (PARTITION BY <用于分组的列名>
                ORDER BY <用于排序的列名>)

其中的<窗口函数>包括两类：
1.专用窗口函数
有三种排序方式：
rank() over() 1 2 2 4 4 6  (计数排名，跳过相同的排名)
row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

2.聚合函数
如sum,avg, count, max, min等。

注意，由于窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

---

下面举例：
1．对于专用窗口函数的例子：
以NOWCODER SQL136为例，是很典型的专用窗口函数的应用。
现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）；
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）；
要求：找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。输出排名为１２３.

SELECT tag AS tid,uid,ranking
FROM (
    SELECT b.tag,a.uid,
    ROW_NUMBER()OVER(PARTITION BY tag ORDER BY max(a.score) DESC,min(a.score) DESC,a.uid DESC) ranking　--先按照最高分，再按照最低分排，最后按照uid排序
    FROM exam_record a 
    LEFT JOIN examination_info b ON a.exam_id=b.exam_id
    GROUP BY b.tag,a.uid)t1
     
WHERE ranking<=3

２.对于聚合函数作为窗口函数的例子：
现有班级成绩表class_info（class班级,stu_id学号,score成绩）

select *,
   sum(score) over (order by stu_id) as current_sum,
   avg(score) over (order by stu_id) as current_avg,
   count(score) over (order by stu_id) as current_count,
   max(score) over (order by stu_id) as current_max,
   min(score) over (order by stu_id) as current_min
from class_info

以sum为例，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。
对于4号，在使用sum窗口函数后的结果，是对1，2，3，4号的成绩求和，若是5号，则结果是1号－5号成绩的求和，以此类推。
同样的，其他聚合函数也是如此。聚合函数作为窗口函数的好处是：可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。
参考：
https://blog.nowcoder.net/n/7ff4b4b0f0c04f0c82b8699a547ef9bb?f=comment
https://www.zhihu.com/tardis/zm/art/92654574?source_id=1005