窗口函数和OVER 关键字
环境准备
MySQL 部署
# 可选,如果本机没装mysql 客户端
brew install mysql-client
## 然后导⼊入到PATH 中
echo 'export PATH="/usr/local/opt/mysql-client/bin:$PATH"' >> ~/.bash_profile
source ~/.bash_profile
# 使⽤用Docker 部署MySQL
## MySQL 8.0 开始支持窗⼝函数,可以用最新的,并设置utf-8 字符集和root 密码为root
docker run -e MYSQL_ROOT_PASSWORD=root \
-p 3306:3306 --name mysql -d \
mysql:8.0 --character-set-server=utf8mb4 \
--collation-server=utf8mb4_unicode_ci \
--lower_case_table_names=1
创建数据文件
# 创建数据文件,后面导入表数据用
## brew install gnu-sed # 安装GNU 版的sed
## gsed 跟macOS 下的sed 用法不太一样,Linux 直接用sed 即可
## sed -r 表使用正则,s 为替换命令,\s 表空格,+ 表出现一次或多次,here doc 中\t 会被忽略
gsed -r 's/\s+//g' > /tmp/sales.txt << 'EOF'
2000 , 1 , India , Calculator , 75
2000 , 1 , Finland , Computer , 1500
2000 , 1 , USA , Calculator , 75
2000 , 4 , India , Calculator , 75
2000 , 4 , USA , Computer , 1500
2001 , 4 , Finland , Phone , 100
2000 , 7 , USA , Calculator , 50
2001 , 7 , India , Computer , 1200
2001 , 1 , USA , Computer , 1500
2001 , 7 , Finland , Phone , 10
2001 , 4 , USA , TV , 150
2001 , 7 , USA , TV , 100
EOF
## 以上板书主要是为了文档美观,写入文件时须去除掉所有的空格和制表符
登录MySQL
# MySQL 客户端连接数据库
## MySQL 连本地服务可用Unix 域套接字,但是docker 中的MySQL 跟宿主机不是同一台机器,所以要用TCP 连接
## 参数-h 127.0.0.1 表使用TCP 连接,-p 后⾯紧跟着密码,不能有空格
mysql -h 127.0.0.1 --local-infile=1 -u root -proot
## --local-infile=1 配合MySQL 中set global local_infile = 'ON'; 才能导入数据
导入数据文件
-- 创建实验数据库
CREATE DATABASE IF NOT EXISTS TestDB;
USE TestDB;
-- 创建实验表
drop table if exists sales;
CREATE TABLE sales
(
year INT,
month INT,
country VARCHAR(20),
product VARCHAR(32),
profit INT
); -- 销售表:年份,国家,产品,利润
-- 允许导入数据
set global local_infile = 'ON';
-- 显示设置结果
show global variables like 'local_infile';
-- 清空表再通过本地文件导入数据
truncate table sales;
LOAD DATA LOCAL INFILE '/tmp/sales.txt'
INTO TABLE sales FIELDS TERMINATED BY ',';
-- 清屏小技巧
system clear
窗口函数讲解
初识窗口函数
-- 0. 查询sales 表中有几个国家
select country from sales group by country;
/*--------+
| country |
+---------+
| India |
| Finland |
| USA |
+--------*/
-- 用group by 进行去重,效果等同于用distinct
select distinct country from sales;
/*--------+
| country |
+---------+
| India |
| Finland |
| USA |
+--------*/
-- 需求,显示国家的名称及有几个国家在sales 表中(根据前面的查询结果国家总数应该是3 才对)
-- 1. 使用普通聚合函数
select
country,
count(*) as CC
from sales
group by country;
/*--------+----+
| country | CC |
+---------+----+
| India | 3 |
| Finland | 3 |
| USA | 6 |
+---------+---*/
-- 查出的是sales 表中每个国家出现的次数,而不是sales 表中有几个国家,所以普通聚合函数无法完成本需求
-- 2. 使用窗口函数
select
country,
count(*) OVER () as CC
from sales
group by country;
/*--------+----+
| country | CC |
+---------+----+
| India | 3 |
| Finland | 3 |
| USA | 3 |
+---------+---*/
-- OVER 关键字用于开辟窗口,简称开窗,窗口就是一个可以根据规则灵活改变大小的记录集
-- OVER 后面的() 代表select * from sales group by country; 的查询结果,注意是'*'
-- () 代表执行FROM 、WHERE 、GROUP BY [HAVING] 后的查询结果集,详见SQL 语句执行顺序
-- count(*) 在OVER 前面它就成了窗口函数,SELECT 每扫描一行,它就被调用一次并计算窗口数据
-- 本例中,窗口里共有三条记录,所以count(*) 每次都返回3,主(即顶层)查询语句等价于
select
country,
3 as CC
from sales
group by country;
-- 此外,在窗口定义() 中还可用分区、排序、框定等子句将原数据集划分成多个数据子集,详见以下讲解
OVER 关键字
-- 开辟窗口关键字OVER 使用方式
-- 方式一,匿名窗口
select
country,
count(*) OVER () as CC
from sales
group by country;
-- OVER 关键字之后直接定义窗口
-- 不要以为OVER() 是个方法,实际上OVER 是个类似于ON、IN、BETWEEN 的关键字
-- 方式二,命名窗口
select
country,
count(*) OVER w as CC
from sales
group by country
WINDOW w AS ();
-- OVER 关键字之后是窗口名字,查询语句最后用WINDOW 关键字定义该窗口
Partition by 子句
-- 窗口中默认所有记录都在同一个分区中
-- 使用Partition by 可以对记录集进行分区,其语法为
-- PARTITION BY expr [, expr] ...
-- 需求,显示每年每个国家的利润CP 及各国每样产品的利润 PP
select distinct
year,
country,
sum(profit) OVER (partition by year, country) as CP,
product,
sum(profit) OVER (partition by year, country, product) as PP
from sales
order by country; -- 用order by 为的是查询方便阅读
/*-----+---------+------+------------+------+
| year | country | CP | product | PP |
+------+---------+------+------------+------+
| 2000 | Finland | 1500 | Computer | 1500 |
| 2001 | Finland | 110 | Phone | 110 |
| 2000 | India | 150 | Calculator | 150 |
| 2001 | India | 1200 | Computer | 1200 |
| 2000 | USA | 1625 | Calculator | 125 |
| 2000 | USA | 1625 | Computer | 1500 |
| 2001 | USA | 1750 | Computer | 1500 |
| 2001 | USA | 1750 | TV | 250 |
+------+---------+------+------------+-----*/
-- 这里窗口() 代表执行FROM 之后的查询结果集,即
select * from sales;
/*-----+-------+---------+------------+--------+
| year | month | country | product | profit |
+------+-------+---------+------------+--------+
| 2000 | 1 | India | Calculator | 75 |
| 2000 | 1 | Finland | Computer | 1500 |
| 2000 | 1 | USA | Calculator | 75 |
| 2000 | 4 | India | Calculator | 75 |
| 2000 | 4 | USA | Computer | 1500 |
| 2001 | 4 | Finland | Phone | 100 |
| 2000 | 7 | USA | Calculator | 50 |
| 2001 | 7 | India | Computer | 1200 |
| 2001 | 1 | USA | Computer | 1500 |
| 2001 | 7 | Finland | Phone | 10 |
| 2001 | 4 | USA | TV | 150 |
| 2001 | 7 | USA | TV | 100 |
+------+-------+---------+------------+-------*/
/*
加入分区条件后,即(partition by year, country) 表示将窗口记录集先按年份分区,再按国家分区
分区就类似于文件系统中分目录,表的记录就类似文本文件,(partition by year, country) 就类似于
top # 将数据集从一个目录里分到了多个多级目录中
|____year1 # 第一层目录以年份分
| |____country1 # 第二层目录以国家分
| | |____record1.txt # 末端放置的是相关的记录
| | |____record2.txt
| | |____...
| |____country2
| |____...
|____year2
|____...
分区不会改变记录的总条数,只是根据记录的内容将它们归类放置到对应的区域下而已
SELECT 会逐行扫描并调用行内的窗口函数,窗口函数把其所在的那行记录叫做CURRENT ROW 即当前行
当SELECT 扫描到第一条记录,即| 2000 | 1 | India | Calculator | 75 |
并调用行内的第一个窗口函数sum(profit) OVER (partition by year, country) as CP 时
它会先根据年份找到2000 分区,再根据国家找到India 分区,然后sum 该分区下所有记录中profit 的总和
最后返回1200 给SELECT,同理sum(profit) OVER (partition by year, country, product) as PP
它会先找年份分区、再找国家分区,然后找产品分区,最后计算该分区下所有记录中的利润总和并返回给SELECT
以此类推,直到SELECT 扫描完所有FROM 生成的记录行为止,最后就生成了含有CP 和PP 的结果表
*/
Order by 子句
-- 窗口中默认记录是无序的,OVER 前的窗口函数在计算时会把所有记录一起处理,例如
select
country,
count(*) OVER ()
from sales;
/*--------+------------------+
| country | count(*) OVER () |
+---------+------------------+
| India | 12 |
| Finland | 12 |
| USA | 12 |
| India | 12 |
| USA | 12 |
| Finland | 12 |
| USA | 12 |
| India | 12 |
| USA | 12 |
| Finland | 12 |
| USA | 12 |
| USA | 12 |
+---------+-----------------*/
-- SELECT 语句会逐行处理上一个SQL 语句(FROM 或之后的语句)生成的结果集
-- SELECT 每扫描一行记录就会调用该行所包含的窗口函数
-- 如果窗口里的记录没有顺序,窗口函数每次都返回处理全部窗口里的记录的结果
-- 本例中因为窗口里一共有12 条无序的记录,所以count 每次都返回12
-- Order by 可对窗口里的记录集进行排序,语法如下
-- ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
-- 窗口函数会把其所在的那行记录称为CURRENT ROW,即当前行
-- 排序后,每次窗口的大小为从起始位置到与当前行的排序字段相关的最大行,例如
select
country,
count(*) OVER (Order by country)
from sales;
/*--------+----------------------------------+
| country | count(*) OVER (Order by country) |
+---------+----------------------------------+
| Finland | 3 |
| Finland | 3 |
| Finland | 3 |
| India | 6 |
| India | 6 |
| India | 6 |
| USA | 12 |
| USA | 12 |
| USA | 12 |
| USA | 12 |
| USA | 12 |
| USA | 12 |
+---------+---------------------------------*/
-- 此时,原始表数据集等于
select country from sales;
-- 窗口中的数据集等同于
select country from sales order by country;
/*
(原始数据集) ==> ORDER BY COUNTRY ==> (原始数据集==窗口数据集)
+---------+ +---------+
| country | 原始数据集和窗口数据集引 | country |
+---------+ 用的是同一个数据集对象,所以 +---------+ <-- 起始位置
| India | 当对窗口数据集进行排序时,相 | Finland |
| Finland | 于对原始数据集也进行了排序 | Finland |
| USA | | Finland | <-- 窗口1 结尾,记录数=3
| India | | India |
| USA | | India |
| Finland | | India | <-- 窗口2 结尾,记录数=6
| USA | | USA |
| India | | USA |
| USA | | USA |
| Finland | | USA |
| USA | | USA |
| USA | | USA | <-- 窗口3 结尾,记录数=12
+---------+ +---------+
*/
-- 主查询中,SELECT 在扫描第1 条记录时,调用了窗口函数count(*),排序字段country 值为Finland
-- 所以,此时的窗口大小是从起始位置到第3 行记录,因为这3 行的country 字段都是Finland
-- 因此SELECT 从第1 条记录扫描到第3 条记录,调用窗口函数count(*) 的返回值都是3
-- 同理,到了第4 条记录,排序字段country 值为India,窗口大小为从起始位置到第6 行记录
-- 所以,SELECT 从第4 条记录扫描到第6 条记录,调用窗口函数count(*) 的返回值都是6
-- 再看一个两级排序的列子,先按year 排,再按country 排
select
year,
country,
count(*) OVER (order by year, country)
from sales;
/*-----+---------+----------------------------------------+
| year | country | count(*) OVER (order by year, country) |
+------+---------+----------------------------------------+ <-- 起始位置
| 2000 | Finland | 1 | <-- 窗口1 结束,记录数=1
| 2000 | India | 3 |
| 2000 | India | 3 | <-- 窗口2 结束,记录数=3
| 2000 | USA | 6 |
| 2000 | USA | 6 |
| 2000 | USA | 6 | <-- 窗口3 结束,记录数=6
| 2001 | Finland | 8 |
| 2001 | Finland | 8 | <-- 窗口4 结束,记录数=8
| 2001 | India | 9 | <-- 窗口5 结束,记录数=9
| 2001 | USA | 12 |
| 2001 | USA | 12 |
| 2001 | USA | 12 | <-- 窗口6 结束,记录数=12
+------+---------+---------------------------------------*/
-- 像之前说的,对窗口数据集进行排序就相当于对原始数据集进行排序,因为它们引用同一个数据集对象
-- 主查询SELECT 扫描第1 条记录时,满足year==2000 && country==Finland 的只有当前一行记录
-- 所以count(*) 返回1。同理,SELECT 继续扫描到第2 条记录时,发现year 和country 与当前行相同的
-- 还有第3 条记录,所以count(*) 返回3。扫描到第3 条记录时,count(*) 自然还是返回3。扫描到第4 条
-- 记录时,发现year==2000 && country==USA 能一直匹配到第6 条记录,所以第4 到第5 个count(*) 返回6
-- 以此类推,最终形成了上面展示的结果表。窗口排序不会改变原数据量的条数,它只是设定窗口伸缩范围的规则
-- 结合Partition by 使用,Order 会分别对每个分区进行排序
select
year,
country,
count(*) OVER (partition by year order by country)
from sales;
/*-----+---------+----------------------------------------------------+
| year | country | count(*) OVER (partition by year order by country) |
+------+---------+----------------------------------------------------+ <-- 起始位置
| 2000 | Finland | 1 | <-- 2000 分区
| 2000 | India | 3 |
| 2000 | India | 3 |
| 2000 | USA | 6 |
| 2000 | USA | 6 |
| 2000 | USA | 6 | <-- 2000 分区
| 2001 | Finland | 2 | <-- 2001 分区
| 2001 | Finland | 2 |
| 2001 | India | 3 |
| 2001 | USA | 6 |
| 2001 | USA | 6 |
| 2001 | USA | 6 | <-- 2001 分区
+------+---------+---------------------------------------------------*/
-- 可见,所有窗口定义无论是分区还是排序或者别的都不会改变主查询数据集的记录数
-- 另外,对于窗口数据集Order by 排序不同的数据库可能会有不同的实现,例如
select
country,
count(*) OVER (Order by country) A,
count(*) OVER (Order by country desc) D
from sales;
/*
MySQL 执行结果 SQLite3 执行结果
+---------+----+----+ country A D
| country | A | D | ---------- ---------- ----------
+---------+----+----+ Finland 3 12
| USA | 12 | 6 | Finland 3 12
| USA | 12 | 6 | Finland 3 12
| USA | 12 | 6 | India 6 9
| USA | 12 | 6 | India 6 9
| USA | 12 | 6 | India 6 9
| USA | 12 | 6 | USA 12 6
| India | 6 | 9 | USA 12 6
| India | 6 | 9 | USA 12 6
| India | 6 | 9 | USA 12 6
| Finland | 3 | 12 | USA 12 6
| Finland | 3 | 12 | USA 12 6
| Finland | 3 | 12 |
+---------+----+----+
可见,对原始数据集SQLite3 只进行了第1 次的排序,而MySQL 进行了2 次
尽管如此,从查询结果可以看出这两种数据库对窗口大小定义的规则是一样的
*/
Row 或Range 子句
-- Row 或Range 子句 也称Frame 子句,它是定义每次调用窗口函数时窗口数据集大小的,例如
select
country,
count(*) OVER w as '窗口行总数'
from sales
group by country
window w as (rows between current row and UNBOUNDED FOLLOWING);
/*--------+-----------------+
| country | 窗口行总数 |
+---------+-----------------+
| India | 3 |
| Finland | 2 |
| USA | 1 |
+---------+----------------*/
-- 窗口函数会把其所在的那行记录称为CURRENT ROW
-- UNBOUNDED FOLLOWING 则表示之后的所有行
-- 所以,主查询中SELECT 在扫描第1 条记录时,该记录就是CURRENT ROW
-- 窗口范围从当前行到之后所有行,所以一共是3 行记录,count(*) 返回3,后面以此类推
/*
Frame 子句语法如下
frame_clause:
{ROWS | RANGE} {frame_start | BETWEEN frame_start AND frame_end}
frame_start, frame_end: {
CURRENT ROW
| UNBOUNDED PRECEDING | UNBOUNDED FOLLOWING
| expr PRECEDING | expr FOLLOWING
}
官方原文中还有这么一句话
In the absence of a frame clause, the default frame depends on whether an ORDER BY clause is present, as described later in this section.
说明ORDER BY 也是定义FRAME 的一种,也是定义窗口大小的一种方式
*/
知识扩充
SQL 执⾏顺序
/*
SQL 语句在MySQL 中的执行顺序
(1) FROM <left_table>
(2) ON <join_condition>
(3) <join_type> JOIN <right_table>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(7) SELECT
(8) DISTINCT <select_list>
(9) UNION
(10) ORDER BY <order_by_condition>
(11) LIMIT <limit_number>
随带提⼀一下:
UNION 执⾏在ORDER BY 之前,所以联合两个已排序的表最终却乱序,
在MySQL 中要联结的有序表在最后加LIMIT 可以保证UNION 的结果顺序不变。
*/
添加行号
-- 2. select 每生成一行数据,变量RN 就+1 一次
select
@RN1:=@RN1+1 AS ROWNUM,
s.*
from
sales s,
(select @RN1:=0) t; -- 1. 变量声明,from 先于select 执行
逐行累加
-- 数据准备
drop table if exists tt;
create table tt (id int, xx int);
insert into tt values (1, 1), (2, 2), (3, 3);
-- 显示XX 列的累加,步骤分解
-- 1. 基本数据
select id, xx from tt;
/*-----+------+
| id | xx |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+-----*/
-- 3 rows in set
-- 2. 表tt 分别取别名t1 和t2 的笛卡尔乘积
select * from tt as t1, tt as t2;
/*-----+------+------+------+
| id | xx | id | xx |
+------+------+------+------+
| 3 | 3 | 1 | 1 |
| 2 | 2 | 1 | 1 |
| 1 | 1 | 1 | 1 |
| 3 | 3 | 2 | 2 |
| 2 | 2 | 2 | 2 |
| 1 | 1 | 2 | 2 |
| 3 | 3 | 3 | 3 |
| 2 | 2 | 3 | 3 |
| 1 | 1 | 3 | 3 |
+------+------+------+-----*/
-- 9 rows in set
-- 3. 别名表通过t1.id >= t2.id 关联
select * from tt as t1, tt as t2 where t1.id >= t2.id;
/*-----+------+------+------+
| id | xx | id | xx |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 1 | 1 |
| 3 | 3 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 3 | 3 | 2 | 2 |
| 3 | 3 | 3 | 3 |
+------+------+------+-----*/
-- 6 rows in set
-- 4. 以上结果集以t1.id 分组,累加t2.xx
select t1.id, sum(t2.xx)
from tt as t1, tt as t2
where t1.id >= t2.id
group by t1.id;
/*-----+------------+
| id | sum(t2.xx) |
+------+------------+
| 1 | 1 |
| 2 | 3 |
| 3 | 6 |
+------+-----------*/
-- 3 rows in set
-- select 每扫描一行,就会根据t1.id 将记录放到不同的组里
-- 最后聚合函数sum 会对各组的xx 值进行求和
SQLite 窗口函数
# 下载SQLite3
wget https://www.sqlite.org/2020/sqlite-tools-osx-x86-3310100.zip
# 安装SQLite3
unzip sqlite-tools-osx-x86-3310100.zip
cd sqlite-tools-osx-x86-3310100/
# 创建数据库testdb 并进入SQLite
./sqlite3 test
-- 以逗号作为分隔符
.separator ","
-- 清空sales 表的数据
delete from sales;
-- 导入数据到sales 表
.import '/tmp/sales.txt' sales
-- 设置查询结果为列模式
.mode column
-- 显示列名
.headers on
-- 查看sales 表的数据
select * from sales;
-- 执行窗口函数
select
country,
count(*) OVER w as '窗口行总数'
from sales
group by country
window w as (rows between current row and UNBOUNDED FOLLOWING);
/*
country 窗口行总数
---------- ----------
Finland 3
India 2
USA 1
*/
参考文档
Window Function Concepts and Syntax
Window Function Frame Specification
SQLite Window Function