SQL 窗口函数LC题目和cheetsheet分享

LZ平时不用SQL，单纯为了面试把所有LC刷了两编，有挑战题的其实就两种： 窗口函数和自定义函数。自定义函数面试中实用性不高，用的时候容易出错。
但是如果窗口函数不熟练，遇到就会跪，而且是立刻马上，拉都拉不起来。

LZ从LC挑出八道代表题，附上我的答案和考点，值得反复练习。结尾处附上窗口函数的cheetsheet。

据说给大米的最后都拿到大包

1.        Median (LC 571)
# Median选判条件

1. select avg(a.number) as median
   
2. from(
   
3. select number,
   
4. sum(Frequency) over(order by number) as pc,
   
5. sum(Frequency) over(order by number desc) as nc,
   
6. sum(Frequency) over() as tt
   
7. rom numbers
   
8. as a
   
9. where a.pc>=tt/2 and a.nc>=tt/2

复制代码

2.        TOP N (LC 185)
# Row_number (). rank (), 和dense_rank () 区别

1. with tp as
   
2. Select Name, Salary, DepartmentId,
   
3. dense_rank() over(partition by DepartmentId order by Salary desc) as top
   
4. from Employee)
   
5. select d.Name as Department,tp.Name as Employee,tp.Salary
   
6. from tp
   
7. join Department as d
   
8. on tp.DepartmentId = d.Id
   
9. where tp.top<4

复制代码

3.        Consecutive Numbers (LC 180)
#主键ID不连续
# DF对于连续数字相等

1. SELECT DISTINCT Num AS CN
   
2. FROM (
   
3. SELECT *, ROW_NUMBER () OVER (ORDER BY Id) –
   
4. ROW_NUMBER () OVER (PARTITION BY Num ORDER BY Id) AS DF
   
5.     FROM Logs
   
6.     ) as TP
   
7. GROUP BY Num, DF
   
8. HAVING COUNT (1) >=3

复制代码

4.        Continuous Ranges (LC 1285)
# 连续问题选用lead/lag 函数 或者 id-row_number

1. SELECT
   
2.     MIN(log_id) START_ID,
   
3.     MAX(log_id) END_ID
   
4. FROM (
   
5.     SELECT
   
6.         log_id,
   
7.         log_id-ROW_NUMBER () OVER (ORDER BY log_id) DF
   
8.     FROM Logs
   
9.     ) AS TP
   
10. GROUP BY DF
    
11. ORDER BY 1

复制代码

5.        Consecutive Available Seats (LC 603)
# 连续问题选用lead/lag 函数 或者 id-row_number

1. select distinct seat_id
   
2. from (
   
3.     select *,
   
4.         lag(free,1,0) over(order by seat_id) as lg,
   
5.         lead(free,1,0) over(order by seat_id) as ld
   
6.     from cinema
   
7. ) as TP
   
8. where free=1 and (lg=1 or ld=1)
   
9. order by 1

复制代码

6.        Students Report by Geography (LC 618)
# Row_number () 给列中元素编号
# Group by + 聚合函数 行转列

1. select
   
2. MAX (if (continent="America", name, NULL)) as America,
   
3. MAX (if (continent="Asia", name, NULL)) as Asia,
   
4. MAX (if (continent="Europe", name, NULL)) as Europe
   
5. from (
   
6.     select *,
   
7.     row_number () over (partition by continent order by name) as rk
   
8.     from student
   
9.     ) as TP
   
10. group by rk
    
11. order by rk

复制代码

7.        Students Report by Geography (LC 618)
# Default window: range between unbounded preceding AND current row

1. select person_name
   
2. from (
   
3. select person_name,
   
4. sum(weight) over(order by turn) as tt
   
5. from queue
   
6. order by turn
   
7. ) as tp
   
8. where tt<=1000
   
9. order by tt desc
   
10. limit 1

复制代码

8.        Restaurant Growth (LC 1321)
# Window w 的写法
# Range是逻辑窗口，row是物理窗口，这里要用物理窗口
# limit 6, 100000 # Top 6+ 前六天不算# 窗口函数因为计算问题是不能在where，join中引用的，但是可以在外面套别的函数，比如这题avg外面用了round函数，if函数也是可以的

1. select visited_on,
   
2. sum(tt) OVER w as amount,
   
3. round(avg(tt) OVER w,2) as average_amount
   
4. from (
   
5.     select visited_on, sum(amount) as tt
   
6.     from customer
   
7.     group by 1
   
8. ) as tp
   
9. window w as (ORDER BY VISITED_ON rows between 6 PRECEDING)
   
10. limit 6,100000

复制代码

收藏的窗口函数cheetsheet，直接贴链接，不消耗大米
https://learnsql.com/blog/sql-window-functions-cheat-sheet/
















补充内容 (2021-08-30 05:40 +8:00):
第七题 标题写错了
Last Person to Fit in the Bus (LC 1204)

Thank you! Very useful

感谢！很好用。

楼主这样的好人不赞留着过年吗

感谢楼主分享

感謝樓主分享