Datawhale SQL TASK5

阿德勒信仰者

已于 2022-06-02 22:15:09 修改

阅读量461

点赞数

于 2022-05-27 10:18:48 首次发布

本文链接：https://blog.csdn.net/qq_35147871/article/details/124998797

版权

DataWhale SQL教程专栏收录该内容

6 篇文章 0 订阅

订阅专栏

第5章 SQL高级处理

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

文章目录

第5章 SQL高级处理

5.1 窗口函数

窗口函数也叫OLAP（Online AnalyticalProcessing）的简称，意思是对数据库数据进行实时分析处理；
窗口函数是对于OLAP的俗称，可以选择部分数据进行汇总、计算、排序，而非整张表

窗口函数的通用形式

<窗口函数> 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;

PARTITION BY用来分组，但是不具备汇总功能
ORDER BY用来排序

5.2 窗口函数种类

窗口函数分为两类
1. 聚合函数，SUM,MAX,MIN
2. 排序函数,RANK,DENSE_RANK
排序函数辨析，例子是前三个数一样，第四个不一样
1. RANK函数，1,1,1,4
2. DENSE_RANK函数，1,1,1,2
3. ROW_NUMBER函数，1,2,3,4
聚合函数的窗口函数是，累计到当前所在行的聚合。
窗口函数的ORDER BY排序只影响窗口函数的排序
原则上，窗口函数只能在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运算符

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

5.5 存储过程和函数

应用场景:例如，需要根据卖出和进货，实时更新商品的数量信息
存储过程类似于建库，以后调用即可，不用重新写了
IN 是入参，OUT是出参，@变量是局部变量
你把存储过程理解为函数就好理解多了
DELIMITER是指定分隔符，原先的分隔符是分号

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. PREPARE FROM，定义
2. EXECUTE USING，执行
3. DEALLOCATE PREPARE ，释放

例子

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);