“SQL编程语言”小组学习的Task05

---

前言

本文章为天池“SQL编程语言”小组学习的Task05-学习日志，主要学习内容为窗口函数，GROUPING运算符，存储过程和函数，预处理声明

---

一、窗口函数

1.概念及基本使用方法

定义：也称为OLAP函数，是对数据库数据进行实时分析处理，其可以有选择的去某一部分数据进行汇总、计算和排序。
语法：

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

其中：PARTITON BY 是用来分组，可省去，能够设定窗口对象范围。
ORDER BY 是用来排序， 能够指定按照哪一列、何种顺序进行排序（可以通过关键字ASC/DESC来指定升序/降序）

2.窗口函数种类

2.1专用窗口函数

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

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

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

2.2 聚合函数在窗口函数上的使用

聚合函数在窗口函数中的使用方法和之前的专用窗口函数一样，只是出来的结果是一个累计的聚合函数值。

2.3 窗口函数的的应用 - 计算移动平均

聚合函数在窗口函数使用时，计算的是累积到当前行的所有的数据的聚合。 实际上，还可以指定更加详细的汇总范围。该汇总范围成为 框架 (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行” + “自身”

二、GROUPING运算符

1.ROLLUP - 计算合计及小计

计算分类的合计，可以用 ROLLUP关键字，ROLLUP可以对多列进行汇总求小计和合计。
语法如下：

SELECT
FROM
GROUP BY A,B WITH ROLLUP;

三、存储过程和函数

1.基本语法

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

2.参数介绍

• IN 是入参。每个参数默认都是一个 IN 参数。如需设定一个参数为其他类型参数，请在参数名称前使用关键字 OUT 或 INOUT。一个IN参数将一个值传递给一个过程。存储过程可能会修改这个值，但是当存储过程返回时，调用者不会看到这个修改。

• OUT 是出参。一个 OUT 参数将一个值从过程中传回给调用者。它的初始值在过程中是 NULL ，当过程返回时，调用者可以看到它的值。

• INOUT ：一个 INOUT 参数由调用者初始化，可以被存储过程修改，当存储过程返回时，调用者可以看到存储过程的任何改变。

对于每个 OUT 或 INOUT 参数，在调用过程的 CALL 语句中传递一个用户定义的变量，以便在过程返回时可以获得其值。

四、预处理声明 PREPARE Statement

1.简介

当MySQL使用不同的 productCode 值执行此查询时，它不必完全解析查询。
例如：

SELECT * 
FROM products 
WHERE productCode = ?;

2.使用步骤

1. PREPARE – 准备需要执行的语句预处理声明。
2. EXECUTE – 执行预处理声明。
3. DEALLOCATE PREPARE – 释放预处理声明。