SQL高级处理

5.1 窗口函数

5.1.1 窗口函数概念及基本的使用方法

窗口函数也称为OLAP函数。OLAP 是OnLine AnalyticalProcessing 的简称，意思是对数据库数据进行实时分析处理。
窗口函数的通用形式为：

<窗口函数> over ([partion by <列名>]
									order by <排序用列名>)

注意：
① [ ] 中的内容可以省略
② partition by 是用来分组的，即选择看哪个窗口，类似于group by 子句的分组功能，但是partition by 子句并不具备 group by 的汇总功能。并不会改变原始表中记录的行数。
③ order by 是用来排序的，即决定窗口内按哪个字段来排序的~也可以通过关键字ASC/DESC来指定升序/降序

5.2 窗口函数种类

5.2.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）

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

sum max min avg……
与之前用法一样，只是出来的结果是一个累计（当前所在行及之前所有行）的聚合函数值。

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

指定汇总范围，并使该汇总范围成为框架。

# preceding(“之前”) 将框架指定为“截止到之前 n行”，加上自身行
<窗口函数> over (order by <排序用列名>
				 rows n preceding)
# following(“之后”) 将框架指定为“截止到之后 n 行”，加上自身行
# between 1 preceding and 1 following 将框架指定为“之前1行”+“之后一行”+“自身”
<窗口函数> over (order by <排序用列名>
				 rows between n preceding and n following)		 

5.3.1 窗口函数适用范围和注意事项

• 原则上，窗口函数只能在 select 子句中使用。
• 窗口函数 over 中的 order by子句并不会影响最终结果的排序，其只是用来决定窗口函数按何种顺序计算。

5.4 GROUPING运算符

5.4.1 rollup - 计算合计及小计

zai group by 最后使用 with rollup 关键字不仅可以得到每个分类的小计，也可以得到分类的合计

练习题

5.1

请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。

select product_id,
	   product_name,
       sale_price,
       max(sale_price) over (order by product_id) as Current_max_price
from product;

从第一个的sale_price依次往后比，新增的一列Current_max_price中只保留当前最高的sale_price

5.2

继续使用product表，计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早
首先，是按登记日期排序所以order by 后面是登记日期，而计算的是sale_price的总额因此需要使用窗口函数中聚合函数的形式~~
注：求的是每日的销售单价的总额因此需要在group by 一下regist_date！！！不能partiton by 后面和 order by后面都写同一个字段哦~~

select product_name,
	   sale_price,
       regist_date,
	   sum(sale_price) over (order by regist_date) as sum_price
from product;

5.3

思考题
① 窗口函数不指定 PARTITION BY 的效果是什么？
【答】相当于在整个大窗口（所有字段）上进行聚合，排序……操作。
如下面两段代码及结果

② 为什么说窗口函数只能在 SELECT 子句中使用？实际上，在ORDER BY 子句使用系统并不会报错。
【答】因为窗口函数在order by后面的话没有什么意义，并不会输出什么结果。