目录
5.1 请说出针对本章中使用的 product(商品)表执行如下 SELECT 语句所能得到的结果。
5.4 使用存储过程创建20个与 shop.product 表结构相同的表,如下图所示:
Ch5.SQL高级处理
一、窗口函数
也称为OLAP函数。OLAP是OnLine Analytical Processing的简称,意思是对数据库数据进行实时分析处理。
<窗口函数> OVER ([PARTITION BY <列名>]
[ORDER BY <排序用列名> ASC/DESC])
·参数:
- PARTITON BY:可选参数,指示如何将查询行划分为组,类似于GROUP BY子句的分组功能,但是PARTITION BY子句并不具备GROUP BY子句的汇总功能,并不会改变原始表中记录的行数。
- ORDER BY:可选参数,指示如何对每个分区中的行进行排序,即决定窗口内,是按那种规则(字段)来排序的。
【注】虽然PARTITON BY子句和ORDER BY子句都是可选参数,但是两个参数不能同时没有(最少二选一)。
【注】窗口函数使用规则
①原则上,窗口函数只能在SELECT子句中使用。
②窗口函数OVER中的ORDER BY子句并不会影响最终结果的排序,其只是用来决定窗口函数按何种顺序计算。
1. 专用窗口函数
(1)RANK函数
计算排序时,如果存在相同位次的记录,则会跳过之后的位次。
例)有 3 条记录排在第 1 位时:1 位、1 位、1 位、4 位……
(2)DENSE_RANK函数
同样是计算排序,即使存在相同位次的记录,也不会跳过之后的位次。
例)有 3 条记录排在第 1 位时:1 位、1 位、1 位、2 位……
(3)ROW_NUMBER函数
赋予唯一的连续位次。
例)有 3 条记录排在第 1 位时:1 位、2 位、3 位、4 位
2. 聚合函数在窗口函数上的使用
聚合函数在窗口函数中的使用方法和之前的专用窗口函数一样,只是出来的结果是一个逐项累计到当前行的聚合函数值。
可以看出,聚合函数的结果是,按指定的排序,这里是product_id,当前所在行及之前所有的行的合计或均值,即累计到当前行的聚合。
3. 窗口函数的的应用——计算移动平均
聚合函数在窗口函数使用时,计算的是累积到当前行的所有的数据的聚合。实际上,还可以指定更加详细的汇总范围,该汇总范围称为框架(frame)。
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS n PRECEDING)
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS BETWEEN n PRECEDING AND n FOLLOWING)
PRECEDING(“之前”),将框架指定为“截止到之前n行”,加上自身行;
FOLLOWING(“之后”),将框架指定为“截止到之后n行”,加上自身行。
举例: