【Kay】SQL窗口函数题目

Base:找到每个学校gpa最低的同学

 

SELECT device_id,university,gpa
FROM (
    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY university ORDER BY gpa) AS rk
    FROM user_profile
    ) Must
WHERE rk = 1
ORDER BY university

SELECT device_id,university,gpa
FROM user_profile 
WHERE(university,gpa) IN (
    SELECT university,MIN(gpa)
    FROM user_profile
    GROUP BY university
    )
ORDER BY university

--字段1，MIN(gpa)

GROUP BY 字段1     → 是可以的

--字段1，字段2，字段3，MIN(gpa)

GROUP BY 字段1     → 字段2，字段3只取第一个值

一、每类试卷得分前3名

现有试卷信息表examination_info

试卷作答记录表exam_record

请找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者

实例结果：

 代码如下：

SELECT te.tid,uid,ranking FROM(
    
SELECT tag AS tid,
        uid,
        ROW_NUMBER() OVER(PARTITION BY tag 
                          ORDER BY MAX(score) DESC,
                          MIN(score)DESC,
                          uid DESC) AS ranking
FROM exam_record r
INNER JOIN examination_info i
ON r.exam_id=i.exam_id
GROUP BY tag,uid
) te
 
WHERE ranking <= 3

二、第二快/慢用时之差大于试卷时长一半的试卷

现有试卷信息表examination_info

试卷作答记录表exam_record 

找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序。由示例数据结果输出如下：

 代码如下：

SELECT DISTINCT te.exam_id ,duration,release_time
FROM

(SELECT r.exam_id,
        TIMESTAMPDIFF(MINUTE,start_time,submit_time) AS td,
        NTH_VALUE(TIMESTAMPDIFF(MINUTE,start_time,submit_time),2) OVER(PARTITION BY r.exam_id ORDER BY TIMESTAMPDIFF(MINUTE,start_time,submit_time)) FAST_2,
        NTH_VALUE(TIMESTAMPDIFF(MINUTE,start_time,submit_time),2) OVER(PARTITION BY r.exam_id ORDER BY TIMESTAMPDIFF(MINUTE,start_time,submit_time) DESC) SLOW_2,
        duration,
        release_time
FROM exam_record r
INNER JOIN examination_info i
ON r.exam_id = i.exam_id) te

WHERE SLOW_2 - FAST_2 > duration * 0.5
ORDER BY exam_id DESC

Bonus:寻找工资第N高的员工:窗口函数NTH_VALUE()

+--------+--------+------+----------------+
| DEPTNO | ENAME  | SAL  | THIRD_MOST_SAL |
+--------+--------+------+----------------+
|     10 | KING   | 5300 |           1600 |
|     10 | CLARK  | 2750 |           1600 |
|     10 | MILLER | 1600 |           1600 |
|     20 | FORD   | 3300 |           3275 |
|     20 | SCOTT  | 3300 |           3275 |
|     20 | JONES  | 3275 |           3275 |
|     20 | ADAMS  | 1400 |           3275 |
|     20 | SMITH  | 1100 |           3275 |
|     30 | BLAKE  | 3150 |           1800 |
|     30 | ALLEN  | 1900 |           1800 |
|     30 | TURNER | 1800 |           1800 |
|     30 | MARTIN | 1550 |           1800 |
|     30 | WARD   | 1550 |           1800 |
|     30 | JAMES  | 1250 |           1800 |
+--------+--------+------+----------------+

找出每个部门工资第3高的员工：

SELECT  deptno
       ,ename
	   ,sal
	   ,NTH_VALUE(sal, 3)OVER (PARTITION BY deptno ORDER BY sal DESC )  AS third_most_sal 
FROM emp_msg

三、连续两次作答试卷的最大时间窗

现有试卷作答记录表exam_record

请计算：在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。由示例数据结果输出如下：

代码如下：

SELECT uid, days_window, round(days_window*exam_cnt/diff_days, 2) as avg_exam_cnt
FROM (
    SELECT uid,
        count(start_time) as exam_cnt,  -- 此人作答的总试卷数
        DATEDIFF(max(start_time), min(start_time))+1 as diff_days, -- 最早一次作答和最晚一次作答的相差天数
        max(DATEDIFF(next_start_time, start_time))+1 as days_window -- 两次作答的最大时间窗
    FROM (
        SELECT uid, exam_id, start_time,
            lead(start_time,1) over(partition by uid ORDER BY start_time) as next_start_time -- 将连续的下次作答时间拼上
        FROM exam_record
        WHERE year(start_time)=2021
    ) as t_exam_record_lead
    GROUP BY uid
) as t_exam_record_stat
WHERE diff_days>1
ORDER BY days_window DESC, avg_exam_cnt DESC

lead窗口函数的用法

• lead(字段名，n) over () :向前移动n位数字;

• lag(字段名，n) over () :向后移动n位数字;

SELECT id,score,Lead(score,2) over(order by id) lead_score,-- *向前移动2位，腾空2个位置
      Lag(score,2) over(order by id) lag_score, -- 向后移动2位，腾空2个位置
      lag(score,2,666) over(order by id) lag_score_3 -- 向后移动2位，空值被填充为666
FROM exam_record;