SQL助你面大厂（窗口函数）

逃离我推掉我的手 2023-10-14 23:38 39阅读 0赞

在面试过程中窗口函数的应用可谓是数不胜数，前提你要知道什么是窗口函数，最常用的窗口函数有哪些？语法是什么？分别用的场景是什么？今天会以这三个问题开始我们今天的学习

#### 什么是窗口函数？ ####

所谓的窗口函数就是动态处理数据的一类函数，也叫做“OLAP”函数，可以对数据库数据进行实时分析处理，“窗口函数”由两部分组成：**分析函数+窗口函数**：

--模板
    函数名(字段名) over(partition by xxx,yyy order by zzz)
    
    --详细介绍
    分析函数 over (partition by "要分组的列")
                  order by "要排序的列"
                  rows between "开始的行" and "结束的行")

over（）部分才是“窗口函数”，只不过因为它的特点大家都这样叫，其作用就是划定窗口，分析函数的作用是在窗口内对记录进行统计

over函数中有**3个参数**：

*  partition by

其表示的是分区或者分组，可以理解成为group by,但是低位有所不同，如果partition by 和group by 后的字段相同，partition by 就会失效，因为group by先分组（划分窗口），如果字段相同则partition by 就没有可分的了

如果partition函数想要做到像group by一样的效果，必须要和distinct关键字进行去重

*  order by

表示排序，排序、累加都是有排序的操作在里面，根据设么条件排名、根据什么条件进行累加，order by后面可以跟一个字段也可以跟多个字段，位置优先，越在前面排序的优先级越高，默认“asc(升序)” 小-大，desc(降序)大-小

*  rows between "开始的行" and "结束的行"

这部分是控制窗口大小的地方，一般默认就行，也不会进行书写，但是也可以自定义窗口大小：

proceding:往前
    following:往后
    unbounded:无穷尽的
    current row:当前行
    unbounded proceding:往前所有行
    unbounded following:往后所有行

举几个例子方便理解：

rows between 2 preceding and current row         --取当前行和前2行，这个可用于求移动平均 
    rows between current row and 2 following         --取当前行和后2行，这个可用于求移动平均
    rows between unbounded preceding and current row --取当前行和之前所有行，这个可用于求累加；如果没有写rows between参数，则默认的窗口就是这个范围
    rows between current row and unbounded following --取当前行和之后所有行
    rows between 2 preceding and 2 following         --取当前行的前面2行和下面2行，总共5行，这个可用于求移动平均

动态窗口的动态演示图：

![动图][bf5c04ac507bb7c4e15e5d53add53c61.png]

接下来我们来讲一般常用的三种窗口函数：

我们现在有一个班级的成绩数据，要求按规定进行查询：

CREATE TABLE class(
    cid VARCHAR(10),
    NAME VARCHAR(20),
    grades INT
    )
    DROP TABLE class
    
    INSERT INTO class (cid,NAME,grades) VALUES('1','路飞',60),('1','鸣人',70),('1','佐助',75),('1','萧炎',80),('1','唐三',90),('1','奕星',91),('2','霸天虎',50),('2','玉环',60),('2','索隆',66),('2','墨子',85),('2','铁甲小宝',89)

![96dbcfa3229049778ac8fbea565c98e5.png][]

假如动漫学院期末成绩出来了，老师让你帮根据一定的规则去查询成绩，你可以帮忙完成这个工作么？

*  聚合类的窗口函数 sum() over()（举例） count\\avg\\max\\min

情境一：现在要求让每位同学后面都新增一列各自班级的总分

SELECT*,
      SUM(grades) OVER (PARTITION BY cid) AS "班级总分"
    FROM class

![50c8a6976c5343e69dbf17337dd6a0b9.png][]

情境二:计算同一个班级内，每个同学和比他分数低的总分是多少？

SELECT*,
      SUM(grades) OVER (PARTITION BY cid ORDER BY grades) AS "累加分数"
    FROM class

![3fab8bb9921643b486b5ec400ae3511c.png][]

SELECT*,
      SUM(grades) OVER (PARTITION BY cid ORDER BY grades ROWS BETWEEN unbounded preceding AND current ROW ) AS "累加分数"
    FROM class

这条SQL语句的效果和上面那条SQL语句效果一致，不写的话默认就是这个范围

![9d91e3c40435494da7523ce36b095911.png][]

*  排序类的窗口函数
    
    ![37398785c26945f0935bb5d5ae1dba16.png][]

1.  **row\_number(顺序，相同的不做特别处理)**  
      
    假如现在让你将1、2班的成绩排个名，从低到高，你有没有什么好的办法？|
    
        SELECT *,
          ROW_NUMBER() OVER (PARTITION BY cid ORDER BY grades DESC) AS "成绩排名"
        FROM class;
    
    ![adfe8eff7e554b21bb2833c986f1ccff.png][]
2.  **rank（相同的做特别处理，会进行占位）**  
      
    在拍完名之后，发现相同成绩的同学居然排名不一样，这又让老师犯难了，怎么样才能使得相同成绩的同学拥有相同的排名
    
        SELECT *,
          RANK() OVER (PARTITION BY cid ORDER BY grades DESC) AS "成绩排名"
        FROM class;
    
    ![ebba33f5f29547d98cd70f4c677b55aa.png][]
3.  **dense\_rank（相同的做特殊处理，不占位）**  
      
    但是又新出现了一个问题，相同的成绩的排名确实排名也相同了，但是后面的那个同学居然是从3开始的，自动的将2跳过了，这也是我们不希望看到的  
    
    
        SELECT *,
          DENSE_RANK() OVER (PARTITION BY cid ORDER BY grades DESC) AS "成绩排名"
        FROM class;
    
    ![7f2ba0f788f04256add50972150a8a3c.png][]

*  偏移类的、跨行的窗口函数

1.  lag(后面)  
      
    如果现在又有一个任务，让每位同学知道比自己低1（N）名的同学的成绩  
    
    
        SELECT*,
        LAG(grades,1) OVER (PARTITION BY cid ORDER BY grades) AS "低一名的成绩"
        FROM class
    
    ![11efc18bb03347fcb3dd11fff1af0a9b.png][]
    
    但是最后的结果中却出现了null值，我们想如果查不到数据结果置为0，该怎么去实现呢？  
    LAG(字段，差值，默认值)  
    
    
        SELECT*,
        LAG(grades,1,0) OVER (PARTITION BY cid ORDER BY grades) AS "低一名的成绩"
        FROM class
    
    ![e00af8be7b6f4cefa637959f762948d5.png][]
2.  2.lead(前面)

如果现在又有一个任务，让每位同学知道比自己高1（N）名的同学的成绩

SELECT*,
    LEAD(grades,1,0) OVER (PARTITION BY cid ORDER BY grades) AS "高一名的成绩"
    FROM class

![ae2dd923fbb742c1874dba18450dd82d.png][]

最常用的窗口函数已经介绍完了，学习SQL的路上一路有我！！！

[bf5c04ac507bb7c4e15e5d53add53c61.png]: https://img-blog.csdnimg.cn/img_convert/bf5c04ac507bb7c4e15e5d53add53c61.png
[96dbcfa3229049778ac8fbea565c98e5.png]: https://img-blog.csdnimg.cn/96dbcfa3229049778ac8fbea565c98e5.png
[50c8a6976c5343e69dbf17337dd6a0b9.png]: https://img-blog.csdnimg.cn/50c8a6976c5343e69dbf17337dd6a0b9.png
[3fab8bb9921643b486b5ec400ae3511c.png]: https://img-blog.csdnimg.cn/3fab8bb9921643b486b5ec400ae3511c.png
[9d91e3c40435494da7523ce36b095911.png]: https://img-blog.csdnimg.cn/9d91e3c40435494da7523ce36b095911.png
[37398785c26945f0935bb5d5ae1dba16.png]: https://img-blog.csdnimg.cn/37398785c26945f0935bb5d5ae1dba16.png
[adfe8eff7e554b21bb2833c986f1ccff.png]: https://img-blog.csdnimg.cn/adfe8eff7e554b21bb2833c986f1ccff.png
[ebba33f5f29547d98cd70f4c677b55aa.png]: https://img-blog.csdnimg.cn/ebba33f5f29547d98cd70f4c677b55aa.png
[7f2ba0f788f04256add50972150a8a3c.png]: https://img-blog.csdnimg.cn/7f2ba0f788f04256add50972150a8a3c.png
[11efc18bb03347fcb3dd11fff1af0a9b.png]: https://img-blog.csdnimg.cn/11efc18bb03347fcb3dd11fff1af0a9b.png
[e00af8be7b6f4cefa637959f762948d5.png]: https://img-blog.csdnimg.cn/e00af8be7b6f4cefa637959f762948d5.png
[ae2dd923fbb742c1874dba18450dd82d.png]: https://img-blog.csdnimg.cn/ae2dd923fbb742c1874dba18450dd82d.png