一、窗口函数概述

??MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。

窗口函数可以分为静态窗口函数和动态窗口函数。

• 静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同；
• 动态窗口函数的窗口大小会随着记录的不同而变化。

窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数，如下表：

二、窗口函数的语法结构

函数 OVER([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
函数 OVER 窗口名 … WINDOW 窗口名 AS ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])

• OVER 关键字指定函数窗口的范围。
  • 如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算。
  • 如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。
• 窗口名：为窗口设置一个别名，用来标识窗口。
• PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。
• ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
• FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。

三、窗口函数的分类讲解

创建表：

CREATE DATABASE test16;
USE test16;

CREATE TABLE goods(
	id INT PRIMARY KEY AUTO_INCREMENT,
	category_id INT,
	category VARCHAR(15),
	NAME VARCHAR(30),
	price DECIMAL(10,2),
	stock INT,
	upper_time DATETIME
);

添加数据：

INSERT INTO goods(category_id,category,NAME,price,stock,upper_time)
VALUES (1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
       (1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
       (1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'),
       (1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'),
       (1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
       (1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'),
       (2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'),
       (2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'),
       (2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
       (2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'),
       (2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'),
       (2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');
       
SELECT * FROM goods;

3.1、序号函数

• ROW_NUMBER()函数
  • ROW_NUMBER()函数能够对数据中的序号进行顺序显示。
• RANK()函数
  • 使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号。
• DENSE_RANK()函数
  • DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号。

example:

-- ROW_NUMBER()函数：对数据中的序号进行顺序显示
-- 查询goods数据表中每个商品分类下价格降序排列的各个商品信息
SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id,
       category_id,category,NAME,price,stock
FROM goods;

-- 使用RANK()函数：对序号进行并列排序，并且会跳过重复的序号。
-- 获取goods数据表中各类别的价格从高到低排序的商品信息
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
       id,category_id,category,NAME,price,stock
FROM goods;

-- DENSE_RANK()函数：对序号进行并列排序，并且不会跳过重复的序号
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
       id,category_id,category,NAME,price,stock
FROM goods;

3.2、分布函数

• PERCENT_RANK()函数
  • PERCENT_RANK()函数是等级值百分比函数。
    • 按照如下方式进行计算：(rank-1)/(rows-1)。
      • rank的值为使用RANK()函数产生的序号
      • rows的值为当前窗口函数的总记录数
• CUME_DIST()函数
  • CUME_DIST()函数主要用于查询小于或等于某个值的比例。

example:

-- PERCENT_RANK()函数：等级值百分比函数。
-- 计算goods数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值
SELECT RANK() OVER w AS r,PERCENT_RANK() OVER w AS pr,
       id,category_id,category,NAME,price,stock
FROM goods
WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);

-- CUME_DIST()函数：用于查询小于或等于某个值的比例
-- 查询goods数据表中小于或等于当前价格的比例
SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,
       id,category,NAME,price
FROM goods;

3.3、前后函数

• LAG(expr,n)函数
  • LAG(expr,n)函数返回当前行的前n行的expr的值。
• LEAD(expr,n)函数
  • LEAD(expr,n)函数返回当前行的后n行的expr的值。

example:

-- LAG(expr,n)函数：返回当前行的前n行的expr的值
-- 查询goods数据表中前一个商品价格与当前商品价格的差值
SELECT id,category,NAME,price,pre_price,price-pre_price AS "diff_price"
FROM (
	SELECT id,category,NAME,price,LAG(price,1) OVER w AS pre_price
	FROM goods
	WINDOW w AS(PARTITION BY category_id ORDER BY price)
) t;

-- LEAD(expr,n)函数：返回当前行的后n行的expr的值
-- 查询goods数据表中后一个商品价格与当前商品价格的差值
SELECT id,category,NAME,price,behind_price,behind_price-price AS "diff_price"
FROM (
	SELECT id,category,NAME,price,LEAD(price,1) OVER w AS behind_price
	FROM goods
	WINDOW w AS (PARTITION BY category_id ORDER BY price)
) t;

3.4、首尾函数

• FIRST_VALUE(expr)函数
  • FIRST_VALUE(expr)函数返回第一个expr的值。
• LAST_VALUE(expr)函数
  • LAST_VALUE(expr)函数返回最后一个expr的值。

example:

-- FIRST_VALUE(expr)函数：返回第一个expr的值
-- 按照价格排序，查询第一个商品的价格信息
SELECT id,category,NAME,price,stock,FIRST_VALUE(price) OVER w AS first_price
FROM goods 
WINDOW w AS (PARTITION BY category_id ORDER BY price);

-- LAST_VALUE(expr)函数：返回最后一个expr的值
-- 按照价格排序，查询最后一个商品的价格信息
SELECT id,category,NAME,price,stock,LAST_VALUE(price) OVER w AS last_price
FROM goods 
WINDOW w AS (PARTITION BY category_id ORDER BY price);

3.5、其他函数

• NTH_VALUE(expr,n)函数
  • NTH_VALUE(expr,n)函数返回第n个expr的值。
• NTILE(n)函数
  • NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。

example:

-- NTH_VALUE(expr,n)：函数返回第n个expr的值。
-- 查询goods数据表中排名第2和第3的价格信息
SELECT id,category,NAME,price,
       NTH_VALUE(price,2) OVER w AS second_price,
       NTH_VALUE(price,3) OVER w AS third_price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price);

-- NTILE(n)函数：将分区中的有序数据分为n个桶，记录桶编号
-- 将goods表中的商品按照价格分为3组
SELECT NTILE(3) OVER w AS nt,id,category,NAME,price
FROM goods
WINDOW w AS(PARTITION BY category_id ORDER BY price);

四、小结

??窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用。