窗口函数、grouping运算符、存储过程、预处理声明
1. 窗口函数
1.1. 的概念与基本使用
窗口函数也叫 OLAP(OnLine AnalyticalProcessing) 函数,，意思是对数据库数据进行实时分析处理。
select 对整张表进行查询，而窗口函数对一部分数据进行汇总、计算和排序。
基本语法：
窗口函数名 OVER (PARTITION BY 列名
ORDER BY 排序用列名)
PARTITION BY 能够设定窗口对象范围。
ORDER BY 能够指定按照哪一列、何种顺序进行排序。
两者至少选其一。
举例：
select product_name,product_type,sale_price,
rank() over (partition by product_type
order by sale_price) as ranking
from product;
product_name|product_type|sale_price|ranking|
------------+------------+----------+-------+
圆珠笔 |办公用品 | 100| 1|
打孔器 |办公用品 | 500| 2|
叉子 |厨房用具 | 500| 1|
擦菜板 |厨房用具 | 880| 2|
菜刀 |厨房用具 | 3000| 3|
高压锅 |厨房用具 | 6800| 4|
T恤 |衣服 | 1000| 1|
运动T恤 |衣服 | 4000| 2|
partition by product_type : 一个商品种类就是一个小的"窗口"。partition by 分组后得到3个集合。
order by 可以指定关键字ASC/DESC来指定升序/降序。默认ASC 升序。
1.2. 窗口函数分为两类
1.2.1. 将SUM、MAX、MIN等聚合函数用在窗口函数中
聚合函数在窗口函数使用时，计算的是累积到当前行的所有的数据的聚合
select product_id,product_name,sale_price,
sum(sale_price) over (order by product_id) as cur_sum,
avg(sale_price) over (order by product_id) as cur_avg,
max(sale_price) over (order by sale_price) as cur_max
from product;
product_id|product_name|sale_price|cur_sum|cur_avg |cur_max|
----------+------------+----------+-------+---------+-------+
0008 |圆珠笔 | 100| 16780|2097.5000| 100|
0002 |打孔器 | 500| 1500| 750.0000| 500|
0006 |叉子 | 500| 15800|2633.3333| 500|
0007 |擦菜板 | 880| 16680|2382.8571| 880|
0001 |T恤 | 1000| 1000|1000.0000| 1000|
0004 |菜刀 | 3000| 8500|2125.0000| 3000|
0003 |运动T恤 | 4000| 5500|1833.3333| 4000|
0005 |高压锅 | 6800| 15300|3060.0000| 6800|
分析：
order by product_id 求sum(sale_price)，avg(sale_price)；
接下来 order by sale_price，求max(sale_price)，不影响前面的结果。
1.2.2. RANK、DENSE_RANK、ROW_NUMBER等排序用的专用窗口函数
3个函数的区别：对于记录 [10,10,10,30]
- RANK 函数：1 位、1 位、1 位、4 位
- DENSE_RANK函数：1 位、1 位、1 位、2 位
- ROW_NUMBER函数：1 位、2 位、3 位、4 位
select product_id,product_name,sale_price,
rank() over (order by sale_price) as ranking,
dense_rank() over (order by sale_price) as dense_ranking,
row_number() over (order by sale_price) as row_numbering
from product;
product_id|product_name|sale_price|ranking|dense_ranking|row_numbering|
----------+------------+----------+-------+-------------+-------------+
0008 |圆珠笔 | 100| 1| 1| 1|
0002 |打孔器 | 500| 2| 2| 2|
0006 |叉子 | 500| 2| 2| 3|
0007 |擦菜板 | 880| 4| 3| 4|
0001 |T恤 | 1000| 5| 4| 5|
0004 |菜刀 | 3000| 6| 5| 6|
0003 |运动T恤 | 4000| 7| 6| 7|
0005 |高压锅 | 6800| 8| 7| 8|
1.3. 窗口函数应用：计算移动平均
聚合函数在窗口函数使用时，除了可以计算累计到当前行的所有数据的集合之外，还可以指定汇总范围，这个范围叫 框架 (frame)。
rows n preceding : 将框架指定为 当前n行+自身
between n preceding and n following：将框架指定为 当前n行+之后n行+自身
... over (order by 列名
rows n preceding)...
... over (order by 列名
rows between n preceding and n following)...
select product_id,product_name,sale_price,
avg(sale_price) over (order by product_id
rows 2 preceding) as moving_avg
from product;
product_id|product_name|sale_price|moving_avg|
----------+------------+----------+----------+
0001 |T恤 | 1000| 1000.0000|
0002 |打孔器 | 500| 750.0000| -- = (1000+500)/2
0003 |运动T恤 | 4000| 1833.3333| -- =(1000+500+4000)/3
0004 |菜刀 | 3000| 2500.0000| -- =(500+4000+3000)/3
0005 |高压锅 | 6800| 4600.0000|
0006 |叉子 | 500| 3433.3333|
0007 |擦菜板 | 880| 2726.6667|
0008 |圆珠笔 | 100| 493.3333|
--------------------------------------------------------------------------------------
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;
product_id|product_name|sale_price|moving_avg|
----------+------------+----------+----------+
0001 |T恤 | 1000| 750.0000| -- =(1000+500)/2
0002 |打孔器 | 500| 1833.3333| -- =(1000+500+4000)/3
0003 |运动T恤 | 4000| 2500.0000| -- =(500+4000+3000)/3
0004 |菜刀 | 3000| 4600.0000|
0005 |高压锅 | 6800| 3433.3333|
0006 |叉子 | 500| 2726.6667|
0007 |擦菜板 | 880| 493.3333|
0008 |圆珠笔 | 100| 490.0000|
窗口函数只能用于select语句中。窗口函数中的order by 不影响数据的最终排序，它只决定窗口函数按照什么顺序计算。要跟from、group by 语句之后的order by 区分开来。
2. GROUPING运算符
只使用group by子句和聚合函数是无法同时得出小计和合计的。
想要同时得到就要用grouping运算符。
- 通常的计算合计：
分别计算出合计行和按照商品种类进行汇总的结果，然后通过union all连接在一起
select '合计' as product_type, sum(sale_price)`在这里插入代码片`
from Product
union all
select product_type, sum(sale_price)
from Product
group by product_type;
product_type|sum(sale_price)|
------------+---------------+
合计 | 16780|
衣服 | 5000|
办公用品 | 600|
厨房用具 | 11180|
- 使用rollup：同时得出合计和小计
select product_type,sum(sale_price) as sum_price
from product
group by product_type with rollup;
product_type|sum_price|
------------+---------+
办公用品 | 600|
厨房用具 | 11180|
衣服 | 5000|
| 16780| -- 总计
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|sum_price|
------------+-----------+---------+
办公用品 | 2009-09-11| 500|
办公用品 | 2009-11-11| 100|
办公用品 | | 600| --小计：办公用品
厨房用具 | 2008-04-28| 880|
厨房用具 | 2009-01-15| 6800|
厨房用具 | 2009-09-20| 3500|
厨房用具 | | 11180| --小计：厨房用具
衣服 | | 4000|
衣服 | 2009-09-20| 1000|
衣服 | | 5000| --小计：衣服
| | 16780| --合计
相当于3种聚合：1.group by ()
2.group by (product_type)
3.group by (product_type, regist_date)
2.1. grouping函数的用法与三种grouping字符：rollup、cube、grouping sets
grouping函数：让null更加容易分辨。超级记录产生的null返回1，其他为0
select grouping(product_type) as product_type, grouping(regist_date) as regist_date, sum(sale_price) as sum_price from Product group by product_type, regist_date with rollup; product_type|regist_date|sum_price| ------------+-----------+---------+ 0| 0| 500| 0| 0| 100| 0| 1| 600| 0| 0| 880| 0| 0| 6800| 0| 0| 3500| 0| 1| 11180| 0| 0| 4000| 0| 0| 1000| 0| 1| 5000| 1| 1| 16780| 在超级记录分组记录的键值中插入恰当的字符串 select case when grouping(product_type) = 1 then '商品种类 合计' else product_type end as product_type, case when grouping(regist_date) = 1 then '登记日期 合计' else regist_date end as regist_date, sum(sale_price) as sum_price from Product group by product_type, regist_date with rollup; product_type|regist_date|sum_price| ------------+-----------+---------+ 办公用品 |2009-09-11 | 500| 办公用品 |2009-11-11 | 100| 办公用品 |登记日期 合计 | 600| 厨房用具 |2008-04-28 | 880| 厨房用具 |2009-01-15 | 6800| 厨房用具 |2009-09-20 | 3500| 厨房用具 |登记日期 合计 | 11180| 衣服 | | 4000| 衣服 |2009-09-20 | 1000| 衣服 |登记日期 合计 | 5000| 商品种类 合计 |登记日期 合计 | 16780|
cube：用数据来搭积木
group by cube(product_type, regist_date)
这里的cube按以下4种方式聚合：
1.group by（）
2.group by（product_type）
3.group by（regist_date)
4.group by（product_type, regist_date)
所谓cube，就是将group by子句中的聚合键的所有可能的组合的汇总结果集中到一个结果中。因此，组合的个数就是2的n次方（n为聚合键的个数）。grouping sets用于从中取出个别条件对应的不固定结果.
! mysql 8.0 不支持 cube
https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html
https://blog.csdn.net/qq_41805514/article/details/81777946
3. 存储过程和函数
原文链接：https://blog.csdn.net/Bb15070047748/article/details/106292488
存储过程是一组为了完成特定功能的sql语句，编译后存储在数据库中。用户指定存储过程的名字并给定参数来调用执行。
- 语法
CREATE PROCEDURE procedure_name ([parameters[,...]])
begin
-- SQL语句
end ;
- 示例
create procedure test1()
begin
select 'hello';
end;
-- 调用存储过程
call test1();
hello|
-----+
hello|
3.1. 存储过程的语法：declare,set,into,传递参数,if,case,while,loop,repeat,游标
- declare ：声明变量
CREATE PROCEDURE test2 ()
begin
declare num int default 0; -- 声明变量,赋默认值为0
select num+10;
end ;
call test2(); -- 调用存储过程
num+10|
------+
10|
- set：赋值操作
CREATE PROCEDURE test3 ()
begin
declare num int default 0;
set num =20; -- 给num变量赋值
select num;
end ;
call test3();
num|
---+
20|
- into：赋值
CREATE PROCEDURE test4 ()
begin
declare num int default 0;
select count(1) into num from student;
select num;
end ;
call test4();
- 传递参数
in：该参数可以作为输入，也就是需要调用方传入值 , 默认
out：该参数作为输出，也就是该参数可以作为返回值
inout：既可以作为输入参数，也可以作为输出参数
-- 定义一个输入参数
CREATE PROCEDURE test6 (in id int)
begin
declare class_name varchar(30);
if id=1 then
set class_name='哇塞，Java大佬！';
elseif id=2 then
set class_name='原来是UI的啊';
else
set class_name='不用想了，肯定是产品小样';
end if;
select class_name;
end ;
call test6(3);
class_name |
------------+
不用想了，肯定是产品小样|
-- 定义一个输入参数和一个输出参数
CREATE PROCEDURE test7 (in id int,out class_name varchar(100))
begin
if id=1 then
set class_name='哇塞，Java大佬！';
elseif id=2 then
set class_name='原来是UI的啊';
else
set class_name='不用想了，肯定是产品小样';
end if;
end ;
call test7(1,@class_name); -- 创建会话变量
select @class_name; -- 引用会话变量
@class_name|
-----------+
哇塞，Java大佬！ |
存储过程优点
1、增强sql语言的功能和灵活性，通过控制语句的编写完成复杂的判断和运算。
2、标准组件式编程，存储过程复用率高，且易于维护，无需修改应用源代码。
3、较快的执行速度，存储过程是预编译的，节省了编译时间。
4、减少网络流量，无需多次将数据传入程序运算，只需调用存储过程。大大降低网络负载。
5、限制访问权限，进一步保证数据安全。
原文链接：https://blog.csdn.net/better_zhang/article/details/121842697
公司开发不使用存储过程的原因：业务逻辑应该放到业务层，不应把业务滞留到数据库来处理，造成业务和数据库严重耦合。
4. 预处理声明 PREPARE Statement
https://www.cnblogs.com/geaozhang/p/9891338.html
1、即时 SQL
一条 SQL 在 DB 接收到最终执行完毕返回，大致的过程如下：
1. 词法和语义解析；
2. 优化 SQL 语句，制定执行计划；
3. 执行并返回结果；
如上，一条 SQL 直接是走流程处理，一次编译，单次运行，此类普通语句被称作 Immediate Statements （即时 SQL）。
2、预处理 SQL
但是，绝大多数情况下，某需求某一条 SQL 语句可能会被反复调用执行，或者每次执行的时候只有个别的值不同（比如 select 的 where 子句值不同，update 的 set 子句值不同，insert 的 values 值不同）。如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，则效率就明显不行了。
所谓预编译语句就是将此类 SQL 语句中的值用占位符替代，可以视为将 SQL 语句模板化或者说参数化，一般称这类语句叫Prepared Statements。
预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止 SQL 注入。
- MySQL PREPARE Statement 使用步骤如下：
- PREPARE – 准备需要执行的语句预处理声明。
- EXECUTE – 执行预处理声明。
- DEALLOCATE PREPARE –释放预处理声明。
-- 1.定义预处理声明
PREPARE stmt1 FROM
'SELECT
product_id,
product_name
FROM product
WHERE product_id = ?';
-- 2.声明变量 pcid，代表商品编号，并将其值设置为 0005
SET @pcid = '0005';
-- 3.执行预处理声明
EXECUTE stmt1 USING @pcid;
product_id|product_name|
----------+------------+
0005 |高压锅 |
-- 4.为变量 pcid 分配另外一个商品编号
SET @pcid = '0008';
-- 5.使用新的商品编号执行预处理声明
EXECUTE stmt1 USING @pcid;
product_id|product_name|
----------+------------+
0008 |圆珠笔 |
-- 6.释放预处理声明以释放其占用的资源
DEALLOCATE PREPARE stmt1;
5. 习题
5.1. 说出执行结果
SELECT product_id
,product_name
,sale_price
,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
FROM product;
product_id|product_name|sale_price|Current_max_price|
----------+------------+----------+-----------------+
0001 |T恤 | 1000| 1000|
0002 |打孔器 | 500| 1000|
0003 |运动T恤 | 4000| 4000|
0004 |菜刀 | 3000| 4000|
0005 |高压锅 | 6800| 6800|
0006 |叉子 | 500| 6800|
0007 |擦菜板 | 880| 6800|
0008 |圆珠笔 | 100| 6800|
按照 product_id 升序，对累计到当前行的所有sale_price求最大值。
5.2. 继续使用product表,计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额
排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早）
select product_id,product_name,sale_price,regist_date,
sum(sale_price) over (order by regist_date) as cur_sum
from product;
product_id|product_name|sale_price|regist_date|cur_sum|
----------+------------+----------+-----------+-------+
0003 |运动T恤 | 4000| | 4000|
0007 |擦菜板 | 880| 2008-04-28| 4880|
0005 |高压锅 | 6800| 2009-01-15| 11680|
0002 |打孔器 | 500| 2009-09-11| 12180|
0001 |T恤 | 1000| 2009-09-20| 16680|
0004 |菜刀 | 3000| 2009-09-20| 16680|
0006 |叉子 | 500| 2009-09-20| 16680|
0008 |圆珠笔 | 100| 2009-11-11| 16780|
5.3. 思考题
- 窗口函数不指定PARTITION BY的效果是什么？
答：不分组，则计算范围是累积到当前行的所有记录的集合。 - 为什么说窗口函数只能在SELECT子句中使用？实际上，在ORDER BY 子句使用系统并不会报错。
答：Windowed functions are defined in the ANSI spec to logically execute after the processing of GROUP BY, HAVING, WHERE.，所以窗口函数原则上只能写在select子句中。但是按照sql执行顺序，写在order by 里也可以。
https://stackoverflow.com/questions/14111321/windowed-functions-can-only-appear-in-the-select-or-order-by-clauses
5.4. 使用存储过程创建20个与 shop.product 表结构相同的表
思路：
- 使用存储过程实现，存储过程中写个20次循环，每次循环更新table名字，创建一个table。
- 因为table_name 是变量，不能直接 create table table_name，通过搜索得知可以使用CONCAT()+预处理声明 实现。
create procedure test5_4 ()
begin
declare i int;
declare table_name VARCHAR(20);
set i = 01;
while i <= 20 do
set table_name = CONCAT('table',i);
set @csql = CONCAT('create table ',table_name,' like shop.product');
prepare create_stmt from @csql;
execute create_stmt;
set i = i+1;
end while;
end ;
call test5_4();
结果发现table1-table9编号没有补零，应该为tabel01-tabel09，需要实现占位2字符。
思路：
MySQL类型转换函数参数 : CAST(xxx AS 类型) , CONVERT(xxx,类型)
这个类型 可以是以下值其中的 一个：
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED[INTEGER]
TIME
UNSIGNED [INTEGER]LPAD(str,len,padstr)
返回字符串str，将其左填充字符串padstr至len个字符的长度。 如果str大于len，则返回值缩短为len个字符。select cast('01'+1 as char); cast('01'+1 as char)| --------------------+ 2 | select LPAD(cast('01'+1 as char),2,0); LPAD(cast('01'+1 as char),2,0)| ------------------------------+ 02 | select LPAD(cast('01'+12 as char),2,0); LPAD(cast('01'+12 aschar),2,0)| -------------------------------+ 13 |
重新修改：
create procedure test5_4 ()
begin
declare i CHAR(2);
declare table_name VARCHAR(20);
set i = '01';
while i <= 20 do
set table_name = CONCAT('table',i);
set @csql = CONCAT('create table ',table_name,' like shop.product');
prepare create_stmt from @csql;
execute create_stmt;
set i = LPAD(cast(i+1 as char),2,0);
end while;
end ;
call test5_4();
这样就正确实现了前缀编号补零。
版权声明：本文为qq_39028641原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。