MySQL学习（五）SQL⾼级处理练习题
窗⼝函数
窗⼝函数也称为OLAP函数。OLAP 是 OnLine AnalyticalProcessing 的简称，意思是对数据库数据进⾏实时分析处理。
为了便于理解，称之为 窗⼝函数。常规的SELECT语句都是对整张表进⾏查询，⽽窗⼝函数可以让我们有选择的去某⼀部分数据进⾏汇总、计算和排序。
窗⼝函数的通⽤形式：
<；窗⼝函数> OVER ([PARTITION BY <；列名>] ORDER BY <；排序⽤列名>)
其中， [ ]中的内容可以省略。
PARTITON BY是⽤来分组，即选择要看哪个窗⼝，类似于 GROUP BY ⼦句的分组功能，但是 PARTITION BY ⼦句并不具备 GROUP BY ⼦句的汇总功能，并不会改变原始表中记录的⾏数。
ORDER BY 是⽤来排序，即决定窗⼝内，是按那种规则(字段)来排序的。
窗⼝函数种类
⼀是 将SUM、MAX、MIN等聚合函数⽤在窗⼝函数中
⼆是 RANK、DENSE_RANK等排序⽤的专⽤窗⼝函数。专⽤窗⼝函数包括：
RANK函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4位……
DENSE_RANK函数：同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。例）有 3 条记录排在第 1 位时：1 位、1位、1 位、2 位……
ROW_NUMBER函数：赋予唯⼀的连续位次。例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位
窗⼝函数的的应⽤ - 计算移动平均
聚合函数在窗⼝函数使⽤时，计算的是累积到当前⾏的所有的数据的聚合。 实际上，还可以指定更加详细的汇总范围。该汇总范围成为 框架 (frame)。
语法
<；窗⼝函数> OVER (ORDER BY <；排序⽤列名> ROWS n PRECEDING )sql容易学吗
<；窗⼝函数> OVER (ORDER BY <；排序⽤列名> ROWS BETWEEN n PRECEDING AND n FOLLOWING)
PRECEDING（“之前”）将框架指定为 “截⽌到之前 n ⾏”，加上⾃⾝⾏
FOLLOWING（“之后”）将框架指定为 “截⽌到之后 n ⾏”，加上⾃⾝⾏
BETWEEN 1 PRECEDING AND 1 FOLLOWING将框架指定为 “之前1⾏” + “之后1⾏” + “⾃⾝”
注意事项
1、原则上，窗⼝函数只能在SELECT⼦句中使⽤。
2、窗⼝函数OVER 中的ORDER BY ⼦句并不会影响最终结果的排序。其只是⽤来决定窗⼝函数按何种顺序计算
存储过程和函数
创建⼀个存储例程（⼀个存储过程或函数）基本语法：
[delimiter //]($$，可以是其他特殊字符）
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...]
[BEGIN]
routine_body
[END//]($$，可以是其他特殊字符）
存储过程和函数的参数有三类，分别是：IN，OUT，INOUT，其中：
IN 是⼊参。每个参数默认都是⼀个 IN 参数。如需设定⼀个参数为其他类型参数，请在参数名称前使⽤关键字 OUT 或 INOUT 。⼀个IN参数将⼀个值传递给⼀个过程。存储过程可能会修改这个值，但是当存储过程返回时，调⽤者不会看到这个修改。
OUT 是出参。⼀个 OUT 参数将⼀个值从过程中传回给调⽤者。它的初始值在过程中是 NULL ，当过程返回时，调⽤者可以看到它的值。
INOUT ：⼀个 INOUT 参数由调⽤者初始化，可以被存储过程修改，当存储过程返回时，调⽤者可以看到存储过程的任何改变。
预处理声明 PREPARE Statement
使⽤步骤如下：
1. PREPARE – 准备需要执⾏的语句预处理声明。
2. EXECUTE – 执⾏预处理声明。
3. DEALLOCATE PREPARE – 释放预处理声明。
基本语法：
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;
答：
该结果表明Current_max_price的值返回的是当前product_id和之前商品售价中的最⼤值，其中product_id按升序排列。
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 Current_SUM_price
FROM product;
5.3**
思考题
① 窗⼝函数不指定PARTITION BY的效果是什么？
② 为什么说窗⼝函数只能在SELECT⼦句中使⽤？实际上，在ORDER BY ⼦句使⽤系统并不会报错。
答：
① partition by ⽤来选择要看哪个窗⼝，类似于分组，不使⽤partition by是查看全局。
② 因为窗⼝函数是对where或者group by⼦句处理后的结果进⾏操作，所以窗⼝函数原则上只能写在select⼦句中，⽽不是order by。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。