task5窗口函数

5.1.1窗口函数概念及基本的使用方法
窗口函数也称为OLAP函数,是对数据库数据进行实时分析处理。
常规的SELECT语句都是对整张表进行查询,而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。
通用形式:<窗口函数> OVER ([PARTITION BY <列名>]
ORDER BY <排序用列名>)
[ ]中的内容可以省略
最关键的是搞明白关键字 PARTITON BY 和 ORDER BY 的作用。
PARTITON BY 是用来分组,即选择要看哪个窗口,类似于 GROUP BY 子句的分组功能,但是不具备汇总功能,不会改变原始表中的记录行数。
ORDER BY用于排序,决定窗口内的字段排序
例如:SELECT product_name,product_type,sale_price,RANK() over (partition by product_type ORDER BY sale_price) AS ranking
FROM product;


oder by可以指定某一列进行某种顺序进行排序,可以通过关键字asc/desc来进行升序或者降序

5.2 窗口函数种类
窗口函数可以分为两类:SUM、MAX、MIN等聚合函数用在窗口函数中
RANK、DENSE_RANK等排序用的专用窗口函数

5.2.1 专用窗口函数
RANK函数:计算排序时,如果存在相同位次的记录,则会跳过之后的位次
DENSE_RANK函数:在RANK基础上不会跳过之后的位次
ROW_NUMBER函数:
赋予唯一的连续位次。

5.2.2 聚合函数在窗口函数上的使用
聚合函数使用方法和专用窗口函数一样,但是出来的结果是一个累计的聚合函数值。
代码: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;

图片:


总结:聚合函数是指按照我们的排序累计之前行的合计或均值。

5.3 窗口函数的的应用 - 计算移动平均
除了在使用窗口函数时,计算的是累计到当前行的所有数据的聚合,还可以指定更加详细的汇总范围称为框架(frame)

PRECEDING(“之前”), 将框架指定为 “截止到之前 n 行”,加上自身行
FOLLOWING(“之后”), 将框架指定为 “截止到之后 n 行”,加上自身行
BETWEEN 1 PRECEDING AND 1 FOLLOWING,将框架指定为 “之前1行” + “之后1行” + “自身”
结果:

5.3.1 窗口函数适用范围和注意事项
原则上,窗口函数只能在SELECT子句中使用
窗口函数OVER 中的ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算

5.4 GROUPING运算符
5.4.1 ROLLUP - 计算合计及小计
GROUP BY 只能得到每个分类的小计,但是ROLLUP关键字可以计算分类的合计

5.5 存储过程和函数
5.5.1 基本介绍
语句:[delimiter //]($$,可以是其他特殊字符)
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,…]])
[characteristic …]
[BEGIN]
routine_body
[END//]($$,可以是其他特殊字符)
默认情况下,一个存储例程与默认数据库相关联。要将该例程明确地与一个给定的数据库相关联,需要在创建该例程时将其名称指定为 db_name.sp_name。
使用 CALL 语句调用一个存储过程。而要调用一个存储的函数时,则要在表达式中引用它。在表达式计算期间,该函数返回一个值。

routine_body 由一个有效的SQL例程语句组成。它可以是一个简单的语句,如 SELECT 或 INSERT,或一个使用 BEGIN 和 END 编写的复合语句。
复合语句可以包含声明、循环和其他控制结构语句。在实践中,存储函数倾向于使用复合语句,除非例程主体由一个 RETURN 语句组成

5.5.2 参数介绍
存储过程和函数的参数有三类,分别是:IN,OUT,INOUT
IN 是入参,OUT 是出参,INOUT :一个 INOUT 参数由调用者初始化,可以被存储过程修改,当存储过程返回时,调用者可以看到存储过程的任何改变

5.6 预处理声明 PREPARE Statement
当MySQL使用不同的 productCode 值执行此查询时,它不必完全解析查询。因此,这有助于MySQL更快地执行查询,特别是当MySQL多次执行相同的查询时
由于预准备语句使用占位符 (),这有助于避免 SQL 注入的许多变体,从而使应用程序更安全。

5.6.1 使用步骤
1、PREPARE – 准备需要执行的语句预处理声明。
2、EXECUTE – 执行预处理声明。
3、DEALLOCATE PREPARE – 释放预处理声明。

作业:
5.1:语句:SELECT product_id,product_name,sale_price,MAX(sale_price) over (ORDER BY product_id) AS CURRENT_max_price FROM product;


5.2:语句:
SELECT regist_date,
SUM(sale_price) over (ORDER BY regist_date) AS current_sum_price
FROM product
ORDER BY regist_date;


5.3:
1.PARTITION BY 能够设定窗口对象范围,不具备汇总功能,要选择哪个窗口。不指定的话会将数据不分类

2.窗口函数一般用于where和groupby字句中进行处理,原则上只能在select中使用
5.4一直是出错没有正确的产出

浙ICP备19012682号