Task05 SQL高级处理

  参考：GitHub - datawhalechina/wonderful-sql: Follow me，从 0 到 1 掌握 SQL。

1.窗口函数

1.1 窗口函数的概念以及基本使用 

窗口函数也称为OLAP函数。

常规的SELECT语句都是对整张表进行查询，而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。

窗口函数的通用形式：

<窗口函数> OVER ( [ PARTITION BY <列名> ]
                                 [ ORDER BY <排序用列名> ] )  

其中 PARTITION BY 语句是用于分组的，即选择窗口，类似于 GROUP BY 语句，但是没有汇总功能；ORDER BY语句是用于排序的，即决定窗口内的数据按何规则排序。

1.2 窗口函数的分类

窗口函数可大致分为两类：

一类是 RANK、DENSE_RANK等排序用的专用窗口函数

另一类是将SUM、MAX、MIN等聚合函数用在窗口函数中

1.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位。

1.2.2 聚合函数在窗口函数中

聚合函数在窗口函数中的使用方法和之前的专用窗口函数一样，只是出来的结果是一个累计的聚合函数值。

1.3 窗口函数的应用-计算移动平均

<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS n PRECEDING )  
                 
<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS BETWEEN n PRECEDING AND n FOLLOWING)

PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行

FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行

2. GROUPING运算符

只使用group by子句和聚合函数是无法同时得出小计和合计的，想要同时得到，可以使用grouping运算符。

2.1 ROLLUP - 计算合计及小计

SELECT  product_type
       ,regist_date
       ,SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type, regist_date WITH ROLLUP;  

得到的结果为：

3. 存储过程及函数

基本语法：

DELIMITER //
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	存储过程体

END;
DELIMITER ;

因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

DELIMITER //”语句的作用是将MySQL的结束符设置为//，并以“END //”结束存储过程。

存储过程和函数的参数有三类，分别是: IN, OUT, INOUT

• IN 是入参。每个参数默认都是一个 IN 参数。如需设定一个参数为其他类型参数，请在参数名称前使用关键字 OUT 或 INOUT 。一个IN参数将一个值传递给一个过程。存储过程可能会修改这个值，但是当存储过程返回时，调用者不会看到这个修改。
• OUT 是出参。一个 OUT 参数将一个值从过程中传回给调用者。它的初始值在过程中是 NULL ，当过程返回时，调用者可以看到它的值。
• INOUT ：一个 INOUT 参数由调用者初始化，可以被存储过程修改，当存储过程返回时，调用者可以看到存储过程的任何改变。

练习题

1. 请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。

SELECT  product_id
       ,product_name
       ,sale_price
       ,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
  FROM product;

找出之前所有行及本行中的最大值

2. 继续使用product表，计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早）

3. 思考题

① 窗口函数不指定PARTITION BY的效果是什么？

② 为什么说窗口函数只能在SELECT子句中使用？实际上，在ORDER BY 子句使用系统并不会报错。

① 不会进行分组，操作窗口是整张表

② 因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中；而ORDER BY 子句则是对SELECT 子句中的结果进行操作，操作的是整个结果表，所以可以使用窗口函数，但是窗口函数的返回结果只作为ORDER BY子句的排序依据，并不能返回期望的结果。