窗口函数

前言

见这种近几、连续、每类前几、各个前几直接考虑窗口函数，这里说下常用的几个：

窗口函数语法都是一样的：

<窗口函数> OVER (partition by <用于分组的列名> order by <用于排序的列名>)

序号函数：row_number、rank、dense_rank

例如：对100，99，99，85，84

row_number的进行排序结果是：1、 2、 3、 4 、5

rank的排序结果是：1、2、2、4、5

dense_rank的排序结果是：1、2、2、3、4

ROW_NUMBER()函数可以理解为排序号，不考虑并列；

RANK()函数也为排号，考虑并列，并列之后的按照实际序号来；

dense_rank()同样是排号，考虑并列，并列之后按下一个名次来。

1）窗口函数：有三种排序方式

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 (不跳过排名，可以理解为对类别进行计数)

2）求时间差函数

timestampdiff(时间格式，开始时间，结束时间)

题目

例一

找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。由示例数据结果输出如下：

tid	uid	ranking
SQL	1003	1
SQL	1004	2
SQL	1002	3
算法	1005	1
算法	1006	2
算法	1003	3

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）

exam_id

tag

difficulty

duration

release_time

试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

uid

exam_id

start_time

submit_time

score

知识点：where不可以引用select里面as 的变量，如果想用需要重新select

select
    tag,
    uid,
    ranking
from(
    SELECT
    a.tag,
    b.uid,
    ROW_NUMBER() OVER (
        PARTITION BY
            tag
        ORDER BY
            max(b.score) DESC,
            min(b.score) DESC,
            b.uid DESC
    ) ranking
FROM
    examination_info a
    LEFT JOIN exam_record b ON a.exam_id = b.exam_id
GROUP BY
    a.tag,
    b.uid
)t1
where ranking<=3

例二

现有试卷作答记录表exam_record（uid:用户ID, exam_id:试卷ID, start_time:开始作答时间, submit_time:交卷时间，为空的话则代表未完成, score:得分）：

id	uid	exam_id	start_time	submit_time	score
1	1006	9003	2021-09-06 10:01:01	2021-09-06 10:21:02	84
2	1006	9001	2021-08-02 12:11:01	2021-08-02 12:31:01	89
3	1006	9002	2021-06-06 10:01:01	2021-06-06 10:21:01	81
4	1006	9002	2021-05-06 10:01:01	2021-05-06 10:21:01	81
5	1006	9001	2021-05-01 12:01:01	(NULL)	(NULL)
6	1001	9001	2021-09-05 10:31:01	2021-09-05 10:51:01	81
7	1001	9003	2021-08-01 09:01:01	2021-08-01 09:51:11	78
8	1001	9002	2021-07-01 09:01:01	2021-07-01 09:31:00	81
9	1001	9002	2021-07-01 12:01:01	2021-07-01 12:31:01	81
10	1001	9002	2021-07-01 12:01:01	(NULL)	(NULL)

找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名。由示例数据结果输出如下：

剔除在3个月中有未完成状态试卷的用户，即作答数=完成数

uid	exam_complete_cnt
1006	3

解释：用户1006近三个有作答试卷的月份为202109、202108、202106，作答试卷数为3，全部完成；用户1001近三个有作答试卷的月份为202109、202108、202107，作答试卷数为5，完成试卷数为4，因为有未完成试卷，故过滤掉。

SELECT uid,(①) 'exam_complete_cnt' --题设要求输出
FROM(②) A --FROM()内填充排序函数
WHERE(③) --用于筛选近三个月
GROUP BY uid --对每位用户
HAVING(④) --用于过滤近三个月有未完成试卷的用户
ORDER BY exam_complete_cnt DESC,uid DESC;
 
select uid,count(1)
count(1)会查询（去重后）所有uid
select uid,count(score)
count(score)会查询分数不为空或者0的uid