SQL 窗口函数

1.窗口函数之排序函数

RANK, DENSE_RANK, ROW_NUMBER

1. RANK函数
   
   计算排序时,如果存在相同位次的记录,则会跳过之后的位次
   
   有 3 条记录排在第 1 位时: 1 位、1 位、1 位、4 位…
2. DENSE_RANK函数
   
   同样是计算排序,即使存在相同位次的记录,也不会跳过之后的位次
   
   有 3 条记录排在第 1 位时: 1 位、1 位、1 位、2 位
3. ROW_NUMBER函数
   
   赋予唯一的连续位次。
   
   例)有 3 条记录排在第 1 位时: 1 位、2 位、3 位、4 位

语法
<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)

根据分数对学生从高到低排名，不对科目进行分组，成绩相同则排名相同并且位次连续

SELECT student_name, score, DENSE_RANK() OVER(ORDER BY score DESC) AS 'rank' FROM grade;

根据分数对同科目的学生从高到低排名，成绩相同，排名不相同

SELECT student_name, score, ROW_NUMBER() OVER(PARTITION BY course_name ORDER BY score DESC) AS 'rank' FROM grade;

窗口函数使用场景

1.直接排序:按照某列对数据进行排序
2.Top-k:查询排名TOP-k的数据
3.最值问题:查询最大、最多的数据
4.辅助列:比如计算连续登陆问题、用SQL计算平均数、众数、中位数(可以参考我之前的视频)

2.窗口函数之分析函数

1.lead():将数据进行位移，把数据从下向上推，下端出现空格

2.lag():将数据进行位移，把数据从上向下推，上端出现空格

语法
lead(column, offset, default) OVER(ORDER BY column)

例题：

180.连续出现的数字

思路：使用lead函数，将当前数后面一个和两个的数进行选取，然后对比

select distinct a.num as ConsecutiveNums from (
    select num,id,
    lead(num,1) over (order by id) as ne,
    lead(num,2) over (order by id) as nene,
    lead(id,1) over (order by id) as id1,
    lead(id,2) over (order by id) as id2 from Logs
) a where a.num=a.ne and a.ne=a.nene and a.id+1=a.id1 and a.id1+1=a.id2
# 判断条件为3个num的值要相等，并且这3个数的id要连续