-- 获取每个学员按照成绩的排名
select
       t1.*,
       row_number() over (partition by student_name order by score desc) rn
from t_score t1;

-- 查询每个部门去除最高、最低薪水后的平均薪水
with t1 as (
    select t_salary_table.*,
           row_number() over (partition by department_id order by salary asc)  rn2,
           row_number() over (partition by department_id order by salary desc) rn1
    from t_salary_table)
select department_id, avg(salary)
from t1
where rn1 > 1
  and rn2 > 1
group by department_id;

-- 查询去除最高分、最低分后的平均分数
with t1 as (
    select t_score.*,
           row_number() over (order by score asc)  rn2,
           row_number() over (order by score desc) rn1
    from t_score)
select avg(score)
from t1
where rn1 > 1
  and rn2 > 1;

-- todo 题目要求: “成绩表”中记录了学生选修的课程号、学生的学号，以及对应课程的成绩。为了对学生成绩进行考核，现需要查询每门课程前三名学生的成绩。
-- todo 注意：如果出现同样的成绩，则视为同一个名次
with t2 as (
    select t1.*,
           dense_rank() over (partition by course_id order by score desc) rn
    from t_score t1)
select
       *
from t2
where t2.rn <= 3;

--  实现 查询课程前2
with t2 as (
    select t1.*,
           dense_rank() over (partition by department_id order by salary desc) rn
    from t_employee t1)
select
       *
from t2
where t2.rn <= 2;

-- 编写一个解决方案，在一个统一的表中计算出每个员工的 累计工资汇总 。
--
-- 员工的 累计工资汇总 可以计算如下:
--     对于该员工工作的每个月，将 该月 和 前两个月 的工资 加 起来。这是他们当月的 3 个月总工资和 。如果员工在前几个月没有为公司工作，那么他们在前几个月的有效工资为 0 。
--     不要 在摘要中包括员工 最近一个月 的 3 个月总工资和。
--     不要 包括雇员 没有工作 的任何一个月的 3 个月总工资和。
--     返回按 id 升序排序 的结果表。如果 id 相等，请按 month 降序排序。
select
       t1.*,
       sum(salary)
           over (partition by id order by month desc range between current row and 2 following) as salary
from t_employee t1
order by id asc, month desc;

-- todo “成绩表”，记录了每个学生各科的成绩。现在要查找单科成绩高于该科目平均成绩的学生名单。
select * from
(select t1.*, avg(score) over (partition by course_name) as avg_score
from t_score t1)
where score>avg_score;

-- todo 现在公司要找出每个部门低于平均薪水的雇员，然后进行培训来提高雇员工作效率，从而提高雇员薪水。
select * from
(select t1.*, avg(salary) over (partition by departmentID) avg_salary
from t_employee t1)
where salary<avg_salary;

-- todo 获取连续三次为球队得分的球员
with t1 as (
select
       t_score.*,
       lead(player_id,1) over (partition by team_name order by score_time) rn1,
       lead(player_id,2) over (partition by team_name order by score_time) rn2
from t_score)
select distinct player_id,player_name,team_name from t1 where t1.player_id=t1.rn1 and t1.rn1=t1.rn2;

-- todo 等差数列的方法
with t1 as (
    select ROWNUM id, t_score.*
    from t_score
),
     t2 as (
     select id,team_name,player_name,player_id,
        id - row_number() over (partition by team_name,player_name order by score_time) 差值
from t1),
t3 as (
select team_name,player_name,count(*) over (partition by team_name,player_name,差值) 计数 from t2)
select distinct t

-- todo 查找电影院所有连续可用的座位。
-- todo 返回按 seat_id 升序排序 的结果表。
-- todo 测试用例的生成使得两个以上的座位连续可用
-- todo 方式1 lad lead
with t1 as (
    select Cinema.*,
           lead(free, 1) over (order by seat_id) rn1,
           lag(free, 1) over (order by seat_id)  rn2
    from Cinema)
select seat_id
from t1
where (t1.free = 1 and t1.rn1 = 1)
   or (t1.rn1 is null and t1.rn2 = 1 and t1.free=1);

-- todo 方式2 等差数列
with t1 as(
select
       Cinema.*,
       row_number() over (partition by free order by seat_id) as rn1,
       seat_id - (row_number() over (partition by free order by seat_id)) as 差值
from Cinema),
t2 as (
select seat_id,count(差值) over (partition by 差值) as 计数 from t1 where free=1)
select seat_id from t2 where 计数>1 order by seat_id;

-- todo 需求: 找出所有至少连续出现三次的数字。
-- todo 返回的结果表中的数据可以按 任意顺序 排列。
-- todo 方式1 等差数列
with t1 as (
select
       Logs.*,
       id - (row_number() over (partition by num order by id)) 差值
from Logs),
     t2 as (
select num,count(1) over (partition by 差值,num) as 计数 from t1)
select distinct num from t2 where 计数>=3;

-- todo 方式2 lag lead
with t1 as (
select
       Logs.*,
       lag(num) over (order by id) lag1,
       lead(num) over (order by id) lead1
from Logs)
select distinct num from t1 where t1.num=lag1 and t1.num=lead1;