Datawhale SQL TASK5

第5章 SQL高级处理

摘要：这一章还好，就是存储过程还一知半解的，ROLLUP是写在一起的，分开就错了，存储过程里面是INT,而不是INTEGER,WHILE对应END WHILE，END WHILE要加分号。

目录

• 第5章 SQL高级处理
  • 5.1 窗口函数
  • 5.2 窗口函数种类
  • 5.3 窗口函数计算移动平均
  • 5.4 GROUPING运算符
  • 5.5 存储过程和函数
  • 5.6 预处理声明
  • 练习题
    • 5.1
    • 5.2
    • 5.3
    • 5.4

5.1 窗口函数

1. 窗口函数也叫OLAP（Online AnalyticalProcessing）的简称，意思是对数据库数据进行实时分析处理；

2. 窗口函数是对于OLAP的俗称，可以选择部分数据进行汇总、计算、排序，而非整张表

3. 窗口函数的通用形式

   1. <窗口函数> OVER 
      (
      [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;  
      

   2. PARTITION BY用来分组，但是不具备汇总功能

   3. ORDER BY用来排序

5.2 窗口函数种类

1. 窗口函数分为两类
   1. 聚合函数，SUM,MAX,MIN
   2. 排序函数,RANK,DENSE_RANK
2. 排序函数辨析，例子是前三个数一样，第四个不一样
   1. RANK函数，1,1,1,4
   2. DENSE_RANK函数，1,1,1,2
   3. ROW_NUMBER函数，1,2,3,4
3. 聚合函数的窗口函数是，累计到当前所在行的聚合。
4. 窗口函数的ORDER BY排序只影响窗口函数的排序
5. 原则上，窗口函数只能在SELECT子句中使用

5.3 窗口函数计算移动平均

SELECT  product_id
       ,product_name
       ,sale_price
       ,AVG(sale_price) OVER (ORDER BY product_id
                               ROWS 2 PRECEDING) AS moving_avg1
       ,AVG(sale_price) OVER (ORDER BY product_id
                               ROWS BETWEEN 1 PRECEDING 
                                        AND 1 FOLLOWING) AS moving_avg2  
       ,AVG(sale_price) OVER (ORDER BY product_id
                               ROWS  1 FOLLOWING) AS moving_avg3  -- 这个报错，没有这个
  FROM product;  

5.4 GROUPING运算符

1. ROLLUP可以计算分类的小计，用法就是GROUP BY column_name WITH ROLLUP
   1. ROLLUP是放在一起写的，不是分开写的

5.5 存储过程和函数

1. 应用场景:例如，需要根据卖出和进货，实时更新商品的数量信息

2. 存储过程类似于建库，以后调用即可，不用重新写了

3. IN 是入参，OUT是出参，@变量是局部变量

4. 你把存储过程理解为函数就好理解多了

5. DELIMITER是指定分隔符，原先的分隔符是分号

6. DEFINER用来指定执行程序的什么用户，默认是当前用户，类似Linux执行语句

   CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT) -- 注意是INT不是INTEGER
          BEGIN
            SELECT COUNT(*) INTO cities FROM world.city
            WHERE CountryCode = country;
          END//
   
   
   DELIMITER ;
   CALL citycount('CHN', @cities); -- 用来调用
   SELECT @cities;
   
   +---------+
   | @cities |
   +---------+
   |     363 |
   +---------+
   1 row in set (0.04 sec)
   
   
   -- 下面这个例子主要学习一下SQL循环怎么用
   
   CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_product_test`()
   BEGIN
       declare i int; -- 变量声明用DECLARE ，类型写在变量名后面，变量得 先声明后使用
       set i=1;		-- 变量数值设置初值用SET
       while i<9 do	-- 循环采用WHILE 条件 DO，最后END WHILE
           set @pcid = CONCAT('000', i);
           PREPARE stmt FROM 'INSERT INTO product_test() SELECT * FROM shop.product where product_id= ?';
           EXECUTE stmt USING @pcid; -- 还是用CALL类型好理解
           set i=i+1;	-- 变量数值改变也用SET，后面跟C语言一样
       end while;		-- END WHILE要加分号
   END
   

5.6 预处理声明

1. 使用步骤：

   1. PREPARE FROM，定义
   2. EXECUTE USING，执行
   3. DEALLOCATE PREPARE ，释放

2. 例子

   1. PREPARE stmt1 FROM 
      	'SELECT 
         	    product_id, 
                  product_name 
      	FROM product
              WHERE product_id = ?';
      
      SET @pcid = '0005'; 
      EXECUTE stmt1 USING @pcid;
      
      DEALLOCATE PREPARE stmt1;
      

练习题

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;

答：逐行执行，求出累计到改行的最大销售价格

5.2

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

SELECT * FROM shop1.product;
SELECT  regist_date,SUM(sale_price) 
OVER (ORDER BY COALESCE(regist_date,"1900-01-01"))
	AS Sum_sale_price
FROM shop1.product;

5.3

思考题

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

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

5.4

使用简洁的方法创建20个与 shop.product 表结构相同的表，如下图所示：

-- 代码有错，后面再改
USE shop;

CREATE DEFINER=`root`@`localhost` PROCEDURE create_n_tables(IN num INT)
BEGIN
 	declare i int;
	SET i=num;
    while i>0 DO
		CREATE TABLE product_test like shop1.product;
        SET i=i-1;
	END while;
END

CALL create_n_tables(20);