Task05:窗口函数等–天池龙珠计划SQL训练营
原教程链接
-
建议参考学习书籍: 《SQL基础教程》
窗口函数
窗口函数也称为OLAP函数,OLAP 是OnLine AnalyticalProcessing 的简称,意思是对数据库数据进行实时分析处理。
-
常规的SELECT语句都是对整张表进行查询,而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。
窗口函数的通用形式:
<窗口函数> OVER ([PARTITION BY <列名>] ORDER BY <排序用列名>)
-
实例:
-
SELECT product_name ,product_type ,sale_price ,RANK() OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking FROM product
-
本例中,就是 按product_type分组,然后在分组内,按sale_price排序。注意,这里按product_type分组并不会把某个分组的合并会一行。
-
结果:
-
窗口函数的使用位置
一般只能在select子句中使用
- 语法上,除了SELECT子句,ORDER BY子句或者UPDATE语句的SET子句中也可以使用。但因为几乎没有实际的业务示例,所以开始的时候大家只要记得“只能在SELECT子句中使用”就可以了。
Why?
- 在DBMS 内部,窗口函数是对WHERE 子句或者GROUPBY 子句处理后的“结果”进行的操作
窗口函数分类
大体可以分为2类:
-
一是 将SUM、MAX、MIN等聚合函数用在窗口函数中
-
二是 RANK、DENSE_RANK等排序用的专用窗口函数
专用窗口函数:
-
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 FROM Product;
- 结果:计算出商品编号“小于自己”的商品的销售单价的合计值。
计算移动平均
可以对包含在窗口内的行进行更加详细的汇总范围指定:汇总范围称为 框架。
-
实例
-
-- 代码清单8-6 指定“最靠近的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 2 PRECEDING
前2行以及此行,或:往上最靠近的3行- 这里我们使用了ROWS(“行”)和PRECEDING(“之前”)两个关键字,将框架指定为“截止到之前~ 行”,因此“ROWS 2 PRECEDING”就是将框架指定为“截止到之前2 行”,也就是将作为汇总对象的记录限定为如下的“最靠近的3 行”。
-
如果将条件中的数字变为“ROWS 5 PRECEDING”,就是“截止到之前5 行”(最靠近的6 行)的意思
-
-
这样的统计方法称为移动平均(moving average)
类似的:
GROUPING运算符
此处只介绍了ROLLUP,其余请参考开头推荐的书籍
-
GROUPING 运算符包含以下3 种
-
ROLLUP
-
可以同时计算出:小计行 & 合计行
-
实例:
-
SELECT product_type, regist_date, SUM(sale_price) AS sum_price FROM Product GROUP BY product_type, regist_date with ROLLUP;
-
-
习题解答:
5.2
继续使用product表,计算出按照登记日期(regist_date)升序进行排列的各日期的销售单价(sale_price)的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位(也就是将其看作比其他日期都早)
答:
-- 不对null进行排列在第一行的处理的答案:
SELECT product_type,regist_date,SUM(sale_price) AS sum_price
FROM product
GROUP BY regist_date,product_type WITH ROLLUP ;