MySQL学习（五）SQL高级处理&练习题

最新推荐文章于 2025-01-09 10:23:19 发布

原创最新推荐文章于 2025-01-09 10:23:19 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #学习

本文深入探讨了MySQL中的窗口函数，包括其概念、应用和分类，如RANK、DENSE_RANK和ROW_NUMBER。同时，介绍了存储过程和函数的创建及参数类型，以及预处理声明PREPARE Statement的使用。并提供了相关练习题，帮助读者巩固学习。

窗口函数

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

为了便于理解，称之为 窗口函数。常规的SELECT语句都是对整张表进行查询，而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。
窗口函数的通用形式：
<窗口函数> OVER ([PARTITION BY <列名>] ORDER BY <排序用列名>)
其中， [ ]中的内容可以省略。
PARTITON BY是用来分组，即选择要看哪个窗口，类似于 GROUP BY 子句的分组功能，但是 PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。
ORDER BY 是用来排序，即决定窗口内，是按那种规则(字段)来排序的。

窗口函数种类
一是将SUM、MAX、MIN等聚合函数用在窗口函数中
二是 RANK、DENSE_RANK等排序用的专用窗口函数。专用窗口函数包括：
RANK函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
DENSE_RANK函数：同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
ROW_NUMBER函数：赋予唯一的连续位次。例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位
窗口函数的的应用 - 计算移动平均
聚合函数在窗口函数使用时，计算的是累积到当前行的所有的数据的聚合。实际上，还可以指定更加详细的汇总范围。该汇总范围成为框架 (frame)。
语法
<窗口函数> OVER (ORDER BY <排序用列名> ROWS n PRECEDING )
<窗口函数> OVER (ORDER BY <排序用列名> ROWS BETWEEN n PRECEDING AND n FOLLOWING)
PRECEDING（“之前”）将框架指定为 “截止到之前 n 行”，加上自身行
FOLLOWING（“之后”）将框架指定为 “截止到之后 n 行”，加上自身行
BETWEEN 1 PRECEDING AND 1 FOLLOWING将框架指定为 “之前1行” + “之后1行” + “自身”
注意事项
1、原则上，窗口函数只能在SELECT子句中使用。
2、窗口函数OVER 中的ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算

存储过程和函数

创建一个存储例程（一个存储过程或函数）基本语法：

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

存储过程和函数的参数有三类，分别是：IN，OUT，INOUT，其中：

IN 是入参。每个参数默认都是一个 IN 参数。如需设定一个参数为其他类型参数，请在参数名称前使用关键字 OUT 或 INOUT 。一个IN参数将一个值传递给一个过程。存储过程可能会修改这个值，但是当存储过程返回时，调用者不会看到这个修改。
OUT 是出参。一个 OUT 参数将一个值从过程中传回给调用者。它的初始值在过程中是 NULL ，当过程返回时，调用者可以看到它的值。
INOUT ：一个 INOUT 参数由调用者初始化，可以被存储过程修改，当存储过程返回时，调用者可以看到存储过程的任何改变。

预处理声明 PREPARE Statement

使用步骤如下：

PREPARE – 准备需要执行的语句预处理声明。
EXECUTE – 执行预处理声明。
DEALLOCATE PREPARE – 释放预处理声明。
基本语法：
PREPARE stmt_name FROM preparable_stmt

练习题

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;

答：在这里插入图片描述该结果表明Current_max_price的值返回的是当前product_id和之前商品售价中的最大值，其中product_id按升序排列。

5.2**

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

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

在这里插入图片描述