MySQL——8.0新特性之一窗口函数

窗口函数

窗口函数的好处：数据分组后不聚合

1、语法

窗口函数的语法结构是：

函数名() 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子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。

2、常用的窗口函数

序号函数

2.1 ROW_NUMBER()函数

• 对数据中的序号进行顺序显示

# 举例：查询 goods 数据表中每个商品分类下价格最高的3种商品信息
SELECT *
FROM (
	SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
	id, category_id, category, NAME, price, stock
	FROM goods) as t
WHERE row_num <= 3;

2.2 RANK()函数

• 对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3

# 举例：使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息
SELECT *
FROM(
	SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
	id, category_id, category, NAME, price, stock
	FROM goods) as t
WHERE category_id = 1 AND row_num <= 4;

2.3 DENSE_RANK()函数

• 对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2

# 举例：使用DENSE_RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息
SELECT *
FROM(
	SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
	id, category_id, category, NAME, price, stock
	FROM goods) t
WHERE category_id = 1 AND row_num <= 3;

分布函数

2.4 PERCENT_RANK()函数

• 等级值百分比函数。按照如下方式进行计算
  • (rank - 1) / (rows - 1)，其中，rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数

# 举例：计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值
# 写法一：
SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,
PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1;


# 写法二：
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);

2.5 CUME_DIST()函数

• 用于查询小于或等于某个值的比例

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

前后函数

2.6 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)
) as t;

2.7 LEAD(expr,n)函数

• 返回当前行的后n行的expr的值

# 举例：查询goods数据表中后一个商品价格与当前商品价格的差值
SELECT id, category, NAME, behind_price, 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)
) as t;

首尾函数

2.8 FIRST_VALUE(expr)函数

• 返回第一个expr的值

# 举例：按照价格排序，查询第1个商品的价格信息
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);

2.9 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);

其他函数

2.10 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);

2.11 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);

参考：尚硅谷-宋红康老师的视频及课件。