MySQL8.0新特性概述
MySQL8.0新增特性
MySQL8.0移除的旧特性
新特性1：窗口函数
窗口函数的分类
MySQL8.0版本开始支持窗口函数，窗口函数的作用类似于在查询过程中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条记录中
窗口函数分为静态和动态
- 静态窗口函数：窗口大小是固定的，不会因记录的不同而不同
- 动态窗口函数：窗口大小会随着记录的不同而变化
语法格式
函数 OVER (| PARTITION BY 字段名 ORDER BY 字段名 ASC | DESC);
或是
函数 OVER 窗口名 _WINDOW 窗口名 AS (| PARTITION BY 字段名 ORDER BY 字段名 ASC | DESC);
举例说明
序号函数
- ROW_NUMBER()函数
能够对数据的序号进行顺序排序，在表中第一行添加一列字段（从1—n）
#查询每个商品分类下价格从高到低的商品信息
SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS ROW_NUM,
# 序号函数 分类 排序 添加字段的别名
要查询的字段
from goods;#要查询的表
#结果：将category_id相同的放在一起，并编号到1—n(每一类重新排序)
- RANK()函数
#查询每个商品分类下价格从高到低的商品信息
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS ROW_NUM,
要查询的字段
from goods;
#结果：将category_id相同的放在一起，并编号到1—n(每一类重新排序)
#但是当价格一样时，序号会是一样的，eg:1、2、2、4
- DENSE_RANK()函数
#查询每个商品分类下价格从高到低的商品信息
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS ROW_NUM,
要查询的字段
from goods;
#结果：将category_id相同的放在一起，并编号到1—n(每一类重新排序)
#但是当价格一样时，序号会是一样的，eg:1、2、2、3
分布函数
- PERCENT_RANK()函数
计算方式：(rank-1) / (rows-1)
#查询表中category_id = 1的商品类别下的PERCENT_RANK的值
SELECT RANK() OVER w AS r,
PERCENT_RANK() OVER w AS pr,
要查询的字段
WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC) AS pr;
#此题的比率是序号-1 / 总数-1；
- CUME_DIST()函数
主要是查询小于或是等于某个数的值
#查询goods数据表下小于或是等于当前价格的比例
SELECT CUME_DIST() OVER (PARTITION BY category_id ORDER BY price ASC) AS cd,
要查询的字段
FROM goods;
#当前数据小于、等于全部数据的的概率
前后函数
- LAG(expr,n)函数（前）
#查询前一个商品的价格于当前价格的查值
SELECT 基本信息，price - pre_price AS diff_price
FROM (SELECT 基本信息，LAG(expr,1) OVER w AS pre_price#子查询：多出一列是上一行的价格
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price ASC))t;
- LEAD(expr,n)函数（后）
和前面的几乎一样
首位函数
- FIRST_VALUE(expr)函数
FIRST_VALUE(expr)函数返回第一个expr的值
#按照价格排序，查询第一个商品的价格信息
SELECT 基本信息，FIRST_VALUE(price) OVER w AS first_price
FROM goods WINDOW w AS (ARTITION BY category_id ORDER BY price ASC)
#新增加的列是数据是该分类下的第一行价格数据
- LAST_VALUE(expr)函数
LAST_VALUE(expr)函数返回最后一个expr的值
其他函数
- WTH_VALUE(expr,n)函数
WTH_VALUE(expr,n)返回函数第n个expr的值
- NTILE(n)函数
NTILE(n)将每一类平均进行n组
小结
窗口函数可以对数据进行分组、排序，并且不会减少由表中的行数，对统计和排序非常有用
公用表表达式
普通公用表表达式
- 语法结果
WITH CTE名称
AS(子查询)
SELECT | DELETE | UPDATE 语句;
- 举个例子
#查询员工所在部门的详细信息
方式一：子查询实现
SELECT *
FROM departments
WHERE department_id IN (SELECT DISTINCT department_id
FROM departments
);
方式二:CTE实现
WITH CIE_emp t2
AS (SELECT DISTINCT department_id FROM departments)#我感觉是创建了一个临时的新表
SELECT *
FROM departments t1 JOIN CIE_emp t2#多表连接了
WHERE t1.department_id = t2.department_id;
递归公用表表达式
递归;自己调用自己
- 语法格式：
WITH RECURSIVE
CTE名称 AS(子查询)
SELECT | DELETE | UPDATE 语句;
- 举个例子
#查询所有的下下属
WITH PECURSIVE cte
AS
(
SELECT 基本信息，1 AS n FROM employees WHERE id = 100 #初始值
UNION ALL
SELECT 基本信息，n+1 FROM employees AS a JOIN cte
ON a.id = cte.id#递归调用
)
SELECT 基本信息
FROM cte
WHERE n > 3;
原文地址：https://www.cnblogs.com/wht-de-bk/archive/2022/03/22/16041362.html
