SQL高级处理
1 窗口函数
1.1 窗口函数概念及基本的使用方法
窗口函数也称为OLAP函数,对数据库数据进行实时分析处理,可以让我们有选择的去某一部分数据进行汇总、计算和排序,基础语法:
<窗口函数> OVER ([ PARTITION BY <列名> ]
[ ORDER BY <排序用列名> ])
/*
[ ]中的内容可以省略。
PARTITON BY 子句 和 ORDER BY 子句 都是可选参数,但是两个参数不能同时没有(最少二选一)
PARTITON BY 子句 可选参数,指示如何将查询行划分为组,类似于 GROUP BY 子句的分组功能,但是 PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能,并不会改变原始表中记录的行数。
ORDER BY 子句 可选参数,指示如何对每个分区中的行进行排序,即决定窗口内,是按哪种规则(字段)来排序的。
2 窗口函数种类
-
将SUM、MAX、MIN等聚合函数用在窗口函数中
-
RANK、DENSE_RANK等排序用的专用窗口函数
2.1 专用窗口函数
- RANK函数
计算排序时,如果存在相同位次的记录,则会跳过之后的位次。
例)有 3 条记录排在第 1 位时:1 位、1 位、1 位、4 位……
- DENSE_RANK函数
同样是计算排序,即使存在相同位次的记录,也不会跳过之后的位次。
例)有 3 条记录排在第 1 位时:1 位、1 位、1 位、2 位……
- ROW_NUMBER函数
赋予唯一的连续位次。
例)有 3 条记录排在第 1 位时:1 位、2 位、3 位、4 位
2.2 聚合函数用在窗口函数
使用方法和之前的专用窗口函数一样,只是出来的结果是一个累计的聚合函数值,函数语义是什么就返回什么值。
3 应用 - 计算移动平均
还可以指定更加详细的汇总范围。该汇总范围称为 框架 (frame)。
基础语法:
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS n PRECEDING )
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS BETWEEN n PRECEDING AND n FOLLOWING)
PRECEDING(“之前”), 将框架指定为 “截止到之前 n 行”,加上自身行
FOLLOWING(“之后”), 将框架指定为 “截止到之后 n 行”,加上自身行
例子1(指定前两行):
SELECT product_id
,product_name
,sale_price
,AVG(sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM product;
结果
例子2(指定当前行和前一行和后一行):
SELECT product_id
,product_name
,sale_price
,AVG(sale_price) OVER (ORDER BY product_id
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM product;
结果
3.1 窗口函数适用范围和注意事项
- 因为窗口函数是对where或者group by子句处理后的结果进行操作,原则上,窗口函数只能在SELECT子句中使用。
- 窗口函数OVER 中的ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。
4 GROUPING运算符
4.1 ROLLUP - 计算合计及小计
常规的GROUP BY 只能得到每个分类的小计,有时候还需要计算分类的合计,可以用 ROLLUP关键字,ROLLUP 可以对多列进行汇总求小计和合计。
例子
SELECT product_type
,regist_date
,SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type, regist_date WITH ROLLUP;
个人解析:通过 product_type和regist_date使用GROUP BY
进行分组最后使用ROLLUP
关键字进行合计
结果:
正式解析
ROLLUP 对product_type, regist_date两列进行合计汇总。结果实际上有三层聚合,如下图 模块3是常规的 GROUP BY 的结果,需要注意的是衣服 有个注册日期为空的,这是本来数据就存在日期为空的,不是对衣服类别的合计; 模块2和1是 ROLLUP 带来的合计,模块2是对产品种类的合计,模块1是对全部数据的总计。
5 存储过程和函数
5.1 基本介绍
基本语法
[delimiter //]($$,可以是其他特殊字符)
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...]
[BEGIN]
routine_body
[END//]($$,可以是其他特殊字符)
- 创建表(示例)
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `product_test`()
BEGIN
#Routine body goes here...
CREATE TABLE product_test like shop.product;
END$$
- 插入数据(示例)
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_product_test`()
BEGIN
declare i int;
set i=1;
while i<9 do
set @pcid = CONCAT('000', i);
PREPARE stmt FROM 'INSERT INTO product_test() SELECT * FROM shop.product where product_id= ?';
EXECUTE stmt USING @pcid;
set i=i+1;
end while;
END
6 预处理声明 PREPARE Statement
MySQL 从4.1版本开始引入了 PREPARE Statement
特性,使用 client/server binary protocol
代替 textual protocol
,其将包含占位符 () 的查询传递给 MySQL 服务器,如以下示例所示:
SELECT *
FROM products
WHERE productCode = ?;
当MySQL使用不同的 productCode
值执行此查询时,它不必完全解析查询。因此,这有助于MySQL更快地执行查询,特别是当MySQL多次执行相同的查询时。productcode
由于预准备语句使用占位符 (),这有助于避免 SQL 注入的许多变体,从而使应用程序更安全。
基本语法
PREPARE stmt_name FROM preparable_stmt
执行示例:
练习题
5.1
请说出针对本章中使用的 product(商品)表执行如下 SELECT 语句所能得到的结果。
SELECT product_id
,product_name
,sale_price
,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
FROM product;
按指定的排序:product_id,返回当前所在行及之前所有的行的最大的sale_price,得到新列Current_max_price
5.2
继续使用product表,计算出按照登记日期(regist_date)升序进行排列的各日期的销售单价(sale_price)的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位(也就是将其看作比其他日期都早)
代码
SELECT
` regist_date`,
sum( ` sale_price` ) over ( PARTITION BY ` regist_date` ) AS sale_sum
FROM
product
ORDER BY
` regist_date`;
结果
5.3
思考题
① 窗口函数不指定PARTITION BY的效果是什么?
partition by相比较于group by,能够在保留全部数据的基础上,只对其中某些字段做分组排序(类似excel中的操作),而group by则只保留参与分组的字段和聚合函数的结果(类似excel中的pivot)。
② 为什么说窗口函数只能在SELECT子句中使用?实际上,在ORDER BY 子句使用系统并不会报错。
SQL语句的执行顺序,(FROM->WHERE->GROUP BY -> HAVING->SELECT->ORDER BY),如果在WHERE、GROUP BY、HAVING子句中使用窗口函数,则会先进行一次排序,如果在 WHERE, GROUP BY, HAVING 使⽤了窗口函数,就是说提前进行了⼀次排序,排序之后再去除记录、汇总、汇总过滤,第⼀次排序结果就是错误的,没有实际意义。而 ORDER BY 语句执行顺序在SELECT 语句之后,就可以使用。
5.4
使用存储过程创建20个与 shop.product
表结构相同的表,如下图所示:
代码
DELIMITER $$
USE `shop`$$
DROP PROCEDURE IF EXISTS `product_test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `product_test`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
SET i = 0;
WHILE i<20 DO
#为了使表名成为xxx00这样的格式加的条件判断
IF i<0 THEN
SET table_name = CONCAT('table',i);
ELSE
SET table_name = CONCAT('tablex',i);
END IF;
SET @csql = CONCAT(
'CREATE TABLE ',table_name,
'(
`product_id` CHAR(4) NOT NULL PRIMARY KEY,
`product_name` VARCHAR(100),
`product_type` VARCHAR(32),
`sale_price` INT,
`purchase_price` INT,
`regist_date` DATE
)ENGINE=Innodb default charset=utf8;'
);
PREPARE create_stmt FROM @csql;
EXECUTE create_stmt;
SET i = i+1;
END WHILE;
END$$
DELIMITER ;
CALL product_test(); SET i = i+1;
END WHILE;
END $$
DELIMITER ;
结果
这里我把循环语句写成i小于10了
正确的应该是
WHILE i<20 DO
#为了使表名成为xxx00这样的格式加的条件判断
IF i<0 THEN
SET table_name = CONCAT('table',i);
ELSE
SET table_name = CONCAT('tablex',i);
END IF;
总结
本次练习主要学习了SQL语句的一些高级处理,包括但不限于窗口函数的含义及应用、GROUPING运算符的用法,预处理声明 PREPARE Statement,以及快速创建多表等内容。
249)]
[外链图片转存中…(img-jVoxRVJM-1690910882249)]
这里我把循环语句写成i小于10了
正确的应该是
WHILE i<20 DO
#为了使表名成为xxx00这样的格式加的条件判断
IF i<0 THEN
SET table_name = CONCAT('table',i);
ELSE
SET table_name = CONCAT('tablex',i);
END IF;
总结
本次练习主要学习了SQL语句的一些高级处理,包括但不限于窗口函数的含义及应用、GROUPING运算符的用法,预处理声明 PREPARE Statement,以及快速创建多表等内容。