窗口函数
窗口函数也称为OLAP函数。OLAP 是OnLine AnalyticalProcessing 的简称,意思是对数据库数据进行实时分析处理。
窗口函数的通用形式:
<窗口函数> OVER ([PARTITION BY <列名>]
ORDER BY <排序用列名>)
*[]中的内容可以省略。
PARTITON BY
是用来分组,即选择要看哪个窗口,类似于GROUP BY
子句的分组功能,但是PARTITION BY
子句并不具备GROUP BY 子句的汇总功能,并不会改变原始表中记录的行数。ORDER BY
是用来排序,即决定窗口内,是按哪种规则(字段)来排序的。- 窗口函数中的
ORDER BY
与SELECT
语句末尾的ORDER BY
一样,可以通过关键字ASC/DESC来指定升序/降序。省略该关键字时会默认按照ASC
窗口函数种类
专用窗口函数
RANK()函数
计算排序时,如果存在相同位次的记录,则会跳过之后的位次。
有 3 条记录排在第 1 位时:1 位、1 位、1 位、4 位……
DENSE_RANK()函数
同样是计算排序,即使存在相同位次的记录,也不会跳过之后的位次。
有 3 条记录排在第 1 位时:1 位、1 位、1 位、2 位……
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 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;
那么如果想更改累计的行应该尝试计算移动平均,规则如下:
- PRECEDING(“之前”), 将框架指定为 “截止到之前 n 行”,加上自身行
- FOLLOWING(“之后”), 将框架指定为 “截止到之后 n 行”,加上自身行
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS n PRECEDING )
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS BETWEEN n PRECEDING AND n FOLLOWING)
窗口函数适用范围和注意事项
- 原则上,窗口函数只能在SELECT子句中使用。
- 窗口函数OVER 中的
ORDER BY
子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。
GROUPING运算符
ROLLUP - 计算合计及小计
SELECT product_type
,regist_date
,SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type, regist_date WITH ROLLUP
效果如下
练习题
1
请说出针对本章中使用的 product(商品)表执行如下 SELECT 语句所能得到的结果。
SELECT product_id
,product_name
,sale_price
,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
FROM product
答:Current_max_price列的值是该行及之前行的sale_price的最大值
+------------+--------------+------------+-------------------+
| product_id | product_name | sale_price | Current_max_price |
+------------+--------------+------------+-------------------+
| 0001 | T恤衫 | 1000 | 1000 |
| 0002 | 打孔器 | 500 | 1000 |
| 0003 | 运动T恤 | 4000 | 4000 |
| 0004 | 菜刀 | 3000 | 4000 |
| 0005 | 高压锅 | 6800 | 6800 |
| 0006 | 叉子 | 500 | 6800 |
| 0007 | 擦菜板 | 880 | 6800 |
| 0008 | 圆珠笔 | 100 | 6800 |
+------------+--------------+------------+-------------------+
2
继续使用product表,计算出按照登记日期(regist_date)升序进行排列的各日期的销售单价(sale_price)的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位(也就是将其看作比其他日期都早)
答:
窗口函数: 发现日期没有合并,如果在 FROM product
后加GROUP BY regist_date
则加总失败
SELECT regist_date
,SUM(sale_price) OVER (PARTITION BY regist_date ORDER BY regist_date ASC) AS date_sum
FROM product;
+-------------+----------+
| regist_date | date_sum |
+-------------+----------+
| NULL | 4000 |
| 2008-04-28 | 880 |
| 2009-01-15 | 6800 |
| 2009-09-11 | 500 |
| 2009-09-20 | 4500 |
| 2009-09-20 | 4500 |
| 2009-09-20 | 4500 |
| 2009-11-11 | 100 |
+-------------+----------+
8 rows in set (0.00 sec)
不使用窗口函数: 得到理想结果
SELECT regist_date
,SUM(sale_price) AS date_sum
FROM product
GROUP BY regist_date
ORDER BY regist_date ASC;
+-------------+----------+
| regist_date | date_sum |
+-------------+----------+
| NULL | 4000 |
| 2008-04-28 | 880 |
| 2009-01-15 | 6800 |
| 2009-09-11 | 500 |
| 2009-09-20 | 4500 |
| 2009-11-11 | 100 |
+-------------+----------+
6 rows in set (0.00 sec)
3 思考题
① 窗口函数不指定PARTITION BY的效果是什么?
答:窗口函数作用的对象以全体作为一类,如做售价排行,有PARTITION BY product_type
的结果为
+--------------+--------------+------------+---------+
| product_name | product_type | sale_price | ranking |
+--------------+--------------+------------+---------+
| 圆珠笔 | 办公用品 | 100 | 1 |
| 打孔器 | 办公用品 | 500 | 2 |
| 叉子 | 厨房用具 | 500 | 1 |
| 擦菜板 | 厨房用具 | 880 | 2 |
| 菜刀 | 厨房用具 | 3000 | 3 |
| 高压锅 | 厨房用具 | 6800 | 4 |
| T恤衫 | 衣服 | 1000 | 1 |
| 运动T恤 | 衣服 | 4000 | 2 |
+--------------+--------------+------------+---------+
8 rows in set (0.00 sec)
而没有PARTITION BY product_type
的结果为
+--------------+--------------+------------+---------+
| product_name | product_type | sale_price | ranking |
+--------------+--------------+------------+---------+
| 圆珠笔 | 办公用品 | 100 | 1 |
| 打孔器 | 办公用品 | 500 | 2 |
| 叉子 | 厨房用具 | 500 | 2 |
| 擦菜板 | 厨房用具 | 880 | 4 |
| T恤衫 | 衣服 | 1000 | 5 |
| 菜刀 | 厨房用具 | 3000 | 6 |
| 运动T恤 | 衣服 | 4000 | 7 |
| 高压锅 | 厨房用具 | 6800 | 8 |
+--------------+--------------+------------+---------+
8 rows in set (0.00 sec)
② 为什么说窗口函数只能在SELECT子句中使用?实际上,在ORDER BY 子句使用系统并不会报错。
答:
以上一题代码为例,在最后加入ORDER BY ranking
,则效果如下:
mysql> SELECT product_name ,product_type ,sale_price ,RANK() OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking FROM product
-> ORDER BY ranking;
+--------------+--------------+------------+---------+
| product_name | product_type | sale_price | ranking |
+--------------+--------------+------------+---------+
| 圆珠笔 | 办公用品 | 100 | 1 |
| 叉子 | 厨房用具 | 500 | 1 |
| T恤衫 | 衣服 | 1000 | 1 |
| 打孔器 | 办公用品 | 500 | 2 |
| 擦菜板 | 厨房用具 | 880 | 2 |
| 运动T恤 | 衣服 | 4000 | 2 |
| 菜刀 | 厨房用具 | 3000 | 3 |
| 高压锅 | 厨房用具 | 6800 | 4 |
+--------------+--------------+------------+---------+
8 rows in set (0.00 sec)
mysql> SELECT product_name ,product_type ,sale_price ,RANK() OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking FROM product
-> ORDER BY sale_price;
+--------------+--------------+------------+---------+
| product_name | product_type | sale_price | ranking |
+--------------+--------------+------------+---------+
| 圆珠笔 | 办公用品 | 100 | 1 |
| 打孔器 | 办公用品 | 500 | 2 |
| 叉子 | 厨房用具 | 500 | 1 |
| 擦菜板 | 厨房用具 | 880 | 2 |
| T恤衫 | 衣服 | 1000 | 1 |
| 菜刀 | 厨房用具 | 3000 | 3 |
| 运动T恤 | 衣服 | 4000 | 2 |
| 高压锅 | 厨房用具 | 6800 | 4 |
+--------------+--------------+------------+---------+
8 rows in set (0.00 sec)
可以明显感受到再排序的实际意义并不大,反而还会使原来的分组显得混乱。当然在部分情况下有意义,如各班的前几名放在全年级的排名分别是什么样的分布情况
当只在ORDER BY
语句中使用窗口函数时
mysql> SELECT product_name ,product_type ,sale_price FROM product ORDER BY RANK() OVER (PARTITION BY product_type ORDER BY sale_price) ;
+--------------+--------------+------------+
| product_name | product_type | sale_price |
+--------------+--------------+------------+
| 圆珠笔 | 办公用品 | 100 |
| 叉子 | 厨房用具 | 500 |
| T恤衫 | 衣服 | 1000 |
| 打孔器 | 办公用品 | 500 |
| 擦菜板 | 厨房用具 | 880 |
| 运动T恤 | 衣服 | 4000 |
| 菜刀 | 厨房用具 | 3000 |
| 高压锅 | 厨房用具 | 6800 |
+--------------+--------------+------------+
8 rows in set (0.00 sec)
发现首先不能设定别名,其次不能单独开列表现出排序的依据,因此窗口函数一般都只在SELECT子句中使用。