sql

窗口函数

有三种排序方式
rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

group by的常见搭配：常和以下聚合函数搭配

avg（）-- 求平均值
count（）-- 计数
sum（）-- 求和
max（） – 最大值
min（）-- 最小值

例题

现有试卷信息表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, uid, ranking
FROM (
    SELECT ei.tag, er.uid, 
    ROW_NUMBER()OVER(PARTITION BY tag ORDER BY max(er.score) DESC, min(er.score) DESC, er.uid DESC) ranking
    FROM exam_record er
    JOIN examination_info ei
    ON er.exam_id = ei.exam_id
    GROUP BY ei.tag, er.uid) t
WHERE ranking <= 3;

或者

WITH t AS (
    SELECT ei.tag, er.uid, 
    ROW_NUMBER()OVER(PARTITION BY tag ORDER BY max(er.score) DESC, min(er.score) DESC, er.uid DESC) ranking
    FROM exam_record er
    JOIN examination_info ei
    ON er.exam_id = ei.exam_id
    GROUP BY ei.tag, er.uid
)

SELECT tag, uid, ranking
FROM t
WHERE ranking <= 3;

note-sql

sql

窗口函数

例题