SQL-一些开窗函数使用场景（OVER+lag）

目录

​​​​​OVER()函数

例子

lag()函数和lead()函数

---

 

​​​​​OVER()函数

OVER(PARTITION BY Year ORDER BY Month DESC )
数据会根据Year字段进行分组，按照Month字段降序排列

OVER()利用Year进行分组，然后利用Month进行组内排序 

 加一些分析函数

比如row_number()：可以在over函数执行的基础上对Month进行不重复排序

比如在over函数基础上加Rank()函数 ，就是随机那种了Month的话就会重复排序，并且over函数不相同的month——>不会保持原本的排序，比如1后面就直接56了

 如果想要保持顺序，我们可以用Dense_rank()函数将后面的元素进行跟序排序

 按班级进行分组，然后按成绩进行排序

sum(score) over(partition by class_id order by score desc)
根据班级ID进行分组，成绩降序排列，再将成绩依次递增

例子

每个部门进行薪水排名——>扩展：取每个部门的第一个或者最后一名

select department_id，rank（） over（partition by department_id order by salary) from employees

查询每个学生的分数最高的前3门课程

 SELECT *
     FROM(
        SELECT stu_id,
        ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY score DESC) AS score_
order,
        lesson_id, score
        FROM t_score) t
    WHERE score_order <= 3
     ;

lag()函数和lead()函数

 子查询可以得到前一条数据通过lag(num,1)然后lag(num,2)得到前两条数据以id进行分组，如果当前的num等于前两个就查询

#编写一个 SQL 查询，查找所有至少连续出现三次的数字。
#利用解决求连续登录问题的思路，使用Lag()开窗函数
select distinct t.num as ConsecutiveNums
from (
select  *,
       lag(num,1) over (order by id) last1,
       lag(num,2) over (order by id) last2
from Logs) t
where t.num=t.last1 and t.last1=t.last2;

场景二：查找与昨天相比，温度更高的所有日期的id

select id from (
   select id,recordDate,temperature,
       LAG(temperature,1) OVER (order by recordDate) as r
   from weather) as t
where temperature>r and r is not null