题解 | 完成员工考核试卷突出的非领导员工

-- 这是用窗口函数的解法
with
    t as (
        select
            r.emp_id,
            emp_level,
            r.exam_id,
            tag,
            start_time,
            submit_time,
            score,
            avg(score) over (
                partition by
                    r.exam_id
            ) as avg_score,
            avg(timestampdiff (second, start_time, submit_time)) over (
                partition by
                    r.exam_id
            ) as avg_time
        from
            exam_record r
            join emp_info p on r.emp_id = p.emp_id
            join examination_info m on r.exam_id = m.exam_id
    )
select
    emp_id,
    emp_level,
    tag as exam_tag
from
    t
where
    emp_level < 7
    and score > avg_score
    and timestampdiff (second, start_time, submit_time) < avg_time
order by
    emp_id asc,
    t.exam_id asc

-- 这是不用窗口函数的解法，供参考
/*
with
    t as (
        select
            r.emp_id,
            emp_level,
            r.exam_id,
            tag,
            start_time,
            submit_time,
            score
        from
            exam_record r
            join emp_info p on r.emp_id = p.emp_id
            join examination_info m on r.exam_id = m.exam_id
    ),
    avg_tb as (
        select
            exam_id,
            avg(score) as avg_score,
            avg(timestampdiff (second, start_time, submit_time)) as avg_time
        from
            t
        group by
            exam_id
    )
select
    emp_id,
    emp_level,
    tag as exam_tag
from
    t
    join avg_tb on t.exam_id = avg_tb.exam_id
where
    emp_level < 7
    and score > avg_score
    and timestampdiff (second, start_time, submit_time) < avg_time
order by
    emp_id asc,
    t.exam_id asc
*/