Task05：SQL高级处理

最新推荐文章于 2024-07-06 00:33:08 发布

JxWang05

最新推荐文章于 2024-07-06 00:33:08 发布

阅读量133

点赞数

分类专栏： SQL编程语言文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/weixin_52202311/article/details/125003578

版权

SQL编程语言专栏收录该内容

10 篇文章 0 订阅

订阅专栏

Task05：SQL高级处理

教程地址
1. 窗口函数
2. rollup
3. 存储过程
4. 预处理
A.练习题
- A.1
- A.2
- A.3
- A.4

教程地址

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

1. 窗口函数

1.1 简介

窗口函数还有一个别名是，OLAP 函数，也就是 OnLine Analytical Processing

其一般的语法格式如下：

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

中括号当中的部分是可以省略的

partition 有一种 group by 的感觉，就是分组处理，选定列进行分组

然后 order by 呢，就是排序，在窗口内排序，这没啥好说的

举例如下：

mysql> SELECT product_name
    ->        ,product_type
    ->        ,sale_price
    ->        ,RANK() OVER (
    ->          PARTITION BY product_type
    ->          ORDER BY sale_price
    ->         ) AS ranking
    -> FROM product;
+--------------+--------------+------------+---------+
| product_name | product_type | sale_price | ranking |
+--------------+--------------+------------+---------+
| 打孔器       | 办公用品     |        500 |       1 |
| 圆珠笔       | 办公用品     |       5000 |       2 |
| 叉子         | 厨房用具     |        500 |       1 |
| 擦菜板       | 厨房用具     |        880 |       2 |
| 菜刀         | 厨房用具     |       3000 |       3 |
| 高压锅       | 厨房用具     |       6800 |       4 |
| T恤          | 衣服         |       1000 |       1 |
| 运动T恤      | 衣服         |       4000 |       2 |
+--------------+--------------+------------+---------+
8 rows in set (0.00 sec)

我们可以看到这是对种类列进行了分组处理

然后对每个组内的记录按照价格进行排序，生成新列

1.2 专用函数

RANK函数
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……

DENSE_RANK函数
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……

ROW_NUMBER函数
赋予唯一的连续位次。

例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位

直接跑一个示例来看看

mysql> SELECT  product_name
    ->        ,product_type
    ->        ,sale_price
    ->        ,RANK() OVER (ORDER BY sale_price) AS ranking
    ->        ,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking
    ->        ,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
    -> FROM product; 
+--------------+--------------+------------+---------+---------------+---------+
| product_name | product_type | sale_price | ranking | dense_ranking | row_num |
+--------------+--------------+------------+---------+---------------+---------+
| 打孔器       | 办公用品     |        500 |       1 |             1 |       1 |
| 叉子         | 厨房用具     |        500 |       1 |             1 |       2 |
| 擦菜板       | 厨房用具     |        880 |       3 |             2 |       3 |
| T恤          | 衣服         |       1000 |       4 |             3 |       4 |
| 菜刀         | 厨房用具     |       3000 |       5 |             4 |       5 |
| 运动T恤      | 衣服         |       4000 |       6 |             5 |       6 |
| 圆珠笔       | 办公用品     |       5000 |       7 |             6 |       7 |
| 高压锅       | 厨房用具     |       6800 |       8 |             7 |       8 |
+--------------+--------------+------------+---------+---------------+---------+
8 rows in set (0.00 sec)

RANK() 是将相同大小的记录置为相同位次，比如 ranking 的叉子序号为 2，但是位次相同则置为 1

但是 RANK() 选择了依照序号，即序号为 3 的菜板，其位次同样为 3

所以总的来说只是将相同大小的记录置为相同序号，其他顺序不变，按照序号对位次进行编号

DENSE_RANK() 也是将相同大小的记录置为相同位次，叉子序号同样为 2，位次为 1

但是 DENSE_RANK() 选择了顺延位次，也就是其位次的编号依赖于上一个位次的编号，不按序号

ROW_NUMBER() 就是直接按照序号对位次进行编号

1.3 聚合函数

直接举例来看，大概就是固定窗口起点，然后扩展窗口长度

mysql> SELECT  product_id
    ->        ,product_name
    ->        ,sale_price
    ->        ,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
    ->        ,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg
    -> FROM product;  
+------------+--------------+------------+-------------+-------------+
| product_id | product_name | sale_price | current_sum | current_avg |
+------------+--------------+------------+-------------+-------------+
| 0001       | T恤          |       1000 |        1000 |   1000.0000 |
| 0002       | 打孔器       |        500 |        1500 |    750.0000 |
| 0003       | 运动T恤      |       4000 |        5500 |   1833.3333 |
| 0004       | 菜刀         |       3000 |        8500 |   2125.0000 |
| 0005       | 高压锅       |       6800 |       15300 |   3060.0000 |
| 0006       | 叉子         |        500 |       15800 |   2633.3333 |
| 0007       | 擦菜板       |        880 |       16680 |   2382.8571 |
| 0008       | 圆珠笔       |       5000 |       21680 |   2710.0000 |
+------------+--------------+------------+-------------+-------------+
8 rows in set (0.02 sec)

也挺神奇的，这是滑动平均和滑动求和，窗口的长度随记录增加而增加

每行记录的这两个列，大概意思就是，接着到这一行，以上所有的求和及平均

1.4 移动平均

算是对聚合函数的变种吧，固定窗口大小，滑动位置

mysql> SELECT product_id
    ->        ,product_name
    ->        ,sale_price
    ->        
    ->        ,AVG(sale_price) OVER (
    ->          ORDER BY product_id
    ->          ROWS 2 PRECEDING
    ->         ) AS moving_avg_1
    ->         
    ->        ,AVG(sale_price) OVER (
    ->          ORDER BY product_id
    ->          ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ->         ) AS moving_avg_2
    ->         
    -> FROM product;  
+------------+--------------+------------+--------------+--------------+
| product_id | product_name | sale_price | moving_avg_1 | moving_avg_2 |
+------------+--------------+------------+--------------+--------------+
| 0001       | T恤          |       1000 |    1000.0000 |     750.0000 |
| 0002       | 打孔器       |        500 |     750.0000 |    1833.3333 |
| 0003       | 运动T恤      |       4000 |    1833.3333 |    2500.0000 |
| 0004       | 菜刀         |       3000 |    2500.0000 |    4600.0000 |
| 0005       | 高压锅       |       6800 |    4600.0000 |    3433.3333 |
| 0006       | 叉子         |        500 |    3433.3333 |    2726.6667 |
| 0007       | 擦菜板       |        880 |    2726.6667 |    2126.6667 |
| 0008       | 圆珠笔       |       5000 |    2126.6667 |    2940.0000 |
+------------+--------------+------------+--------------+--------------+
8 rows in set (0.00 sec)

我们在第一个滑动平均，设置的是截止到目前行之前的 2 行，然后加上自身，为 3 行

那么在第二个滑动平均，是目前行之前的一行，以及之后的一行，加上自身，为 3 行

这个主要的区别在第二行的相关列的计算上

对于第一个来说，其求的是前一行及自身的平均，共 2 行的平均

但是对于第二个，是前一行、后一行，以及自身，共 3 行的平均

2. rollup

大概意思就是，计算一个合计的值，对于分类的合计

( 话说为啥不把这一块内容放在 group by 那里啊 )

mysql> SELECT  product_type
    ->        ,regist_date
    ->        ,SUM(sale_price) AS sum_price
    -> FROM product
    -> GROUP BY product_type, regist_date WITH ROLLUP;  
+--------------+-------------+-----------+
| product_type | regist_date | sum_price |
+--------------+-------------+-----------+
| 办公用品     | 2009-09-11  |       500 |
| 办公用品     | 2009-11-11  |      5000 |
| 办公用品     | NULL        |      5500 |
| 厨房用具     | 2008-04-28  |       880 |
| 厨房用具     | 2009-01-15  |      6800 |
| 厨房用具     | 2009-09-20  |      3500 |
| 厨房用具     | NULL        |     11180 |
| 衣服         | NULL        |      4000 |
| 衣服         | 2009-09-20  |      1000 |
| 衣服         | NULL        |      5000 |
| NULL         | NULL        |     21680 |
+--------------+-------------+-----------+
11 rows in set (0.00 sec)

这大概就是，呃，算了各个种类的合计值，然后算总的合计值

话说这个 regist_date 出现的就很奇怪啊，删掉试试

mysql> SELECT  product_type
    ->        ,SUM(sale_price) AS sum_price
    -> FROM product
    -> GROUP BY product_type WITH ROLLUP;  
+--------------+-----------+
| product_type | sum_price |
+--------------+-----------+
| 办公用品     |      5500 |
| 厨房用具     |     11180 |
| 衣服         |      5000 |
| NULL         |     21680 |
+--------------+-----------+
4 rows in set (0.00 sec)

嗯，group by 里面删掉，那 select 当然也得删掉

这个结果来看的话，只有各个类别的合计值，没有详细值了

换一个列顶替 regist_date 看看

mysql> SELECT  product_id
    ->        ,product_type
    ->        ,SUM(sale_price) AS sum_price
    -> FROM product
    -> GROUP BY product_type,product_id WITH ROLLUP;  
+------------+--------------+-----------+
| product_id | product_type | sum_price |
+------------+--------------+-----------+
| 0002       | 办公用品     |       500 |
| 0008       | 办公用品     |      5000 |
| NULL       | 办公用品     |      5500 |
| 0004       | 厨房用具     |      3000 |
| 0005       | 厨房用具     |      6800 |
| 0006       | 厨房用具     |       500 |
| 0007       | 厨房用具     |       880 |
| NULL       | 厨房用具     |     11180 |
| 0001       | 衣服         |      1000 |
| 0003       | 衣服         |      4000 |
| NULL       | 衣服         |      5000 |
| NULL       | NULL         |     21680 |
+------------+--------------+-----------+
12 rows in set (0.00 sec)

唔，因为这里的 id 和 date 都具有唯一性

所以说，group 之后，就把各个类别的详细记录展示出来了

啊，话说这样也可以啊，我之前一直不敢往这里想

我是说，当我们 group 之后，有时需要 select 其他东西

就是说，select 不是 group 依据的列

现在我发现可以把这些列丢进 group

虽然它们似乎并不在 group 起作用

但是可以在 group 之后被 select 出来

3. 存储过程

我的理解是，预先写好的函数

然后 in 用来传入参数，out 是传出，语法如下：

[delimiter //]($$，可以是其他特殊字符)
CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] 
[BEGIN]
  routine_body
[END //]($$，可以是其他特殊字符)

直接上例子吧

mysql> DROP PROCEDURE IF EXISTS pricecount;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> DELIMITER //
mysql> CREATE PROCEDURE pricecount (IN input_price INT, OUT counts INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO counts FROM shop.product
    ->   WHERE input_price = sale_price;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> DELIMITER ;
mysql> 
mysql> call pricecount(1000, @counts);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select @counts;
+---------+
| @counts |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> 
mysql> select * from product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤          | 衣服         |       1000 |            500 | 2009-09-20  |
| 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11  |
| 0003       | 运动T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0004       | 菜刀         | 厨房用具     |       3000 |           2800 | 2009-09-20  |
| 0005       | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-15  |
| 0006       | 叉子         | 厨房用具     |        500 |           NULL | 2009-09-20  |
| 0007       | 擦菜板       | 厨房用具     |        880 |            790 | 2008-04-28  |
| 0008       | 圆珠笔       | 办公用品     |       5000 |           NULL | 2009-11-11  |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)

首先我们要删除一下 pricecount，防止已经存在，即重名

然后是是声明部分，以 // 作为分隔符

然后这个 DELIMITER ; 感觉跟激活函数一样，但实际上好像不是

我查的资料说是，这是在告诉 mysql 存储过程定义完备

但是 end // 不是已经标明完毕了吗？

我的发现是，不行，不加这个 DELIMITER ; ，后面就一直认为没结束

还有一个建表的例子，这里就不做了，大同小异

4. 预处理

简单来说就是，设置了一个占位符，然后使得查询不必完全展开，每次更新占位符就好

举例：

mysql> PREPARE stmt1 FROM 
    ->   'SELECT product_id, 
    '>           product_name 
    '>    FROM product 
    '>      WHERE product_id = ?';
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> 
mysql> SET @pcid = '0005'; 
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> EXECUTE stmt1 USING @pcid;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0005       | 高压锅       |
+------------+--------------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt1;
Query OK, 0 rows affected (0.00 sec)

先定义语句结构，然后设置变量，最后传入变量执行，最后释放声明

与上面的存储过程联动可得：

mysql> DROP TABLE IF EXISTS product_test;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> 
mysql> CREATE TABLE IF NOT EXISTS product_test LIKE product;
select * from product_test;Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> DROP PROCEDURE IF EXISTS insert_product_test;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> DELIMITER //
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_product_test`()
    -> BEGIN
    ->     declare i int;
    ->     set i=1;
    ->     while i<9 do
    ->         set @pcid = CONCAT('000', i);
    ->         PREPARE stmt FROM 'INSERT INTO product_test SELECT * FROM shop.product where product_id= ?';
    ->         EXECUTE stmt USING @pcid;
    ->         set i=i+1;
    ->     end while;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> DELIMITER ;
mysql> 
mysql> call insert_product_test();
Query OK, 1 row affected (0.05 sec)

mysql> 
mysql> select * from product_test;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤          | 衣服         |       1000 |            500 | 2009-09-20  |
| 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11  |
| 0003       | 运动T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0004       | 菜刀         | 厨房用具     |       3000 |           2800 | 2009-09-20  |
| 0005       | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-15  |
| 0006       | 叉子         | 厨房用具     |        500 |           NULL | 2009-09-20  |
| 0007       | 擦菜板       | 厨房用具     |        880 |            790 | 2008-04-28  |
| 0008       | 圆珠笔       | 办公用品     |       5000 |           NULL | 2009-11-11  |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)

先删表，再建表，然后建循环，使用预处理去逐个挑选并插入数据

但是预处理的定义应该在循环外面吧，定义一次就够了

然后就是要加上 DEALLOCATE PREPARE stmt1; 在最后取消声明

A.练习题

A.1

请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。
SELECT product_id
,product_name
,sale_price
,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
FROM product;

前面几列就不说了，新增的这个，应该是一个固定起点，然后不断延长的窗口

然后聚合函数就是求窗口内的最大，所以结果应该是截止到目前售价最高的价格

mysql> SELECT  product_id
    ->        ,product_name
    ->        ,sale_price
    ->        ,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
    -> FROM product;
+------------+--------------+------------+-------------------+
| product_id | product_name | sale_price | Current_max_price |
+------------+--------------+------------+-------------------+
| 0001       | T恤          |       1000 |              1000 |
| 0002       | 打孔器       |        500 |              1000 |
| 0003       | 运动T恤      |       4000 |              4000 |
| 0004       | 菜刀         |       3000 |              4000 |
| 0005       | 高压锅       |       6800 |              6800 |
| 0006       | 叉子         |        500 |              6800 |
| 0007       | 擦菜板       |        880 |              6800 |
| 0008       | 圆珠笔       |       5000 |              6800 |
+------------+--------------+------------+-------------------+
8 rows in set (0.00 sec)

A.2

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

反正 null 值默认比其他小，那就直接升序排列

关键问题是计算这个累计值，按日期累计的话，窗口函数和 group 都可以

但问题是，rollup 搞出来的总计值，其 regist_date 也是 null，这会导致总计值窜到第二行

所以我放弃了 rollup，换成子查询，单独一列显示总计值

mysql> SELECT  regist_date
    ->         ,SUM(sale_price) OVER (PARTITION BY regist_date ORDER BY regist_date) AS date_price
    ->         ,(select sum(sale_price) from product) AS sum_price
    -> FROM product
    -> ;
+-------------+------------+-----------+
| regist_date | date_price | sum_price |
+-------------+------------+-----------+
| NULL        |       4000 |     21680 |
| 2008-04-28  |        880 |     21680 |
| 2009-01-15  |       6800 |     21680 |
| 2009-09-11  |        500 |     21680 |
| 2009-09-20  |       4500 |     21680 |
| 2009-09-20  |       4500 |     21680 |
| 2009-09-20  |       4500 |     21680 |
| 2009-11-11  |       5000 |     21680 |
+-------------+------------+-----------+
8 rows in set (0.00 sec)

但是我发现不主动排序的话，好像结果也是有序的

mysql> SELECT  regist_date
    ->         ,SUM(sale_price)
    -> FROM product
    -> GROUP BY regist_date WITH ROLLUP
    -> -- ORDER BY regist_date
    -> ;
+-------------+-----------------+
| regist_date | SUM(sale_price) |
+-------------+-----------------+
| NULL        |            4000 |
| 2008-04-28  |             880 |
| 2009-01-15  |            6800 |
| 2009-09-11  |             500 |
| 2009-09-20  |            4500 |
| 2009-11-11  |            5000 |
| NULL        |           21680 |
+-------------+-----------------+
7 rows in set (0.00 sec)

A.3

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

那就是，一个，固定起点的窗口，长度随着记录的行数递增

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

执行顺序问题

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

窗口函数是对筛选后的数据做处理的

A.4

使用简洁的方法创建20个与 shop.product 表结构相同的表：

用预处理定义好建表的语句结构，然后在存储过程当中逐个执行

DROP PROCEDURE IF EXISTS insert_product_test;

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_product_test`()
BEGIN
    PREPARE stmt FROM 'CREATE TABLE IF NOT EXISTS ? LIKE product';
    declare i int;
    set i=0;
    while i<21 do
        IF i<10 THEN SET @table_name=CONCAT('table','0',i);
        ELSE SET @table_name=CONCAT('table',i);
        EXECUTE stmt USING @table_name;
        set i=i+1;
    end while;
END //

DELIMITER ;

call insert_product_test();

JxWang05

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Task05：SQL高级处理

Task05：SQL高级处理教程地址1. 窗口函数1.1 简介1.2 专用函数1.3 聚合函数1.4 移动平均2. rollup3. 存储过程4. 预处理A.练习题A.1A.2A.3A.4教程地址https://github.com/datawhalechina/wonderful-sqlhttps://gitee.com/datawhalechina/wonderful-sql1. 窗口函数1.1 简介窗口函数还有一个别名是，OLAP 函数，也就是 OnLine Analytical Proc
复制链接

扫一扫