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

# 第二快 和 第二慢 用时之差大于试卷时长的一半的试卷信息

# 字段：exam_id、duration、release_time

# 表一：链接
with table1 as (
    select exam_id,duration,release_time,
    timestampdiff(minute,start_time,submit_time) as finish_time
    from exam_record left join examination_info using(exam_id)
    # where submit_time is not null
),
# 表二：排序 最快(升序) 最慢(降序)
table2 as(
    select exam_id,duration,release_time,finish_time,
    row_number()over(partition by exam_id order by finish_time asc) as asc_rank,
    row_number()over(partition by exam_id order by finish_time desc) as desc_rank
    from table1
)
# 筛选 desc_rank=2 - asc_rank=2 > 0.5*duration
# 聚合函数 和 case when  结合使用
select exam_id,duration,release_time
from table2
group by exam_id,duration,release_time
having sum(case when desc_rank = 2 then finish_time when asc_rank = 2 then -finish_time else 0 end) > 0.5*duration
order by exam_id desc

# 注意点： 不能筛选排除submit_time为空的数据