MySQL窗口函数的具体使用

当前第2页 返回上一页

  对于滑动窗口的范围指定，有两种方式，基于行和基于范围，我跟你着重介绍常用的基于行来控制窗口范围；

  通常使用BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：

• CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用
• UNBOUNDED PRECEDING 边界是分区中的第一行
• UNBOUNDED FOLLOWING 边界是分区中的最后一行
• expr PRECEDING 边界是当前行减去expr的值
• expr FOLLOWING 边界是当前行加上expr的值

来看几个例子：

①计算当前行与前n行（共n+1行）的聚合窗口函数

下例中控制窗口大小为当前月+前两个月的利润总和，来看一下效果：

SELECT *,SUM(sales) OVER win as '近三个月利润相加'
FROM chh_baozipu 
WINDOW win as (PARTITION BY product ORDER BY `month` ROWS 2 PRECEDING);

mysql> SELECT *,SUM(sales) OVER win as '近三个月利润相加'
    -> FROM chh_baozipu 
	-> WINDOW win as (PARTITION BY product ORDER BY `month` ROWS 2 PRECEDING);
+----+--------------------+-------+---------+--------------------------+
| id | product            | sales | month   | 近三个月利润相加         |
+----+--------------------+-------+---------+--------------------------+
|  6 | 猪肉大葱包子       |  1000 | 2021-06 |                     1000 |
|  5 | 猪肉大葱包子       |  1600 | 2021-07 |                     2600 |
|  4 | 猪肉大葱包子       |   800 | 2021-08 |                     3400 |
|  3 | 猪肉大葱包子       |  1000 | 2021-09 |                     3400 |
|  2 | 猪肉大葱包子       |  1600 | 2021-10 |                     3400 |
|  1 | 猪肉大葱包子       |   600 | 2021-11 |                     3200 |
| 12 | 面馅儿包子         |   200 | 2021-06 |                      200 |
| 11 | 面馅儿包子         |   100 | 2021-07 |                      300 |
| 10 | 面馅儿包子         |     0 | 2021-08 |                      300 |
|  9 | 面馅儿包子         |   300 | 2021-09 |                      400 |
|  8 | 面馅儿包子         |   200 | 2021-10 |                      500 |
|  7 | 面馅儿包子         |   700 | 2021-11 |                     1200 |
+----+--------------------+-------+---------+--------------------------+
12 rows in set (0.00 sec)

②计算当前行与前n1行、后n2行的聚合窗口函数

下例中控制窗口大小为当前月前一个月到后一个月的利润总和，来看一下效果：

SELECT *,SUM(sales) OVER win as '前三个月利润相加' 
FROM chh_baozipu 
WINDOW win as (PARTITION BY product ORDER BY `month` ROWS BETWEEN n1 PRECEDING AND n2 FOLLOWING);

mysql> SELECT *,SUM(sales) OVER win as '前一个月到下一个月利润相加' FROM chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month` ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
+----+--------------------+-------+---------+--------------------------+
| id | product            | sales | month   |前一个月到下一个月利润相加|
+----+--------------------+-------+---------+--------------------------+
|  6 | 猪肉大葱包子       |  1000 | 2021-06 |                     2600 |
|  5 | 猪肉大葱包子       |  1600 | 2021-07 |                     3400 |
|  4 | 猪肉大葱包子       |   800 | 2021-08 |                     3400 |
|  3 | 猪肉大葱包子       |  1000 | 2021-09 |                     3400 |
|  2 | 猪肉大葱包子       |  1600 | 2021-10 |                     3200 |
|  1 | 猪肉大葱包子       |   600 | 2021-11 |                     2200 |
| 12 | 面馅儿包子         |   200 | 2021-06 |                      300 |
| 11 | 面馅儿包子         |   100 | 2021-07 |                      300 |
| 10 | 面馅儿包子         |     0 | 2021-08 |                      400 |
|  9 | 面馅儿包子         |   300 | 2021-09 |                      500 |
|  8 | 面馅儿包子         |   200 | 2021-10 |                     1200 |
|  7 | 面馅儿包子         |   700 | 2021-11 |                      900 |
+----+--------------------+-------+---------+--------------------------+
12 rows in set (0.00 sec)

4、头尾函数：FIRST_VALUE(expr)、LAST_VALUE(expr)

头尾函数应用于：返回第一个或最后一个expr的值；

应用场景：截止到当前，按照日期排序查询当前最大月收入和当前最小月收入。

SELECT *,
FIRST_VALUE(sales) over win as '当前最大月收入',
LAST_VALUE(sales) over win as '当前最小月收入' 
from chh_baozipu 
WINDOW win as (PARTITION BY product ORDER BY `month`);

mysql> SELECT *,FIRST_VALUE(sales) over win as '当前最大月收入',LAST_VALUE(sales) over win as '当前最小月收入' from chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month`);
+----+--------------------+-------+---------+-----------------------+-----------------------+
| id | product            | sales | month   | 当前最大月收入        | 当前最小月收入        |
+----+--------------------+-------+---------+-----------------------+-----------------------+
|  6 | 猪肉大葱包子       |  1000 | 2021-06 |                  1000 |                  1000 |
|  5 | 猪肉大葱包子       |  1600 | 2021-07 |                  1000 |                  1600 |
|  4 | 猪肉大葱包子       |   800 | 2021-08 |                  1000 |                   800 |
|  3 | 猪肉大葱包子       |  1000 | 2021-09 |                  1000 |                  1000 |
|  2 | 猪肉大葱包子       |  1600 | 2021-10 |                  1000 |                  1600 |
|  1 | 猪肉大葱包子       |   600 | 2021-11 |                  1000 |                   600 |
| 12 | 面馅儿包子         |   200 | 2021-06 |                   200 |                   200 |
| 11 | 面馅儿包子         |   100 | 2021-07 |                   200 |                   100 |
| 10 | 面馅儿包子         |     0 | 2021-08 |                   200 |                     0 |
|  9 | 面馅儿包子         |   300 | 2021-09 |                   200 |                   300 |
|  8 | 面馅儿包子         |   200 | 2021-10 |                   200 |                   200 |
|  7 | 面馅儿包子         |   700 | 2021-11 |                   200 |                   700 |
+----+--------------------+-------+---------+-----------------------+-----------------------+
12 rows in set (0.00 sec)

5、其他函数：nth_value() / nfile()

nfile()不常用，不再赘述；这里我们只提一下NTH_VALUE(expr,n)函数；

NTH_VALUE用途：返回窗口中第n个expr的值。

应用场景：截止到当前，显示陈哈哈包子铺月利润榜中排名第2和第3的成绩的利润。

SELECT *,
nth_value(sales,2) over win as '当前排名第二的月收入',
nth_value(sales,3) over win as '当前排名第三的月收入' 
from chh_baozipu 
WINDOW win as (PARTITION BY product ORDER BY `month`);

mysql> SELECT *,nth_value(sales,2) over win as '当前排名第二的月收入',nth_value(sales,3) over win as '当前排名第三的月收入' from chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month`);
+----+--------------------+-------+---------+--------------------------------+--------------------------------+
| id | product            | sales | month   | 当前排名第二的月收入           | 当前排名第三的月收入           |
+----+--------------------+-------+---------+--------------------------------+--------------------------------+
|  6 | 猪肉大葱包子       |  1000 | 2021-06 |                           NULL |                           NULL |
|  5 | 猪肉大葱包子       |  1600 | 2021-07 |                           1600 |                           NULL |
|  4 | 猪肉大葱包子       |   800 | 2021-08 |                           1600 |                            800 |
|  3 | 猪肉大葱包子       |  1000 | 2021-09 |                           1600 |                            800 |
|  2 | 猪肉大葱包子       |  1600 | 2021-10 |                           1600 |                            800 |
|  1 | 猪肉大葱包子       |   600 | 2021-11 |                           1600 |                            800 |
| 12 | 面馅儿包子         |   200 | 2021-06 |                           NULL |                           NULL |
| 11 | 面馅儿包子         |   100 | 2021-07 |                            100 |                           NULL |
| 10 | 面馅儿包子         |     0 | 2021-08 |                            100 |                              0 |
|  9 | 面馅儿包子         |   300 | 2021-09 |                            100 |                              0 |
|  8 | 面馅儿包子         |   200 | 2021-10 |                            100 |                              0 |
|  7 | 面馅儿包子         |   700 | 2021-11 |                            100 |                              0 |
+----+--------------------+-------+---------+--------------------------------+--------------------------------+
12 rows in set (0.00 sec)

本章小结

  窗口函数就说到这里，窗口函数是我接触MySQL8以后发现的新东西，突然感觉MySQL开发团队还是很灵性的，每个版本都会新增一些玩儿法，当然也很实用，希望MySQL9.0会给我们带来更多的惊喜。

到此这篇关于MySQL窗口函数的具体使用的文章就介绍到这了,更多相关MySQL窗口函数内容请搜索

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容

相关阅读 >>

sql语言是什么语言

详解navicat简单使用方法

查看表结构的sql指令是什么

mysql的一条慢sql查询导致整个网站宕机的解决方法

左连接、右连接、内连接、全外连接的区别是什么？

深入理解sql server中的表扫描

oracle中 关于数据库存储过程和存储函数的使用

sql interface是什么

sparksql使用快速入门

用sql批量插入数据的存储过程

更多相关阅读请进入《sql》频道 >>

书籍

数据库系统概念 第6版

￥41.1元   机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » MySQL窗口函数的具体使用