文章目录
前言
本文章为天池“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.使用步骤
- PREPARE – 准备需要执行的语句预处理声明。
- EXECUTE – 执行预处理声明。
- DEALLOCATE PREPARE – 释放预处理声明。