Task 06 SQL高级处理
Content
- 窗口函数
- GROUPING运算符
- 存储过程和参数
- 预处理声明PREPARE Statement
Reference：
[1]GitHub - datawhalechina/wonderful-sql: Follow me，从 0 到 1 掌握 SQL，决胜秋招。
[2]MySQL：Task05-窗口函数和存储过程_Shirell的博客-CSDN博客
[3]MySQL删除存储过程（DROP PROCEDURE） (biancheng.net)
[4]mysql-使用存储过程一次性批量创建多张表__YuanXin的博客-CSDN博客_mysql 批量创建表
[5]MySQL 使用存储实现快速创建多个分表_bkhech的博客-CSDN博客_mysql创建多个表
[6]mysql存储过程:delimiter定界符导致You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near - mysql常见问题 - 谷谷点程序 (3qphp.com)
[7]sql语句错误代码[Err]1064_FerrymanJACK的博客-CSDN博客_err1064
1. 窗口函数
窗口函数也称为 OLAP 函数。OLAP 是 OnLine AnalyticalProcessing 的简称，意思是对数据库数据进行实时分析处理。
为了便于理解，称之为窗口函数。常规的 SELECT 语句都是对整张表进行查询，而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。
<窗口函数> OVER ([PARTITION BY <列名>]
ORDER BY <排序用列名>)
- PARTITION BY用来分组，即选择要看哪个窗口，类似于GROUP BY 子句的分组功能，但是 PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。
- ORDER BY用来排序，即决定窗口内，是按那种规则 (字段) 来排序的，与 SELECT 语句末尾的 ORDER BY 一样，可以通过关键字**ASC/DESC **来指定升序/降序。省略该关键字时会默认按照 ASC
- 原则上，窗口函数只能在 SELECT 子句中使用。窗口函数 OVER 中的 ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算
用一个形象的栗子来理解：
SELECT product_name
,product_type,sale_price
,RANK() OVER (PARTITION BY product_type
ORDER BY sale_price) AS ranking
FROM product;
得到的结果是：
6.1 窗口函数的种类
大致来说分为：
- 专用窗口函数：RANK、DENSE_RANK等
- 聚合函数：SUM、MAX、MIN等
1.1.2 专用窗口函数
RANK 函数（英式排序）
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……DENSE_RANK函数（中式排序）
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……ROW_NUMBER 函数
赋予唯一的连续位次。
例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位
1.1.2 聚合函数在窗口函数上的使用
得到一个累计的聚合函数值
SELECT product_id
,product_name
,sale_price
#以id排序，计算累计售价和，同理AVG
,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
FROM product;
相关说明
- 聚合函数结果是，按我们指定的排序，当前所在行及之前所有的行的合计或均值。即累计到当前行的聚合。
- 还可以指定更加详细的汇总范围。该汇总范围成为框架 (frame)
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS n PRECEDING )
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS BETWEEN n PRECEDING AND n FOLLOWING)
相关说明
- PRECEDING（“之前”），将框架指定为“截止到之前 n 行”，加上自身行
- FOLLOWING（“之后”），将框架指定为“截止到之后 n 行”，加上自身行
- BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为“之前 1 行”+“之后 1 行”+“自身”
- 注意观察框架的范围。
用一个形象的栗子来理解：
/*ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：*/
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;
2.GROUPING 运算符
2.1 ROLLUP - 计算合计及小计
常规的 GROUP BY 只能得到每个分类的小计，有时候还需要计算分类的合计，可以用 ROLLUP 关键字
SELECT product_type
,regist_date
,SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type, regist_date WITH ROLLUP
这里 ROLLUP 对 product_type, regist_date 两列进行合计汇总。结果实际上有三层聚合，如下图模块 3是常规的 GROUP BY 的结果，需要注意的是衣服有个注册日期为空的，这是本来数据就存在日期为空的，不是对衣服类别的合计；模块 2 和 1 是 ROLLUP 带来的合计，模块 2 是对产品种类的合计，模块 1 是对全部数据的总计。
3.存储过程和函数
（SQL竟然也可以定义函数
3.1 基本语法
[delimiter //]($$，可以是其他特殊字符）
DROP PROCEDURE [IF EXISTS] <过程名> #删除数据库中已经存在的存储过程
CREATE
- [DEFINER = user]
- PROCEDURE sp_name ([proc_parameter[,...]])
- [characteristic ...]
[BEGIN]
- routine_body #由一个有效的 SQL 例程语句组成
[END//]($$，可以是其他特殊字符）
相关说明
- 过程名：指定要删除的存储过程的名称。删除后可以通过查询information_schema数据库下的routines表来确定上面的删除是否成功：
SELECT * FROM information_schema.routines
WHERE routine_name='过程名';
#出现Empty set就是成功了
- 创建：以上语句被用来创建一个存储例程（一个存储过程或函数）。也就是说，指定的例程被服务器知道了。默认情况下，一个存储例程与默认数据库相关联。要将该例程明确地与一个给定的数据库相关联，需要在创建该例程时将其名称指定为 db_name.sp_name
- 调用：使用 CALL 语句调用一个存储过程。而要调用一个存储的函数时，则要在表达式中引用它。在表达式计算期间，该函数返回一个值
- routine_body：可以是一个简单的语句，如 SELECT 或 INSERT，或一个使用 BEGIN 和 END 编写的复合语句。复合语句可以包含声明、循环和其他控制结构语句。在实践中，存储函数倾向于使用复合语句，除非例程主体由一个 RETURN 语句组成
- 存储过程和函数的参数有三类，分别是：IN，OUT，INOUT
- IN ：入参
- 每个参数默认都是一个 IN 参数。如需设定一个参数为其他类型参数，请在参数名称前使用关键字 OUT或 INOUT
- 一个 IN 参数将一个值传递给一个过程。存储过程可能会修改这个值，但是当存储过程返回时，调用者不会看到这个修改
- OUT：出参
- 一个 OUT 参数将一个值从过程中传回给调用者
- 它的初始值在过程中是 NULL ，当过程返回时，调用者可以看到它的值
- INOUT
- 一个 INOUT 参数由调用者初始化，可以被存储过程修改，当存储过程返回时，调用者可以看到存储过程的任何改变
- IN ：入参
示例不一一列举了
4.预处理声明 PREPARE Statement
PREPARE和上面的例子联动了，那我浅举一个
/*插入数据*/
CREATE DEFINER=root@localhost PROCEDURE insert_product_test()
BEGIN
declare i int;
set i=1;
while i<9 do
set @pcid = CONCAT(’000’, i); #声明变量 pcid，代表商品编号，并将其值设置为 000i：
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
最后应释放预处理声明以释放其占用的资源：
DEALLOCATE PREPARE stmt1;
练习
- 请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。
SELECT product_id,product_name,sale_price
,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
FROM product;
ANS:
- 首先看到有OVER那就是搭建了一个窗口，这个窗口内的排序是按照product_id来排序的，其次我们看它筛选出了啥：
- 前三列是product_id,product_name,sale_price，最后一列是聚合函数MAX在窗口函数上的使用，Current_max_price得到的是——按我们指定的排序，当前所在行及之前的所有的行中的最大值，即累计到当前行的最大值。
- 继续使用 product 表，计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。排序是需要将登记日期为 NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早）
SELECT
DISTINCT regist_date
,SUM(sale_price)
OVER (PARTITION BY regist_date
ORDER BY regist_date) AS sum_price
FROM product;
- 思考题
- 窗口函数不指定 PARTITION BY 的效果是什么？
- 为什么说窗口函数只能在 SELECT 子句中使用？实际上，在 ORDER BY 子句使用系统并不会报错。
ANS:
- 不加PARTITION BY就没有分组。对于专用窗口函数就会将整个表当作一个窗口；对于聚合函数则将得到一个累计的聚合函数值，即按我们指定的排序，当前所在行及之前所有的行的<聚合操作>。即累计到当前行的聚合。
- 窗口函数原则上只能在SELECT子句中使用，是因为窗口函数是对WHERE或GROUP BY子句处理后的结果进行操作
-SQL执行顺序：FROMWHERE
可以看出：ORDER BY子句将在SELECT后执行，并不会减少最后检索的记录（窗口函数 OVER 中的 ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算）（UPDATE 中的SET子句中也能够使用窗口函数）
-
使用简洁的方法创建 20 个与 shop.product 表结构相同的表，如下图所示：
问题4.png
第一次学习怎么写存储过程，因此把所有可省略的都保留了
[DELIMITER //] # 空格后再加//不然会报错误 ①
[USE shop //] #USE 'DBName' // # 数据库名不需要用引号括起来
DROP PROCEDURE IF EXISTS create_multi_table // #过程名不需要用引号括起来不然会报错误 ①
CREATE [DEFINER = root@localhost] #DEFINER = 'root'@'%'
PROCEDURE create_multi_table(IN table_counts int) #也不需要加引号！！
/*或者更简单的写法
DROP PROCEDURE IF EXISTS shop.create_multi_table
CREATE PROCEDURE shop.create_multi_table(IN table_counts int)
*/
[BEGIN]
DECLARE i int;
DECLARE table_name varchar(50); #漏了这个会报错误 ②
SET i = 0;
WHILE i<table_counts do
SET table_name = CONCAT('table_test',i);
SET @ctbl = CONCAT('CREATE TABLE',table_name,'LIKE','shop.product;'); #声明变量
PREPARE create_stmt FROM @ctbl; #预处理声明
EXECUTE create_stmt; #执行预处理声明
SET i=i+1;
END WHILE;
[END//]
# 调用
DELIMITER;
CALL create_multi_table(20);
#删除程序
DROP PROCEDURE create_multi_table;
检查存储过程：
- 为显示用来创建一个存储过程的CREATE语句
SHOW CREATE PROCEDURE create_multi_table;
- 为了获得包括何时、由谁创建等详细信息的存储过程列表
SHOW PROCEDURE STATUS;
-- 发现此时出来的表很多，可以选择用like过滤一些不需要的信息
SHOW PROCEDURE STATUS LIKE ' create_multi_table';
过程中可能会报错的类型：
①ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''
错误原因是没有修改定界符。
默认情况下，delimiter"；"用于向mysql提交查询语句。在执行存储过程</a>中每个语句的结尾都有个"；"，这个时候每逢"；"就像mysql提交的话，当然会出问题。于是在写存储过程代码之前，先修改mysql的存储定界符delimiter，等存储过程执行之后，在修改回"；"
比如修改为"//"；
代码示例：delimiter//
而且DELIMITER后面要加空格！！
②ERROR 1193 (HY000): Unknown system variable
错误原因可能是：
(1) sql 语句中的字段和MySQL数据库关键字冲突，如update 、create等，这个时候可以用``将字段括起来(键盘1左边那个)
(2) sql语句标点错误，如单双引号误写，分号中文；
(3) 格式错误/未声明格式;
(4) 拼sql语句的时候，拼接的两部分之间没有区分开(concat)。
