MySQL --- 窗口函数

桃扇骨 2024-03-31 16:23 41阅读 0赞

**目录**

概念

语法

窗口确定

分组子句(partition by)

排序子句(order by)

窗口子句(rows)

总体流程

函数分类

排序类

案例

跨行类

案例

分组内topN

语法

案例

--------------------

### 概念 ###

窗口限定一个范围，它可以理解为满足某些条件的记录集合，窗口函数也就是在窗口范围内执行的函数。

### 语法 ###

窗口函数有over关键字，指定函数执行的范围，可分为三部分：分组子句(partition by)，排序子句(order by)，窗口子句(rows)

<函数名> over (partition by <分组的列> order by <排序的列> rows between <起始行> and <终止行>)

> **TIP：Mysql8才支持窗口函数**

### 窗口确定 ###

#### 分组子句(partition by) ####

不分组可以写成partition by null或者直接不写

后面可以跟多个列， 如 partition by cid, sname

> **TIP：** 
> 
> **partition by与group by的区别**
> 
> 1）前者不会压缩行数但是后者会
> 
> 2）后者只能选取分组的列和聚合的列
> 
> 也就是说group by 后生成的结果集与原表的行数和列数都不同

#### 排序子句(order by) ####

不排序可以写成order by null 或者直接不写

asc或不写表示升序，desc表示降序

后面可以跟多个列， 如 order by cid, sname

#### 窗口子句(rows) ####

窗口子句的描述：

起始行: N preceding/unbounded preceding

当前行: current row

终止行: N following/unbounded following

> **TIP:**
> 
> 排序子句后面缺少窗口子句，窗口规范默认是 rows between unbounded preceding and current row
> 
> 排序子句和窗口子句都缺失，窗口规范默认是 rows between unbounded preceding and unbounded following

#### 总体流程 ####

1.通过partition by 和 order by 子句确定大窗口（ 定义出上界unbounded preceding和下界unbounded following）

2.通过row 子句针对每一行数据确定小窗口（滑动窗口）

3.对每行的小窗口内的数据执行函数并生成新的列

### 函数分类 ###

#### 排序类 ####

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>RANK（）</td> 
   <td>分区中当前行的排名，带有间隙</td> 
  </tr> 
  <tr> 
   <td>DENSE_RANK（）</td> 
   <td>分区中当前行的排名，无间隙</td> 
  </tr> 
  <tr> 
   <td>ROW_NUMBER（）</td> 
   <td>其分区中的当前行数</td> 
  </tr> 
 </tbody> 
</table>

#### 案例 ####

表图

![25178a32e7204ae5b62bc09cae3ee112.png][]

代码

-- 【排序类】
    -- 按班级分组后打上序号 不考虑并列
    select *, row_number() over (partition by cid order by score desc) as '不可并列排名'
    from SQL_5;
    -- 按班级分组后作跳跃排名 考虑并列
    select *, rank() over (partition by cid order by score desc) as '跳跃可并列排名'
    from SQL_5;
    -- 按班级分组后作连续排名 考虑并列
    select *, dense_rank() over (partition by cid order by score desc) as '连续可并列排名'
    from SQL_5;
    -- 合并起来对比
    select *,
           row_number() over (partition by cid order by score desc) as '不可并列排名',
           rank() over (partition by cid order by score desc)       as '跳跃可并列排名',
           dense_rank() over (partition by cid order by score desc) as '连续可并列排名'
    from SQL_5;

结果

![6a04247d03164eeab60ed3ae505d3e64.png][]

#### 跨行类 ####

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>LAG（）</td> 
   <td>分区内滞后当前行的参数值</td> 
  </tr> 
  <tr> 
   <td>LEAD（）</td> 
   <td>分区内当前行前导行的参数值</td> 
  </tr> 
 </tbody> 
</table>

#### 案例 ####

代码

-- 【跨行类】
    -- lag/lead 函数 参数1:比较的列 参数2: 偏移量 参数3:找不到的默认值
    -- 同一班级内，成绩比自己低一名的分数是多少
    select *, lag(score, 1) over (partition by cid order by score) as '低一名的分数' from SQL_5;
    -- 或者写成
    select *, lag(score, 1, 0) over (partition by cid order by score) as '低一名的分数' from SQL_5;
    -- 同一班级内，成绩比自己高2名的分数是多少
    select *, lead(score, 2) over (partition by cid order by score) as '高两名的分数' from SQL_5;

结果

![3bddba3e719f42168755a58e74be103b.png][]

![07370a497e9f48b0823f226a2ee18283.png][]

### 分组内topN ###

#### 语法 ####

select * from
    (
    	select *, row_number() over (partition by 分组列 order by 比较列) as rn from table
    ) as tmp
    where rn <= N;

#### 案例 ####

表图

![d2be0e43414641a4b38785e9119cac7f.png][]

代码

-- 【分组内topN】
    -- 求出每个学生成绩最高的三条记录
    select * from
    (
        select *, row_number() over (partition by sname order by score desc) as rn from SQL_6
    ) temp
    where rn <= 3

结果

![0e81cbdae39b403081ccf95e5f7b8d9c.png][]