Mysql8.0 新特性 窗口函数 公共表表达式

2024-08-06 14:49:37 浏览数 (1)

Mysql8.0 新特性 窗口函数 公共表表达式

MySQL 5.7 到 8.0，Oracle 官方跳跃了 Version 版本号

  • 随之而来的就是在 MySQL 8.0 上做了许多重大更新，在往企业级数据库的路上大步前行
  • 全新 Data Dictionary 设计，支持 Atomic DDL，全新的版本升级策略，安全和账号管理加强，InnoDB 功能增强等。
  • 最突出的一点是多MySQL Optimizer优化 器进行了改进， 不仅在速度上得到了改善，还为用户带来了更好的性能和更棒的体验。

这里就不一一介绍了，就先介绍几个简单常用的窗口函数 公共表表达式, 感觉挺高级常用的，帮助快速开发.

准备工作:

本节操作的表，提供：

代码语言：javascript复制
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `category_id` int(0) NULL DEFAULT NULL COMMENT '商品类型ID',
  `category` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商品类型',
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商品名称',
  `price` decimal(10, 2) NULL DEFAULT NULL COMMENT '价格',
  `stock` int(0) NULL DEFAULT NULL COMMENT '库存',
  `upper_time` datetime(0) NULL DEFAULT NULL COMMENT '上架时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


INSERT INTO `goods` VALUES (1, 2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (2, 1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (3, 3, '电子设备', '华为手机', 3200.00, 100, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (4, 2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (5, 1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (6, 1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (7, 2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (8, 3, '电子设备', '平板', 2000.00, 300, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (9, 2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (10, 3, '电子设备', '显示器', 1000.00, 500, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (11, 2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (12, 1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (13, 2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (14, 1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (15, 3, '电子设备', '小米手机', 3100.00, 100, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (16, 1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (17, 3, '电子设备', '笔记本', 500.00, 1200, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (18, 3, '电子设备', '手机支架', 500.00, 1200, '2020-11-10 00:00:00');
INSERT INTO `goods` VALUES (19, 3, '电子设备', 'U盘', 100.00, 1200, '2020-11-10 00:00:00');

窗口函数：

Mysql8.0 开始支持窗口函数 官方地址 隔壁Oracle Db2 好像一直都有...

  • 窗口函数也称为OLAP函数 OnLine Analytical Processing 联机分析处理 功能很强大，可以帮我们做很多事情.
  • 窗口函数的作用类似于在查询中对数据进行分组：✨ 但，不同的是分组操作，并不会把分组后的结果合并成一条记录，窗口函数将结果，置于每一条记录中. 可以更加方便的进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。 让我们快来了解吧!

窗口函数，可以分为 静态窗口函数 动态窗口函数

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

语法结构：

代码语言：javascript复制
窗口函数 OVER ([PARTITION BY 窗口列清单] ORDER BY 排序列清单 ASC|DESC)

-- 在查询的时候,窗口函数列,就想是一个单独的结果集一样,将查询的结果集单独的进行分组排序,返回的一个新的列,而不会对原SELECT结果集改变.

或

代码语言：javascript复制
窗口函数 OVER 窗口名
WINDOW 窗口名 AS ([PARTITION BY 窗口列清单] ORDER BY 排序列清单 ASC|DESC)

-- 为了可以方便查看|复用,可以在查询 WHERE Group By...之后，WINDOW声明定义窗口, 方便上面SELECT 上窗口函数直接引用;

OVER() 关键字指定窗口函数的，范围：

  • 若后面括号中什么都不写,则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算。
    • 如果不为空,则支持以下4中语法来设置窗口 window_name窗口名 partition by 窗口分组 order by 窗口排序 frame 滑动窗口
  • 窗口名： 为窗口设置一个别名，用来标记窗口，如果SQL中针对这个窗口,使用频繁采用别名,可以更加清新方便复用 调用
  • Partition by 分组： 按指定字段进行分组，分组后，可以在每个分组中分别执行。
  • Order by 排序： 指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号
  • Frame 子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用

常用窗口函数：

序号函数：

ROW_NUMBER()
  • ROW_NUMBER()函数能够对数据中的序号进行顺序显示
代码语言：javascript复制
-- 窗体函数 ROW_NUMBER();
-- 就相当于窗体中每一行记录,下标行号,表示当前行数据对于窗体的第几行;
SELECT  
	ROW_NUMBER() OVER() as rownum,	-- 设置表查询结果集行号列名 AS rownum;
	god.*
FROM Goods AS god;
	-- 因为OVER()是设置窗体的,如果什么都没控制则默认是整个结果集是一个窗体;
	
-- 窗体函数最大的特点是基于 OVER(); 设置窗体大小范围在通过窗口函数进行各种复杂聚合操作,很是方便;
	-- 查询每个商品类型进行分组并标记行号
	SELECT 
		ROW_NUMBER() OVER(PARTITION BY category_id) AS rownum,	-- 基于商品类型进行分组,ROW——NUMBER()每一个窗口内计算行号;
		category_id,category,name,price,stock,upper_time
	FROM Goods;

上面应该大致了解了窗体函数:

通过,OVER(…) 可以对结果集进行,分组成为一个个窗口,使用对应的窗口函数,可以对窗口中行进行操作,并将返回结果返回到一个列上

代码语言：javascript复制
-- 仔细思考ROW_NUMBER还可以做很多事情:
-- 查询每个商品分类价格前三的商品:
SELECT * FROM(
	SELECT 
    	-- 基于商品类型进行分组,ROW——NUMBER()每一个窗口内计算行号;
		ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS rownum,	
		category_id,category,name,price,stock,upper_time
	FROM Goods
) A 
WHERE A.rownum <= 3

	-- 子查询,窗口函数根据商品ID进行分组,并通过商品价格进行降序排序,设置行号,行号越小当然就价格约大！
	-- 外部查询只需要根据行号 <= 3 就可以知道,商品类型价格排名前三的商品了！太简单了！

窗口函数yyds，如果没有窗口函数，上面查询分类价格前三的商品如何查询呢？ 就很复杂了.

代码语言：javascript复制
-- 外层查询根据SELECT * FROM 表 遍历每一个结果进入子查询：
-- 将每一行的结果带进子查询,查询符合条件的记录数：商品类型一样 且 子查询价格>外层子查询价格
-- 影响行数>3 因为最大的价格影响行数也是最大一点大于三
SELECT * FROM goods g1
WHERE (
		SELECT COUNT(1) FROM goods g2
		WHERE g2.category_id = g1.category_id AND g2.price > g1.price
      ) <3
ORDER BY category_id,price DESC 	-- 最后排个序,方便查看

如此一看窗口函数确实方便了很多, 让我们继续深入了解吧！

RANK()

和 ROW_NUMBER() 类型，也是一种序号函数：

RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3

  • 对于排序相同的值，序号是一样的，同时后面的序号会跳过当前的序号. 后面的商品序号是不连续的.
  • 业务场景： 比如班级考试，相同分数的同学应该是并列第一，而第三个同学有时候是第二还是第三呢？有的情况下会认为他是第三名就出现了排名：1、1、3
代码语言：javascript复制
-- 使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息
-- 并进行排序：
-- 	相同价格的商品并列排序,后面的商品排名跳过.
SELECT *
FROM(
    -- RANK 和 ROW_NUMBER 都是排序函数,不同的是排序手法;
	SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,	
	id, category_id, category, NAME, price, stock
	FROM goods
) A
WHERE category_id = 1 AND row_num <= 4;
DENSE_RANK()

DENSE_RANK() 函数和 RANK() 函数类似,相同值的顺序会并列排序，但不同的是，后面的顺序不会跨值，而是继续的顺序下去.

  • 业务场景： 班级考试，相同分数的同学应该是并列第一，而第三个同学分数有人认为应该是第二就出现了排名：1、1、2 的情况
代码语言：javascript复制
SELECT *
FROM(
    -- 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
) A
WHERE category_id = 1 AND row_num <= 4;

分布函数：概率统计

PERCENT_RANK()

**函数是等级值百分比函数。按照如下方式进行计算 ** (rank - 1) / (rows - 1) 这是一种概率统计论中的一种算法, 实际场景使用较少，了解即可

数据 统计 递归 函数 排序

0 人点赞