学习笔记与练习 | SQL入门（五）|SQL高级处理

最新推荐文章于 2022-09-11 00:39:51 发布

choconeverlate

最新推荐文章于 2022-09-11 00:39:51 发布

阅读量166

点赞数

分类专栏： SQL 文章标签： sql mysql

本文链接：https://blog.csdn.net/choconeverlate/article/details/111569241

版权

SQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

窗口函数
窗口函数种类
- 专用窗口函数
- 聚合函数（窗口）
GROUPING运算符
- ROLLUP - 计算合计及小计
练习题

窗口函数

窗口函数也称为OLAP函数。OLAP 是OnLine AnalyticalProcessing 的简称，意思是对数据库数据进行实时分析处理。
窗口函数的通用形式：

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

*[]中的内容可以省略。

PARTITON BY是用来分组，即选择要看哪个窗口，类似于GROUP BY 子句的分组功能，但是PARTITION BY子句并不具备GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。
ORDER BY是用来排序，即决定窗口内，是按哪种规则(字段)来排序的。
窗口函数中的ORDER BY与SELECT语句末尾的ORDER BY一样，可以通过关键字ASC/DESC来指定升序/降序。省略该关键字时会默认按照ASC

窗口函数种类

专用窗口函数

RANK()函数
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……

DENSE_RANK()函数
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……

ROW_NUMBER()函数
赋予唯一的连续位次。
有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位

例子：

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

聚合函数（窗口）

当前所在行及之前所有的行的合计或均值，即累计到当前行的聚合。

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;

那么如果想更改累计的行应该尝试计算移动平均，规则如下：

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

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

窗口函数适用范围和注意事项

原则上，窗口函数只能在SELECT子句中使用。
窗口函数OVER 中的ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。

GROUPING运算符

ROLLUP - 计算合计及小计

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

效果如下
在这里插入图片描述

练习题

1

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

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

答：Current_max_price列的值是该行及之前行的sale_price的最大值

+------------+--------------+------------+-------------------+
| 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       | 圆珠笔       |        100 |              6800 |
+------------+--------------+------------+-------------------+

2

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

答：
窗口函数： 发现日期没有合并，如果在 FROM product后加GROUP BY regist_date则加总失败

    SELECT  regist_date
       ,SUM(sale_price) OVER (PARTITION BY regist_date ORDER BY regist_date ASC) AS date_sum
  FROM product;
  +-------------+----------+
| regist_date | date_sum |
+-------------+----------+
| NULL        |     4000 |
| 2008-04-28  |      880 |
| 2009-01-15  |     6800 |
| 2009-09-11  |      500 |
| 2009-09-20  |     4500 |
| 2009-09-20  |     4500 |
| 2009-09-20  |     4500 |
| 2009-11-11  |      100 |
+-------------+----------+
8 rows in set (0.00 sec)

不使用窗口函数： 得到理想结果

SELECT  regist_date
       ,SUM(sale_price) AS date_sum
  FROM product
  GROUP BY regist_date
  ORDER BY regist_date ASC;
  +-------------+----------+
| regist_date | date_sum |
+-------------+----------+
| NULL        |     4000 |
| 2008-04-28  |      880 |
| 2009-01-15  |     6800 |
| 2009-09-11  |      500 |
| 2009-09-20  |     4500 |
| 2009-11-11  |      100 |
+-------------+----------+
6 rows in set (0.00 sec)

3 思考题

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

答：窗口函数作用的对象以全体作为一类，如做售价排行，有PARTITION BY product_type的结果为

+--------------+--------------+------------+---------+
| product_name | product_type | sale_price | ranking |
+--------------+--------------+------------+---------+
| 圆珠笔       | 办公用品     |        100 |       1 |
| 打孔器       | 办公用品     |        500 |       2 |
| 叉子         | 厨房用具     |        500 |       1 |
| 擦菜板       | 厨房用具     |        880 |       2 |
| 菜刀         | 厨房用具     |       3000 |       3 |
| 高压锅       | 厨房用具     |       6800 |       4 |
| T恤衫        | 衣服         |       1000 |       1 |
| 运动T恤      | 衣服         |       4000 |       2 |
+--------------+--------------+------------+---------+
8 rows in set (0.00 sec)

而没有PARTITION BY product_type的结果为

+--------------+--------------+------------+---------+
| product_name | product_type | sale_price | ranking |
+--------------+--------------+------------+---------+
| 圆珠笔       | 办公用品     |        100 |       1 |
| 打孔器       | 办公用品     |        500 |       2 |
| 叉子         | 厨房用具     |        500 |       2 |
| 擦菜板       | 厨房用具     |        880 |       4 |
| T恤衫        | 衣服         |       1000 |       5 |
| 菜刀         | 厨房用具     |       3000 |       6 |
| 运动T恤      | 衣服         |       4000 |       7 |
| 高压锅       | 厨房用具     |       6800 |       8 |
+--------------+--------------+------------+---------+
8 rows in set (0.00 sec)

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

答：
以上一题代码为例，在最后加入ORDER BY ranking，则效果如下：

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

可以明显感受到再排序的实际意义并不大，反而还会使原来的分组显得混乱。当然在部分情况下有意义，如各班的前几名放在全年级的排名分别是什么样的分布情况

当只在ORDER BY语句中使用窗口函数时

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

发现首先不能设定别名，其次不能单独开列表现出排序的依据，因此窗口函数一般都只在SELECT子句中使用。

choconeverlate

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
学习笔记与练习 | SQL入门（五）|SQL高级处理

文章目录窗口函数窗口函数种类专用窗口函数聚合函数（窗口）GROUPING运算符ROLLUP - 计算合计及小计练习题窗口函数窗口函数也称为OLAP函数。OLAP 是OnLine AnalyticalProcessing 的简称，意思是对数据库数据进行实时分析处理。窗口函数的通用形式：<窗口函数> OVER ([PARTITION BY <列名>] ORDER BY <排序用列名>) *[]中的内容可以省略。PA
复制链接

扫一扫