窗口函数(OLAP 函数 OnLine Analytical Processing):兼具分组和排序两种功能,但PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能,因此,不会减少原表中记录的行数。
<窗口函数> OVER ([PARTITION BY <列清单>]
ORDER BY <排序用列清单>)
PARTITION BY 能够设定排序的对象范围。
ORDER BY 能够指定按照哪一列、何种顺序进行排序。
通过 PARTITION BY 分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思,而是代表范围。
能够作为窗口函数使用的函数
① 聚合函数(SUM、AVG、COUNT、MAX、MIN):需要在括号内指定作为汇总对象的列
SELECT product_id, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;
在按照时间序列的顺序,计算各个时间的销售额总额等的时候,通常都会使用这种称为累计的统计方法。
将聚合函数当作窗口函数使用时的最大特征:以“自身记录(当前记录)”作为基准进行统计
② RANK(1114)、DENSE_RANK(1112)、ROW_NUMBER (1234)等专用窗口函数,由于专用窗口函数无需参数,因此通常括号中都是空的。
窗口函数的适用范围:只能书写在SELECT 子句之中。理由:在 DBMS 内部,窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作。
移动平均
在窗口中指定更加详细的汇总范围的备选功能,该备选功能中的汇总范围称为框架。
指定框架(汇总范围)
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM Product;
截止到之前 2 行
框架是根据当前记录来确定的,因此和固定的窗口不同,其范围会随着当前记录的变化而变化。
两个ORDER BY
OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的,对结果的排列顺序并没有影响。
怎样才能让记录切实按照 ranking 列的升序进行排列呢?在 SELECT 语句的最后,使用 ORDER BY 子句进行指定。