第五章:SQL高级处理
5.1 窗口函数
窗口函数也称为OLAP函数(OnLine AnalyticalProcessing)或窗口函数,意思是对数据库数据进行实时分析处理。常规的SELECT语句都是对整张表进行查询,而窗口函数可以让我们有选择的去调某一部分数据进行汇总、计算和排序。
窗口函数通用形式:
<窗口函数> OVER ([ PARTITION BY <列名> ]
[ ORDER BY <排序用列名> ])
[ ]中的内容可以省略,但是两个参数不能同时没有(最少二选一)。
PARTITON BY 子句可选参数,指示如何将查询行划分为组,类似于 GROUP BY 子句的分组功能,但是其不具备 GROUP BY的汇总功能,并不会改变原始表中记录的行数。
ORDER BY 子句可选参数,指示如何对每个分组中的行进行排序,即决定窗口内,是按那种规则(字段)来排序的。
例如:
SELECT product_name
,product_type
,sale_price
,RANK() OVER (PARTITION BY product_type
ORDER BY sale_price) AS ranking
FROM product;
得到的结果是:
PARTITION BY 能够设定窗口对象范围。本例中,为了按照商品种类进行排序,我们指定了product_type。即一个商品种类就是一个小的"窗口"。
ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列,我们指定了sale_price。窗口函数中的ORDER BY与SELECT语句末尾的ORDER BY一样,通过关键字ASC/DESC来指定升序/降序。省略该关键字时会默认按照ASC升序进行排序。
5.2 窗口函数种类
窗口函数大致可分为两类。
一是 将SUM、MAX、MIN等聚合函数用在窗口函数中
二是 RANK、DENSE_RANK等排序用的专用窗口函数
5.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 位
运行以下代码:
SELECT product_name
,product_type
,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_num
FROM product;
5.2.2 聚合函数在窗口函数上的使用
聚合函数在窗口函数中的使用方法和之前的专用窗口函数一样,只是出来的结果是一个累计的聚合函数值。
运行以下代码:
SELECT product_id
,product_name
,sale_price
,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg
FROM product;
即,按我们指定的排序,这里是product_id,当前所在行及之前所有的行的合计或均值。即累计到当前行的聚合。
5.3 窗口函数的的应用 - 计算移动平均
聚合函数在窗口函数使用时,计算的是累积到当前行的所有的数据的聚合。 但实际上,还可以指定更加详细的汇总范围。该汇总范围称为框架。
语法
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS n PRECEDING )
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS BETWEEN n PRECEDING AND n FOLLOWING)
PRECEDING(“之前”),将框架指定为 “截止到当前运行行之前的n 行”,加上自身行
FOLLOWING(“之后”),将框架指定为 “截止到当前运行行之后 n 行”,加上自身行
执行以下代码:
SELECT product_id
,product_name
,sale_price
,AVG(sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM product;
*执行结果1:
注意观察计算的范围。
BETWEEN 1 PRECEDING AND 1 FOLLOWING,将框架指定为 “之前1行” + “之后1行” + “自身”
执行以下代码:
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:
5.3.1 窗口函数适用范围和注意事项
* 窗口函数只能在SELECT子句中使用。
* 窗口函数OVER 中的ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。
5.4 GROUPING运算符
5.4.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;
得到的结果为:
5.5 存储过程和函数
5.5.1 基本介绍
存储过程的含义:一组预先编译好的SQL语句的集合,便于成批的处理语句
SQL语句1 SQL语句2 SQL语句3 |
令这三句整体为存储过程a,以后调用这三句话可以直接用a
存储过程的作用:提高代码的重用性,简化操作,减少了编译次数,并且减少了和数据库服务器连接的次数。
5.5.2 创建语法
3、存储过程体中的每条SQL语句的结尾要求必须加分号。
4、存储过程的结尾要使用delimiter重新设置,语法为:delimiter+结束标记符,例如$。
5.5.3 调用语法
CALL+存储过程名(参数列表)
参数列表内容同5.5.2
5.5.4 应用示例
创建存储过程,实现传入用户名和密码插入到admin表中。
(调用时即调用了插入功能,插入的是admin 0000)
练习题
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
5.2
继续使用product表,计算出按照登记日期(regist_date)升序进行排列的各日期的销售单价(sale_price)的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位(也就是将其看作比其他日期都早)
-- 1. regist_date 为 NULL 时,显示 “1年1月1日”。
SELECT regist_date, product_name, sale_price,
SUM(sale_price) OVER (ORDER BY COALESCE(regist_date, CAST('0001-01-01' AS DATE))) AS current_sum_price
FROM Product;
-- 2. regist_date 为 NULL 时,将该记录放在最前显示。
SELECT regist_date, product_name, sale_price,
SUM(sale_price) OVER (ORDER BY regist_date NULLS FIRST) AS current_sum_price
FROM Product;
5.3
思考题
① 窗口函数不指定PARTITION BY的效果是什么?
解答:PARTITION BY 能够设定窗口对象范围,窗口函数不指定就是针对全局
② 为什么说窗口函数只能在SELECT子句中使用?实际上,在ORDER BY 子句使用系统并不会报错。
解答:
本质上是因为 SQL
语句的执行顺序。
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
如果在 WHERE, GROUP BY, HAVING
使用了窗口函数(属于SELECT 语句),就是说提前进行了一次排序,排序之后再去除记录、汇总、汇总过滤,第一次排序结果就是错误的,没有实际意义。而 ORDER BY
语句执行顺序在 SELECT
语句之后,自然是可以使用的。
5.4
使用存储过程创建20个与 `shop.product` 表结构相同的表,如下图所示:
首先创建存储过程如下:
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_multi_table`()
BEGIN
#Routine body goes here...
declare i int; --declare表示定义
set i=1;
while i<21 do
IF i<10 THEN
set @tb = CONCAT('create table table0',i,' like product');
ELSE
set @tb = CONCAT('create table table', i,' like product');
END IF;
PREPARE create_stmt FROM @tb;
EXECUTE create_stmt;
SET i=i+1;
end while;
END $
保存该存储过程,然后调用即可
call `create_multi_table`()