Sql中的窗口函数

案例

原始数据

入门程序

写法一：
select 
province,city,gdp,people, rank() over (partition by province order by gdp) as ranking 
from windowfunc;

写法二：
select 
province,city,gdp,people,rank() over  w1 as ranking 
from windowfunc 
window w1 as (partition by province order by gdp);

大致解读：
根据province字段进行分区，也就是将province字段相同的值划分到同一个窗口之中，在每一个窗口之中根据gdp字段进行排行，得到每个省份各自的城市的gdp排行。

入门总结

窗口函数有两种：

1. 直接在over关键字之后添加窗口函数的定义；
2. 预先定义一个窗口函数，然后在over关键字之后直接使用定义的窗口函数即可。

基本格式：

[window_name] [partition_clause] [order_clause][frame_clause]

各部分解读

window_name：预先定义的窗口函数
partition_clause：书写格式为partition by properties_name,即将全部数据划分按照字段名进行划分窗口，多个字段名之间用逗号进行分隔。如果没有指定字段，则所有数据属于一个窗口。
order_clause：书写格式为order by properties_name.即在每一个窗口之内根据指定的字段名进行排序，多个字段之间用逗号进行分隔。同时可以desc和asc关键字来指定排序的顺序。
frame_clause：用来指定窗口中的操作范围。
如以上四部分都不存在，则窗口为所有查询行

非聚合窗口函数

名称	描述
CUME_DIST()	累计分布值
DENSE_RANK()	当前行在其窗口内的排名
FIRST_VALUE()	窗口中的第一个值
LAG()	窗口中当前行滞后的参数值
LAST VALUE()	窗口中的最后一个值
LEAD()	窗口内当前行的行的参数值
NTH VALUE	窗口内的第N个值
NTILE	窗口中当前行的存储桶号
PRRCENT RANK()	百分比排名值
RANK()	当前行在分区内的排名
ROW NUMBER()	窗口中的数据行数

演示：

数据

执行语句

select 
sex,height,
cume_dist() over win as 'cume_dist',
first_value(height) over win as 'first',
last_value(height) over win as 'last',
nth_value(height,2) over win as 'second',
rank() over win as 'rank',
dense_rank() over win as 'dense_rank',
percent_rank() over win as 'percent_rank',
row_number() over win as 'row_number',
ntile(4) over win as 'ntile',
lag(height) over win as 'lag',
lead(height) over win as 'lead'
 from man  
 window win as (partition by sex order by height);

结果

CUME_DIST()

返回窗口内小于等于当前值的比例。即小于等于当前值的数据个数/窗口内数据量。

first_value(properties_name)

返回窗口内指定字段名的第一个值

last_value(properties_name)

返回窗口内指定字段名的最后一个值

nth_value(properties_name,N)

返回窗口内指定字段名的第N个值

rank()

对窗口内的数据进行排序，返回其排名，相同的排名一致，同时占用后面的排名

dense_rank()

对窗口内的数据进行排序，返回其排名，相同的排名一致，但是不占用后面的排名

percent_rank()

在dense_rank()的前提，将排名转化为百分比。即当前值的排名/最后一名的排名

row_number()

返回窗口内当前行的编号，编号从1开始

ntile(N)

将窗口内的数据分成N个桶，同时返回每个数据所在的桶编号。桶可以更小的分区

lag(properties_name,N,default_value)

窗口内，当前行之前的第N个值，若不存在，则使用default_value。在不指定的情况下，N = 1，default_value=null。N必须大于等于0

lead(properties_name,N,default_value)

窗口内，当前行之后的第N个值，若不存在，则使用default_value。在不指定的情况下，N = 1，default_value=null。N必须大于等于0

frame_clause

用来在窗口之中划分一块更小的子集，然后进行运算。

格式

frame_units frame_extent

frame_units:rows | range
frame_extent:{frame_start | frame_between}
	frame_between:between frame_start and frame_end
	frame_start/frame_end:{
		current row
		unbounded preceding
		unbounded following
		express preceding
		express following
		}
		
在frame_between中，frame_start必须早于frame_end

案例

select sex, height, 
min(height) over (partition by sex order by height) as 'min',
min(height) over (partition by sex order by height rows between 1 preceding and 1 following) as 'min_row_1_preceing_1_following',
max(height) over (partition by sex order by height range unbounded preceding) as 'range_max_unbounder_preceding' from man
;

默认情况

在没有frame子句的情况下，默认框架取决于是否存在order by子句

1. 使用order by：默认frame_clause是从窗口的开始到当前的所有行
2. 不使用order by：默认frame_clause是包含窗口的所有数据

案例

select sex,height,
max(height) over (partition by sex order by height) as 'max_with_order',
max(height) over (partition by sex) as 'max_without_order' 
from man;