1 简介
窗口函数也称为OLAP 函数A。为了让大家快速形成直观印象,才起了这样一个容易理解的名称。OLAP 是OnLine Analytical Processing 的简称,意思是对数据库数据进行实时分析处理。例如,市场分析、创建财务报表、创建计划等日常性商务工作。窗口函数就是为了实现OLAP 而添加的标准SQL 功能。
2 窗口函数语法
基本语句
<窗口函数> OVER ([PARTITION BY <列清单>]
ORDER BY <排序用列清单>)
其中[]中的内容可以省略。
## 能够作为窗口函数的函数
- 能够作为窗口函数的聚合函数(SUM、AVG、COUNT、MAX、MIN)
- RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数
## 语法的使用方法
### 专用窗口函数
rank函数
SELECT product_name, product_type, sale_price,
RANK () OVER (PARTITION BY product_type
ORDER BY sale_price) AS ranking
FROM Product;
PARTITION BY 能够设定排序的对象范围。本例中,为了按照商品种类进行排序,我们指定了product_type。ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列,我们指定了sale_price。此外,窗口函数中的ORDER BY 与SELECT 语句末尾的ORDER BY 一样,可以通过关键字ASC/DESC 来指定升序和降序。省略该关键字时会默认按照ASC,也就是升序进行排序。
使用窗口函数时起到关键作用的是PARTITION BY 和GROUP BY。其中,PARTITION BY 并不是必需的,即使不指定也可以正常使用窗口函数。
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;
之前我们得到的是按照商品种类分组后的排序,而这次变成了全部商品的排序。像这样,当希望先将表中的数据分为多个部分(窗口),再使用窗口函数时,可以使用PARTITION BY 选项。
## 窗口函数区别
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 位……
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking,
DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking,
ROW_NUMBER () OVER (ORDER BY sale_price) AS row_num
FROM Product;
注意点:原则上窗口函数只能在SELECT子句中使用。因为在DBMS 内部,窗口函数是对WHERE 子句或者GROUP BY 子句处理后的“结果”进行的操作。大家仔细想一想就会明白,在得到用户想要的结果之前,即使进行了排序处理,结果也是错误的。在得到排序结果之后,如果通过WHERE 子句中的条件除去了某些记录,或者使用GROUP BY 子句进行了汇总处理,那好不容易得到的排序结果也无法
使用了。
### 作为窗口函数使用的聚合函数
先来看一个将SUM 函数作为窗口函数使用的例子。
SELECT product_id, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;
实现了累加和。
使用AVG
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id) AS current_avg
FROM Product;
实现了求当前记录的平均值。
### 实现移动平均
指定“最靠近的3行”作为汇总对象
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM Product;
这里我们使用了ROWS(“行”)和PRECEDING(“之前”)两个关键字,将框架指定为“截止到之前~ 行”,因此“ROWS 2 PRECEDING”就是将框架指定为“截止到之前2 行”,也就是将作为汇总对象的记录限定为如下的“最靠近的3 行”。
使用关键字FOLLOWING(“之后”)替换PRECEDING,就可以指定“截止到之后~ 行”作为框架了。
如果希望将当前记录的前后行作为汇总对象时,就可以像代码清单8-7那样,同时使用PRECEDING(“之前”)和FOLLOWING(“之后”)关键字来实现。
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS BETWEEN 1 PRECEDING AND
1 FOLLOWING) AS moving_avg
FROM Product;
在上述代码中,我们通过指定框架,将“1 PRECEDING”(之前1 行)和“1 FOLLOWING”(之后1 行)的区间作为汇总对象。具体来说,就是将如下3 行作为汇总对象来进行计算。