题解 137 | #第二快/慢用时之差大于试卷时长一半的试#

【场景】：与某行数据的差

【分类】：专用窗口函数、嵌套子查询

分析思路

难点：

1.查询第二慢和第二快的（一个正序排名第二，一个倒序第二），nth_value(expr,n)可以取expr列第n行的数两者求差，另一种方法是排序之后向下平移一位再做差。

2.换算成秒来计算作答时间

（1）统计试卷每次作答的时间（秒）

​ [条件]：submit_time is not null

​ [使用]：timestampdiff(second,start_time,submit_time)

（2）查询第二慢和第二快的用时记录（一个正序排名第二，一个倒序第二）

​ [使用]：nth_value(second,2) over(partition by exam_id order by second)；nth_value(second,2) over(partition by exam_id order by second desc)

（3）统计第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序

​ [条件]：where 第二快 - 第二慢 - duration*60/2 > 0（换算成秒）

最终结果

select 查询结果 [试卷类型; 用户ID;排名]
from 从哪张表中查询数据[嵌套from子查询]
where 查询条件 [每类试卷得分的前3名]

扩展

前往查看： MySQL 窗口函数

求解代码

方法一

with子句 + 使用nth_value()

with 
    temp as(
        #统计试卷每次作答的时间（秒）
        select
            a.exam_id,
            duration,
            timestampdiff(second,start_time,submit_time) as second
        from examination_info a,exam_record b
        where a.exam_id=b.exam_id
        and submit_time is not null
    ),
    temp1 as(
        #查询第二慢和第二快的用时记录（一个正序排名第二，一个倒序第二）
        select 
            exam_id,
            duration*60/2 as duration,
            second,
            nth_value(second,2) over(partition by exam_id order by second) as row_asc,
            nth_value(second,2) over(partition by exam_id order by second desc) as row_desc
        from temp
    )

#统计第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序
select distinct
    exam_id,
    duration,
    release_time
from examination_info
join temp1 using(exam_id)
where row_desc-row_asc - temp1.duration > 0
order by exam_id desc

方法二

with子句 + 使用row_number()和lag(exrp,number)

with 
    temp1 as(
        #统计试卷每次作答的时间（秒）
        select
            a.exam_id,
            a.duration,
            timestampdiff(second,start_time,submit_time) as second
        from examination_info a,exam_record b
        where a.exam_id=b.exam_id
        and submit_time is not null
    ),
    temp2 as(
        #统计每个试卷每次作答时间正序和倒序
        select
            exam_id,
            duration,
            second,
            row_number() over(partition by exam_id order by second) as row_asc,
            row_number() over(partition by exam_id order by second desc) as row_desc
        from temp1
    ),
    temp3 as(
        #正序=2为第二慢，倒序=2为第二快
        select 
            exam_id,
            duration,
            second
        from temp2
        where (row_asc = 2 or row_desc = 2)
    ),
    temp4 as (
        #按照试卷类型分组后再按照作答时间向下平移一位得到pre_second
        select 
            exam_id,
            duration*60/2 as duration,
            second,
            lag(second,1) over (partition by exam_id order by second desc) as pre_second
        from temp3
    )
#统计第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序
select
    a.exam_id,
    a.duration,
    release_time
from examination_info a
join temp4
using(exam_id)
where pre_second-second - temp4.duration > 0
order by exam_id desc

方法三

嵌套子查询 + join子查询 + nth_value()

#统计第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序
select distinct
    exam_id,
    a.duration,
    release_time
from examination_info a
join(
    #查询第二慢和第二快的用时记录（一个正序排名第二，一个倒序第二）
    select 
        exam_id,
        duration*60/2 as duration,
        second,
        nth_value(second,2) over(partition by exam_id order by second) as row_asc,
        nth_value(second,2) over(partition by exam_id order by second desc) as row_desc
    from(
        #统计试卷每次作答的时间（秒）
        select
            a.exam_id,
            duration,
            timestampdiff(second,start_time,submit_time) as second
        from examination_info a,exam_record b
        where a.exam_id=b.exam_id
        and submit_time is not null
    ) temp
) temp1 using(exam_id)
where row_desc-row_asc - temp1.duration > 0
order by exam_id desc