wonderful-sql：(5)SQL 高级处理

教程地址： https://linklearner.com/learn/detail/70

《SQL基础教程（第2版）》： https://www.ituring.com.cn/book/1880

《DataWhale SQL 基础教程》： https://fastly.jsdelivr.net/gh/datawhalechina/wonderful-sql@main/materials/SQL%E5%9F%BA%E7%A1%80%E6%95%99%E7%A8%8B-V2.0.pdf

wonderful-sql： https://github.com/datawhalechina/wonderful-sql

这是笔者参与开源学习《wonderful-sql》的学习记录，希望通过分享自己的学习过程能够帮助到零基础的同学快速入门 SQL。

本次学习借助开源学习社区 DataWhale，不仅有优秀的助教辅导，还有优秀的小伙伴一起学习，计划大概如下：

前言

所谓“光阴似箭，日月如梭”，一转眼，又是新的一个月了， 2023 年余额所剩不多了。最近工作不太顺利，感觉做什么都没激情，唯有夜深人静的时候，打开 datawhale linklearner开始刷教程，瞬间就感觉累了、困了……本次 Task 主要学习 SQL 高级处理，之所以高级大抵是用来处理复杂查询的和分析大量数据，SQL 高级处理包括窗口函数、GROUPING、存储过程和函数、预处理声明等等。本次学习笔记，我们还是按照老规矩–从习题入手，开始吧~

习题1

题目：

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

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

知识点

上述 SQL 使用了窗口函数：

• 窗口函数：窗口函数也称为OLAP函数（ Online Analytical Processing 在线分析处理），可以对数据库数据进行实时分析处理。窗口函数可以让我们选择特定的数据进行分析，并对其进行汇总、计算和排序。窗口函数包括聚合函数（如SUM、MAX、MIN）和专用函数（如RANK、DENSE_RANK、ROW_NUMBER）。

语法：

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

PARTITION BY 子句 可选参数，用于将结果集划分为多个分区（也称为窗口），然后在每个分区内对数据进行聚合计算。PARTITION BY子句通常用于大型数据集，并且在处理大量数据时可以显著提高性能（笔者未验证）。

ORDER BY 子句可选参数，顾名思义，用于指示每个分区的排序规则。

解题

以上SQL查询语句使用了窗口函数来计算每个产品的销售价格（sale_price）和当前最大销售价格（Current_max_price）。该查询将返回product表中的每个产品的product_id、product_name和sale_price列，并在每一行中显示该产品在当前行（按照product_id升序排列）之前的所有产品的最大销售价格作为Current_max_price列的值。

习题2

题目：

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

知识点

使用窗口函数计算累计值。

解题

SELECT  product_id,
        product_name,
	    regist_date,
		sale_price,
		SUM(sale_price) OVER (ORDER BY regist_date) AS current_sum
FROM product;

习题3

题目：

思考题

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

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

知识点

PARTITION BY进行分区，然后在每个分区内对数据进行聚合计算。

解题

① 窗口函数不指定PARTITION BY的效果是将整个查询结果作为一个分区，不对结果进行分割。以 每个商品类别的销售价格排名的 SQL 为例：

-- PARTITION BY
SELECT product_name
       ,product_type
       ,sale_price
       ,RANK() OVER (PARTITION BY product_type
                         ORDER BY sale_price) AS ranking
FROM product; 


-- 没有
SELECT product_name
       ,product_type
       ,sale_price
       ,RANK() OVER (ORDER BY sale_price) AS ranking
FROM product;  

②
窗口函数只能在SELECT子句中使用是因为它们是一种特殊的聚合函数，能够对查询结果进行分组、排序和聚合计算。窗口函数通常用于计算排名、百分比、排名积分等与窗口有关的数据。

虽然在实际使用中，在ORDER BY子句中使用窗口函数并不会报错，但这种使用方式并不是规范的语言语法(具体可能跟 SQL 执行顺序有关)。在ORDER by子句中，应该使用普通的ORDER BY子句来对查询结果进行排序，而不是使用窗口函数。

我们尽量遵守SQL语法规则和保持良好的编程习惯，应该将窗口函数仅用于SELECT子句中，而不是在ORDER BY子句中使用。

习题4

题目：

使用存储过程创建20个与 shop.product 表结构相同的表，如下图所示：

知识点

存储过程基本语法：

[delimiter //] -- $$，可以是其他特殊字符
CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] 
[BEGIN]
  routine_body
  -- 存储过程的SQL代码块
  -- 可以包含查询、数据操作、控制结构等
[END//] -- $$，可以是其他特殊字符

通过CALL调用（某些系统中无需 CALL 关键字）：

CALL sp_name([parameter_value, ...]);

• GROUPING 运算符：GROUPing运算符包括ROLLUP、CUBE和GROUPING SETS。ROLLUP可以同时得出合计和小计，CUBE可以将所有可能的组合的汇总结果集中到一个结果中，GROUPING SETS允许指定多个GROUP BY选项，增强了GROUP BY的功能。

• 预处理声明（PREPARE Statement），用于准备一个可重用的SQL语句，以便在需要时进行执行。

解题

DELIMITER $$

CREATE PROCEDURE `c_product`(IN num_tables INT)     -- 定义函数及入参
BEGIN  
    DECLARE i INT DEFAULT 1;    -- 初始化迭代的 i
    DECLARE table_name VARCHAR(20);   -- 定义 table_name
    WHILE i <= num_tables DO   -- 循环
        SET table_name = CONCAT('table', LPAD(i, 2, '0'));  -- 动态表名 如 table01
        SET @sql = CONCAT('CREATE TABLE ', table_name, ' LIKE shop.product;'); -- 动态 SQL
        PREPARE stmt FROM @sql; -- 准备需要执行的语句预处理声明
        EXECUTE stmt;   -- 执行预处理声明
        DEALLOCATE PREPARE stmt; -- 释放预处理声明
        SET i = i + 1;  
    END WHILE;  
END $$

DELIMITER ;

CALL c_product(20); -- 调用

当然这里申明的函数会一直在且后续重复定义不会覆盖，通过 DROP 删除。