#注释是不使用窗口函数，直接使用group by和inner join完成
# select
#     emp_record.emp_id,
#     emp_record.emp_level,
#     tag
# from 
#     (
#         select 
#             emp_level,
#             emi.emp_id,
#             exam_id,
#             timestampdiff(second, start_time, submit_time) as cost_time,
#             score
#         from emp_info as emi
#         inner join exam_record er on emi.emp_id=er.emp_id and emp_level < 7
#     ) as emp_record
#     inner join 
#     (
#         select 
#             exam_id,
#             avg(timestampdiff(second, start_time, submit_time)) as avg_cost_time,
#             avg(score) as avg_score
#         from  exam_record
#         group by
#             exam_id
#     ) as avg_tb on emp_record.exam_id=avg_tb.exam_id and cost_time < avg_cost_time and score > avg_score
#     inner join examination_info on emp_record.exam_id=examination_info.exam_id 
# order by
#     emp_record.emp_id, emp_record.exam_id


/*
使用窗口函数
需要注意的是，对于SQL的查询过程是
  1.FROM / JOIN：确定数据来源，加载表并关联。
  2.WHERE：过滤原始数据（基于行级条件）。
  3.GROUP BY：对数据分组。
  4.HAVING：过滤分组后的结果（基于聚合条件）。
  5.窗口函数（如 ROW_NUMBER()、AVG() OVER(...)）：在分组 / 过滤后的数据上计算窗口结果。
  6.SELECT：提取最终需要的列（包括窗口函数的别名）。
  7.ORDER BY：对结果排序（可以使用窗口函数别名）。
 因此不能对于窗口函数查询的结果直接having cost_time < avg_cost_time and score < avg_score,需要使用子查询（外面直接嵌套一个select * from(...)as就行）或者CTE(Common Table Expression) ,CTE类似临时表，使用with as创建，之后在整个SQL都能访问。
*/
select
    emi.emp_id,
    emi.emp_level,
    tag
from 
    emp_info as emi
    inner join 
        (
            with avg_tb as(
                select
                    emp_id, 
                    exam_id,
                    timestampdiff(second, start_time, submit_time) as cost_time,
                    avg(timestampdiff(second, start_time, submit_time)) over(partition by exam_id) as avg_cost_time,
                    score, 
                    avg(score) over(partition by exam_id) as avg_score
                from exam_record
            ) 
            select * 
            from avg_tb
            where score > avg_score and cost_time < avg_cost_time 
        ) as great_emp on emi.emp_id=great_emp.emp_id and emp_level < 7
    inner join examination_info on examination_info.exam_id=great_emp.exam_id
order by
    great_emp.emp_id, great_emp.exam_id