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

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;

SELECT e_i.emp_id, emp_level, tag FROM
    (SELECT er.emp_id, er.exam_id, er.score, e_a_s.a_s, e_a_s.a_t FROM exam_record er join
        (SELECT exam_id, avg(score) a_s, tag, avg(a_t) as a_t FROM
            (SELECT ei.emp_id, emi.exam_id, ei.emp_level, er.score, emi.tag, timestampdiff(second, start_time, submit_time)/60 a_t FROM
                    (SELECT emp_id, emp_level FROM emp_info)ei
                    join exam_record er on ei.emp_id = er.emp_id
                    join examination_info emi on er.exam_id = emi.exam_id) es
        GROUP BY tag, exam_id) e_a_s
    on er.exam_id = e_a_s.exam_id
    WHERE timestampdiff(second, start_time, submit_time)/60 < e_a_s.a_t AND score > e_a_s.a_s AND er.emp_id in (SELECT emp_id FROM emp_info WHERE emp_level < 7))e_i
join emp_info ei on e_i.emp_id = ei.emp_id
join examination_info emi on e_i.exam_id = emi.exam_id
ORDER BY e_i.emp_id asc, e_i.exam_id asc

with aa as (select t2.tag
,t2.exam_id
,avg((unix_timestamp(t1.submit_time)-unix_timestamp(t1.start_time))) avg_time
,avg(t1.score) avg_score
from exam_record t1 
left join examination_info t2 
on t1.exam_id=t2.exam_id
group by t2.tag,t2.exam_id

)

select t1.emp_id
,t3.emp_level
,t2.tag exam_tag
from exam_record t1 
left join examination_info t2 
on t1.exam_id=t2.exam_id
left join emp_info t3 
on t1.emp_id=t3.emp_id
left join aa t4 
on t4.exam_id=t1.exam_id
where t1.score>t4.avg_score
and (unix_timestamp(t1.submit_time)-unix_timestamp(t1.start_time))<t4.avg_time
and t1.emp_id not in (
    select distinct emp_id
    from emp_info 
    where emp_level>=7
)
order by t1.emp_id,t1.exam_id

SELECT DISTINCT
    er.emp_id,
    emp.emp_level,
    ei.tag
FROM
    exam_record er
    INNER JOIN emp_info emp ON er.emp_id = emp.emp_id
    INNER JOIN examination_info ei ON er.exam_id = ei.exam_id
    INNER JOIN (
        SELECT
            exam_id,
            AVG(score) as avg_score,
            AVG(TIMESTAMPDIFF(SECOND, start_time, submit_time)) as avg_seconds
        FROM
            exam_record
        GROUP BY
            exam_id
    ) t ON er.exam_id = t.exam_id
WHERE
    emp.emp_level < 7
    AND er.score > t.avg_score
    AND TIMESTAMPDIFF(SECOND, er.start_time, er.submit_time) < t.avg_seconds

# 1.计算试卷平均时间和平均分数
with a as
(select exam_id,avg(timestampdiff(second,start_time,submit_time)) as avg_time,
avg(score) as avg_score
from exam_record
group by exam_id)
# 2.计算目标人群
select e1.emp_id,emp_level,tag as exam_tag
from a join exam_record e1 on a.exam_id = e1.exam_id join emp_info e2 on e1.emp_id = e2.emp_id join examination_info e3 on e1.exam_id = e3.exam_id
where score > avg_score and timestampdiff(second,start_time,submit_time) < avg_time and emp_level<7
order by e1.emp_id,e1.exam_id

刚开始以为可以直接用表连接和子查询做出来 结果整半天都没想起用窗口函数 还是看了ai给得提示才回想起用窗口函数 

select emp_id
,emp_level
,tag
from 
    (
        select b1.emp_id as emp_id
        ,tag 
        ,emp_level
        ,score 得分
        ,avg(score)over(partition by tag) 平均得分
        ,TIMESTAMPDIFF(MINUTE,start_time,submit_time) 答题时长
        ,avg(TIMESTAMPDIFF(MINUTE,start_time,submit_time))over(partition by tag) 平均时长
        from emp_info b1
        join exam_record b2
        on b1.emp_id = b2.emp_id
        and emp_level < 7
        join examination_info b3
        on b2.exam_id = b3.exam_id
    ) a 
where 得分 > 平均得分 and 答题时长 < 平均时长
order by emp_id

# 每条考试记录的该考试平均分和平均作答时间；领导不算
WITH t AS (
    SELECT *, AVG(score) OVER (PARTITION BY exam_id) AS avg_score,
        AVG(TIMESTAMPDIFF(SECOND, start_time, submit_time)) OVER (PARTITION BY exam_id) AS avg_t
    FROM exam_record JOIN emp_info USING (emp_id)
    JOIN examination_info USING (exam_id)
    WHERE emp_level < 7
)
SELECT emp_id, emp_level, tag AS exam_tag
FROM t
WHERE score > avg_score AND TIMESTAMPDIFF(SECOND, start_time, submit_time) < avg_t
ORDER BY 1