【SQL篇】窗口函数和公共表达式

1077 项目员工 III

# Write your MySQL query statement below
select project_id, employee_id
from (
    select project_id, e.employee_id, rank() over(partition by project_id order by experience_years desc) as rk
    from Employee e
    join Project p
    on e.employee_id = p.employee_id
) tmp
where tmp.rk = 1;

1285 找到连续区间的开始和结束数字

# Write your MySQL query statement below
select min(log_id) as start_id, max(log_id) as end_id
from (
    select log_id, log_id - rank() over(order by log_id) as num
    from Logs
) t
group by num

思路

1. 先给每个数进行排名
2. 用这些数减去自己的排名，如果减了之后的结果是一样的，说明这几个数是连续的
3. 用logid减去排名得出来的数进行group by，也就是把连续的数全都放在一个一个小组里面，求出每个小组的最大值和最小值就可以了

1596 每位顾客最经常订购的商品

• 1596题

select t.customer_id, t.product_id, p.product_name
from (
    select customer_id, product_id,
    rank() over(partition by customer_id order by count(product_id) desc) as rk
    from Orders
    group by customer_id, product_id
) t
join Products p
on t.product_id = p.product_id
where rk = 1

总结

• 这里有个坑，count并不会根据partition来分组，所以必须根据group by 来分组计算出count的数量；其次group by在窗口函数之前执行。

1709 访问日期之间最大的空档期

select user_id, max(datediff(next_visit, visit_date)) as biggest_window
from (
    select  user_id, 
            visit_date, 
            lead(visit_date, 1, "2021-01-01") over(partition by user_id 
            order by visit_date) as next_visit
    from UserVisits
) tmp
group by user_id
order by user_id;

总结

• 注意lead(x,y,z) over()的参数，x代表要寻找的列，y代表往后走几个位置，z代表当x+y找不到记录时的默认值。

  此题中lead(visit_date,1,‘2021-01-01’)就说明要寻找的是visit_date列，1代表visit_date往后走1条记录，‘2021-01-01’代表往后走找不到记录时，默认为’2021-01-01’。这里的前后是指用order by排序过后的前后。

1270 向公司CEO汇报工作的所有人

# 写法1
select employee_id from Employees
where manager_id in(
    select employee_id from Employees
    where manager_id in
    (select employee_id from Employees where manager_id=1) 
) and employee_id <> 1;

# 写法2
select distinct e1.employee_id
from Employees e1,Employees e2, Employees e3
where e1.manager_id=e2.employee_id and e2.manager_id=e3.employee_id
and e3.manager_id=1 and e1.employee_id !=1

总结

• 思路1：嵌套查询
• 思路2：三表联合，该方法只有在老板的employee_id=manager_id时才生效。
• 对于思路2，一开始疑惑的点为什么不是四表查询，一开始认为employee_id=7这个员工无法查询得到，但如果画出表来我们可以发现。表三中employee_id=2,manager_id=1的记录可以被查询出来，如下：

 ["employee_id", "employee_name", "manager_id", #表1
 "employee_id", "employee_name", "manager_id", #表2
 "employee_id", "employee_name", "manager_id"] #表3
  [7, "Luis", 4, 
  4, "Daniel", 2, 
  2, "Bob", 1]

1412 查找成绩处于中游的学生

• 1412题

# 写法1
select distinct e.student_id, student_name
from Exam e
left join Student s
on e.student_id = s.student_id
where e.student_id not in (
    select student_id from Exam 
    where (exam_id, score) in ((select exam_id, max(score) from Exam group by exam_id) 
    union all (select exam_id, min(score) from Exam  group by exam_id))
)
order by student_id;

# 写法2
# 根据成绩升序和降序排序，根据学生id分组，去掉成绩排名中为1的记录
select e.student_id, student_name
from (
    select student_id, 
    rank() over(partition by exam_id order by score desc) max_score_rk,
    rank() over(partition by exam_id order by score) min_score_rk
    from Exam
) e
left join Student s
on e.student_id = s.student_id
group by e.student_id
having min(e.max_score_rk) <> 1 and min(e.min_score_rk) <> 1
order by e.student_id;

1767 寻找没有被执行的任务对

• 1767题

with recursive t as 
(
    select task_id, subtasks_count subtask_id from Tasks
    union all
    select task_id, subtask_id-1 from t where subtask_id > 1
)

select t.task_id, t.subtask_id
from t
left join Executed e
on t.task_id = e.task_id and t.subtask_id = e.subtask_id
where e.subtask_id is null;

总结

通过recursive直接生成每个任务对应的所有可能的（主任务id， 子任务id）组合，下面是recursive的用法。

WITH RECURSIVE cte (n) AS
( select 初始值 from table
union all
select 递归内容 from cte where (终止条件)
)

参考

1. 1285题思路
2. 行转列7种方法