【SQL进阶】Day05：窗口函数

最新推荐文章于 2025-05-22 09:04:46 发布

原创

最新推荐文章于 2025-05-22 09:04:46 发布 · 458 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #java #开发语言

本文探讨了如何运用SQL查询技术，包括专用窗口函数，来分析考试成绩排名、答题速度差异、连续作答时间窗口、未完成试卷统计和增长率等，帮助理解用户的学习行为和完成情况。

〇、概述

一、专用窗口函数

1、每类试卷得分前3名

自己写出来的部分

SELECT
    tag AS tid,
    uid AS uid,
    Rank AS ranking -- 如何确定排名
FROM examination_info ei
JOIN exam_record er
USING(exam_id)
GROUP BY tid
ORDER BY MIN(score) DESC,uid ASC

答案：

select u.tag tid,u.uid,u.ranking FROM
    (SELECT *,
        row_number() over (partition by t.tag order by t.max_score desc,t.min_score desc,t.uid desc) ranking 
    FROM
        (SELECT i.tag,r.uid,max(r.score) max_score,min(r.score) min_score
        from examination_info i
        join exam_record r
        on i.exam_id=r.exam_id
        where r.score is not null
        group by i.tag,r.uid
        ) t
    ) u
WHERE u.ranking<=3

或

SELECT tag,uid,ranking
FROM(
    SELECT
        tag,
        uid,
        row_number() OVER (PARTITION BY tag 
                           ORDER BY tag, MAX(score) DESC, MIN(score) DESC, uid DESC)
                     AS ranking
    FROM exam_record
    JOIN examination_info USING(exam_id)
    GROUP BY tag,uid
    ) new_examrecord
WHERE ranking < 4

学到：ROW_NUMBER() OVER( PATITION BY A ORDER BYB)