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

with a1 as(
select emp_id,exam_id,timestampdiff(minute,start_time,submit_time)/60 as time,
avg(timestampdiff(minute,start_time,submit_time)/60) over(partition by exam_id) as avg_time,
score,
avg(score) over(partition by emp_id) as pavg_score,
avg(score) over(partition by exam_id) as avg_score
from exam_record
order by exam_id,emp_id)

select a1.emp_id,emp_level,tag as exam_tag
from a1
left join examination_info ex on ex.exam_id = a1.exam_id
left join emp_info ei on a1.emp_id=ei.emp_id
where time < avg_time and score > avg_score and emp_level < 7
order by a1.emp_id,a1.exam_id

select t1.emp_id,
        emp_level,
       tag as exam_tag
from (select emp_id,
             exam_id,
             score,
             submit_time,
             start_time,
             AVG(score) over (partition by exam_id) as avg,
             AVG(timestampdiff(second,start_time,submit_time)) over (partition by exam_id) as avg_time
      from exam_record er) t1
left join examination_info ei on t1.exam_id=ei.exam_id
left join emp_info empi on empi.emp_id=t1.emp_id
where score > avg and timestampdiff(second,start_time,submit_time) < avg_time and emp_level < 7
order by emp_id,ei.exam_id

with a as (
    select exam_id,avg(submit_time-start_time) at,avg(score) ass
    from exam_record 
    group by exam_id
)
select e.emp_id,ei.emp_level,ex.tag exam_tag
from exam_record  e 
join emp_info  ei 
on e.emp_id=ei.emp_id
join examination_info ex
on e.exam_id=ex.exam_id
join a 
on a.exam_id=e.exam_id
where e.score>a.ass and e.submit_time-e.start_time<at and ei.emp_level<7;

#构建临时表t，求2种类型考试的平均考试时间和平均分数分别是多少

with t as (
    select r.exam_id,
           e.tag,
           avg(unix_timestamp(r.submit_time)-unix_timestamp(r.start_time)) as avg_time,
       avg(r.score) as avg_score
       
from exam_record as r
left join examination_info as e
on r.exam_id=e.exam_id

group by r.exam_id, e.tag ),

#构建临时表s.求员工的id，考试id，考试分数,考试时间,最后仅保留 emp_level 小于7的
q as (
    select r.emp_id,
    r.exam_id,
    ei.emp_level,
    (unix_timestamp(r.submit_time)-unix_timestamp(r.start_time)) as exam_time,
     score

from exam_record as r
left join emp_info as ei
on r.emp_id=ei.emp_id

where ei.emp_level<7
)

#主查询，仅保留考试用时小于平均用时，分数大于平均分的
select q.emp_id,
       q.emp_level,
       t.tag as exam_tag
from q
left join t
on q.exam_id=t.exam_id

where q.exam_time< t.avg_time and q.score>t.avg_score

with
    k as (
        select
            exam_id,
            avg(score) s,
            round(
                avg(
                    timestampdiff(second, start_time, submit_time) / 60
                ),
                1
            ) mm
        from
            exam_record
        group by
            exam_id
    )
select
    c.emp_id,
    emp_level,
    tag exam_tag
from
    exam_record c
    left join emp_info d on d.emp_id = c.emp_id
    left join examination_info e on e.exam_id = c.exam_id
    left join k on k.exam_id = c.exam_id
where
    emp_level < 7
    and timestampdiff(second, start_time, submit_time) / 60 < mm
    and score > s

with exam_time as
(
    select emp_id,exam_id,
    timestampdiff(second,start_time,submit_time) as exam_time,score
    from exam_record 
)
,t2 as(
    SELECT *
FROM (
    SELECT *,
        AVG(exam_time) OVER (PARTITION BY exam_id) AS avg_time,
        AVG(score) OVER (PARTITION BY exam_id) AS avg_score
    FROM exam_time
) t
WHERE exam_time < avg_time
  AND score > avg_score
)
select t1.emp_id,t1.emp_level,t3.tag as exam_tag
from emp_info t1
join t2 on t1.emp_id=t2.emp_id 
join examination_info t3 on t2.exam_id=t3.exam_id
where t1.emp_level<7
order by t1.emp_id,t2.exam_id

select
    emp_id
    , emp_level
    , exam_tag
from
    (select
        er.emp_id
        , empi.emp_level
        , exami.exam_id
        , exami.tag as exam_tag
        , er.score
        , t1.avg_score
        , round(
            timestampdiff(
                second, start_time, submit_time)/60, 3) as time_diff
        , avg_time_diff
    from
        exam_record as er
        left join 
        (select
            exam_id
            , avg(score) as avg_score
        from
            exam_record
        group by
            exam_id
        ) as t1 on er.exam_id = t1.exam_id
        left join
        (select
            exam_id
            , avg(
                round(
                    timestampdiff(second, start_time, submit_time)/60, 3)) as avg_time_diff
        from
            exam_record
        group by
            exam_id
        ) as t2 on er.exam_id = t2.exam_id
        left join emp_info as empi on er.emp_id = empi.emp_id
        left join examination_info as exami on er.exam_id = exami.exam_id
    where
        empi.emp_level < 7
        and er.score > t1.avg_score
    ) as tt1
where
    time_diff < avg_time_diff
order by
    emp_id
    , exam_id

WITH 
  temp1 AS(
    SELECT er.emp_id, emp_level, er.exam_id, tag, score,
        TIMESTAMPDIFF(SECOND, start_time, submit_time) AS time
    FROM exam_record er
    JOIN examination_info USING (exam_id)
    JOIN emp_info USING (emp_id)
  ),
  temp2 AS(
    SELECT *,
        AVG(score) OVER (PARTITION BY exam_id) AS avg_score,
        AVG(time) OVER (PARTITION BY exam_id) AS avg_time
    FROM temp1
  )

SELECT emp_id, emp_level, tag AS exam_tag
FROM temp2
WHERE time < avg_time AND score > avg_score AND emp_level < 7
ORDER BY emp_id, exam_id