MySQL的高级处理

窗口函数（OLAP函数）

窗口函数的概念的概念和基本使用

所谓的窗口函数实际上就是对数据库中的数据进行处理。select语句是对表的整体进行处理而窗口函数是对表的局部进行处理。

窗口函数的基本形式如下：

'窗口函数' OVER (PARTITION BY '列表名' ORDER BY '排序用列名')

PARTITON BY和ORDER BY的作用

**PARTITON BY 子句与GROUP BY 子句分组功能相似**

ORDER BY 子句是对分区中的行进行排序

我们下面就来看一个例子：

SELECT student_id, exam_date, exam_score,
 ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY exam_date) 
  AS row_num 
  FROM student_data;

首先可以创建一个表student_data包含学生数据，其中包括student_id（学生学号）、exam_date（考试日期）和exam_score（考试成绩）字段。

ROW_NUMBER()函数用于为每个学生学号进行分组内的考试日期进行排序并分配一个序号。
PARTITION BY student_id指定了按照student_id列进行分组。
ORDER BY exam_date指定了按照考试日期对每个学生的考试记录进行排序。
执行上述查询后，将得到一个结果集，其中包含每个学生的考试记录以及为每个学生分配的行号（row_num）。

通过这样的查询，我们可以更方便地对学生的考试数据进行分析，了解每个学生的考试顺序。窗口函数在这里帮助对数据进行排名操作。

窗口函数的种类

窗口函数种类可以简单分为两类，聚合函数以及排序专用的函数。

MySQL中的聚合函数和Excel中的聚合函数相似。

专用窗口函数

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

SELECT ROW_NUMBER() OVER (ORDER BY column_name) 
AS row_number, column_name 
FROM table_name;

SELECT：指定需要遍历的列。

这里的ROW_NUMBER()函数来为结果集中的每行分配一个唯一的行号。

ORDER BY column_name指定按照某一列的值进行排序，每行会根据指定的列的值来分配行号。AS row_number是重命名为row_number。

column_name：查询结果集中的其他列。

FROM table_name：指定要从什么表中来。

PANK函数：计算排序，存在相同为位次，会直接跳过重复的数据，会跳过之后的位次。

SELECT：指定需要检索的列。
RANK() OVER (ORDER BY column_name) 
AS rank column_name
FROM table_name;

SELECT：指定需要遍历的列。

这里使用RANK()函数来为结果集中的每行分配一个排名。ORDER BY column_name指定按照某一列的值进行排序，这样每行会根据指定的列的值确定排名。AS rank为生成的排名指定别名为rank。

column_name：查询结果集中的其他列。

table_name：指定要从中遍历数据的表。

DENSE_RANK函数：计算排序，存在相同为位次，不会直接跳过重复的数据，而是会正常输出。

SELECT DENSE_RANK() OVER (ORDER BY column_name) 
AS dense_rank, column_name 
FROM table_name;

SELECT：指定需要遍历的列。

这里使用DENSE_RANK()函数来为结果集中的每行分配一个密集排名。ORDER BY column_name指定按照某一列的值进行排序，这样每行会根据指定的列的值确定密集排名。AS dense_rank为生成的密集排名指定别名为dense_rank。

column_name：查询结果集中的其他列。

FROM table_name：指定要从中遍历数据的表。

窗口函数的应用

实际上就是对于窗口函数的说法进行更为详细的解释称之为汇总范围，也就是所谓的框架

具体的语法格式如下：

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

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

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

下面我们就来看一下具体的例子：

一、计算每个学生的前n个分数的平均分数：

SELECT student_id, student_name, score,
AVG(score) OVER (PARTITION BY student_id ORDER BY score ROWS 2 PRECEDING) 
AS avg_score
FROM student_scores;

上述这串代码，我们使用了AVG()窗口函数来计算每个学生在分数排名表现上的前n个分数的平均分数。PARTITION BY student_id表示按照学生学号进行分组，ORDER BY score表示根据分数进行排序，ROWS 2 PRECEDING表示从当前行向前包括自身最近的两行进行计算其平均分数。avg_score为计算得到的平均分数列。

二、计算每个学生的前n个分数和后n个分数的平均分数：

SELECT student_id, student_name, score,
AVG(score) OVER (PARTITION BY student_id ORDER BY score ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 
AS avg_score
FROM student_scores;

上述这串代码中，使用了AVG()窗口函数来计算每个学生在分数排名表现上的前一个分数和后一个分数的平均分数。

PARTITION BY student_id表示按照学生ID进行分组，ORDER BY score表示根据分数进行排序，

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING表示计算当前行的前一个行和后一个行的分数平均值。avg_score为计算得到的平均分数列。

存储过程的函数

基本语法格式：

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

这语法格式只管看起来的话是有些抽象的，下面我们就通过一个例子进行具体的说明。

DELIMITER //
CREATE PROCEDURE GetStudentInfo (IN student_id INT)
BEGIN
DECLARE student_name VARCHAR(50);
DECLARE student_grade VARCHAR(10);

SELECT student_name, grade INTO student_name, student_grade 
FROM students 
WHERE student_id = student_id;

SELECT CONCAT('Student Name: ', student_name) AS 'Student Info',CONCAT('Grade: ', student_grade) AS 'Student Info';
END //
DELIMITER ;

假设我们有一个名为students的表，包含以下字段：student_id（学生学号）、student_name（学生姓名）和grade（年级）。创建一个存储过程，用于根据输入的学生学号查找并打印学生的姓名和年级。

在上面的存储过程GetStudentInfo（获取学生信息）中，我们声明了两个变量student_name和student_grade,然后从students表中根据输入的学生学号查询学生的姓名和年级。最后，打印出学生的姓名和年级。

本笔记是本人在学习数据库时整理的，由于学习能力属实有限，可能无法做到面面俱到，还望理解。