窗口函数
窗口函数可以进行排序,生成序列号等一般的聚合函数无法实现的高级操作。只有SELECT和ORDER BY 子句直接支持窗口函数。窗口函数也称为OLAP函数,即对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。窗口函数语法:其中[]中的内容可以省略
<窗口函数> over ([partition by <列清单>] order by <排序用列清单>)
窗口函数中的元素
窗口函数的行为描述出现在函数的OVER子句中,并涉及多个元素。3个核心元素分别是分区、排序和框架。不是所有的窗口函数都支持这3个元素。
分区
分区元素是由PARTITION BY子句定义,并被所有的窗口函数支持。对当前计算的窗口函数进行限制,仅仅那些在结果集的分区列中与当前行有相同值的行才能进入窗口。如果没有指定PARTITION BY子句,窗口就没有限制。换种说法就是:如果没有显示指定分区,则默认分区就是把整个查询结果集当作一个分区。同一个查询中的不同函数,可能会有不同的分区描述。
排序
排序元素定义计算的顺序,如果与分区有关,则是在分区内的顺序。针对不同的函数类别,排序元素有轻微的不同意义。对于排名函数,排序是直观的。而聚合窗口函数的排序和排名窗口的排序略有意义上的不同。在聚合中,排序与聚合的顺序无关;然而,排序元素为下面将描述的框架选项赋予一定的含义,换句话说,排序元素帮助限定在窗口内的行。
框架
从本质上来说,框架是一个在分区内对行进行进一步限制的筛选器。它适用于聚合窗口函数,也适用于三个偏移函数:FIRST_VALUE、LAST_VALUE、NTH_VALUE。把这个窗口元素想成是基于给定的排序,在当前行所在分区中定义两个点,这两个点形成的框架之间的行才会参与计算。
在标准的框架描述中,包含一个ROWS或RANGE选项,用来定义框架的开始行和结束行,这两行也可以形成“框架外”(框架内的行被排除在计算外)窗口选项。
ROWS选项允许用相对当前行的偏移行数来指定框架的起点和终点。RANGE选项更具灵活性,可以以框架起终点的值于当前行的值的差异来定义偏移行数。“框架外”窗口选项用来定义如何对当前行及具有相同值的行进行处置。
窗口函数类别
窗口函数大体可以分为:
【1】能够作为窗口函数的聚合函数(sum,avg,count,max,min)
【2】rank、dense_rank、row_number等专用窗口函数
【3】分布函数
【4】偏移函数
语法的基本使用方法:
rank函数
rank函数是用来计算记录排序的函数。
select product_name, product_type, sale_price,
rank