8-1 窗口函数
什么是窗口函数
窗口函数也称为 OLAP (OnLine Analytical Processing)函数,意思是对数据库数据进行实时分析处理。例如,市场分析、创建财务报表、创建计划等日常性商务工作
窗口函数的语法
<窗口函数> 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 能够设定排序的对象范围
ORDER BY 能够指定按照哪一列、何种顺序进行排序
窗口函数兼具 GROUP BY 子句的分组功能以及ORDER BY 子句的排序功能。但是,PARTITION BY 子句并不具备GROUP BY 子句的汇总功能
通过 PARTITION BY 分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思,而是代表范围
无需指定PARTITION BY
/*
不指定PARTITION BY和使用没有 GROUP BY 的聚合函数时的效果一样,也就是将整个表作为一个大的窗口来使用
当希望先将表中的数据分为多个部分(窗口),再使用窗口函数时,可以使用 PARTITION BY 选项
*/
select product_name,product_type,sale_price,
rank() over (
order by sale_price) as ranking
from product;
专用窗口函数的种类
●RANK函数
计算排序时,如果存在相同位次的记录,则会跳过之后的位次。
例)有 3 条记录排在第 1 位时:1 位、1 位、1 位、4 位……
●DENSE_RANK函数
同样是计算排序,即使存在相同位次的记录,也不会跳过之后的位次。
例)有 3 条记录排在第 1 位时:1 位、1 位、1 位、2 位……
●ROW_NUMBER函数
赋予唯一的连续位次。
例)有 3 条记录排在第 1 位时:1 位、2 位、3 位、4 位……
-- 比较RANK、DENSE_RANK、ROW_NUMBER的结果
select product_name,product_type,sale_price,
rank() over (
order by sale_price) as ranking,
dense_rank () over (
order by sale_price) as dense_ran