窗口函数

窗口函数

一.简介

窗口函数又名开窗函数，属于分析函数的一种。其与聚合函数都可以对表内容进行统计分析，但与聚合函数不同的是，对于区组的统计，窗口函数可以返回区组内的每一行，而聚合函数对于每区组只能返回一行。

基本语句

function(arg1,…) over (partition by partiton_clause order by ordby_clause ) 

其中partition_clause为要按照其来分区的列，即目标分组列。function为功能函数，或者说可以使用窗口的函数。
如下函数可以使用窗口：

 - row_number() # 行号
 - rank(),dense_rank() # 排名和连续排名
 - lead(),lag() # 偏移函数
 - first_value(),last_value() # 区内首位行
 - ntile # 分桶
 - ratio_to_report() # 该行占区内和的百分比
 - max(),min(),avg(),sum() # 统计函数

高效！

窗口函数的具体使用

1.row_number()

基本语句：

row_number() over(partition by col1 order by col2);

上述语句表示，按照col1分区，各区内按照col2排序，同时表中增加新的字段，字段内容为该行在区内的行号。区内是连续唯一的。注意：使用row_number()时，必须有order_by_clause，否则会报错。

栗子：
首先建一张表。

create table test(id int,name varchar(10),sale int);
insert into test values(1,’aaa’,100);
insert into test values(1,’bbb’,200);
insert into test values(1,’ccc’,200);
insert into test values(1,’ddd’,300);
insert into test values(2,’eee’,400);
insert into test values(2,’fff’,200);
select * from test;

Ln	ID	NAME	SALE
1	1	aaa	100
2	1	bbb	200
3	1	ccc	200
4	1	ddd	300
5	2	eee	400
6	2	fff	200

row_number()函数使用。
1.有partition by子句时

select t.*,row_number() over(partition by id order by sale) rn from test t;

2.无partition by子句时，视全部记录为一个分组。

select t.*,row_number() over(order by sale) rn from test t;

2.rank() 和 dense_rank()

二者主要是计算区内的排序值。rank()是间断排序；dense_rank()是连续排序。
基本语句

rank() over(partition by partition_clause order by ordby_clause)
dense_rank() over(partition by partition_clause order by ordby_clause)

栗子

select t.*,rank() over(partition by id order by sale) rn from test t;

按照id分区，区内按照sale 排序，每条记录增加rn字段，字段内容为该记录在区内的间断排名（跳跃排名）。

select t.*,dense_rank() over(partition by id order by sale) rn from test t;

按照id分区，区内按照sale 排序，每条记录增加rn字段，字段内容为该记录在区内的连续排名。

3.lead() 和 lag()

偏移量函数：lead()是向下取值函数，若当前行是当前分区最后一条则显示null；lag()是向上取值函数，若当前行是当前分区第一条则显示null。

lead(EXPR,<OFFSET>,<DEFAULT>)
lag(EXPR,<OFFSET>,<DEFAULT>)

• EXPR通常是直接是列名，也可以是从其他行返回的表达式；
• OFFSET是默认为1，表示在当前分区内基于当前行的偏移行数；
• DEFAULT是在OFFSET指定的偏移行数超出了分组的范围时(此时会返回null)，可以通过设置这个字段来返回一个默认值来替代null。
  注意：这里如果只给了一个参数，那就是EXPR；如果给了两个参数，则为EXPR和OFFSET；如果给了三个参数，为EXPR\OFFSET\DEFAULT.

栗子

 select t.*,lead(sale) over(partition by id order by sale) rn from test t;

select t.*,lag(sale) over(partition by id order by sale) rn from test t;

专心！

4.first_value() 和 last_value()

二者分别返回区内的第一个值和最后一个值。
语法如下

first_value(expr) over(analytic_clause)
last_value(expr) over(analytic_clause)

栗子：

select t.*,first_value(sale) over(partition by id order by sale) rn from test t;

select t.*,last_value(sale) over(partition by id order by sale) rn from test t;

5.ntile

对一个数据分区中的有序结果集进行划分，将其分组到各个桶，并为每个小组分配一个唯一的组编号。
语法如下

ntile(ntile_num) over (analytic_clause)

其中，ntile_num需要大于0，且analytic_clause中必须有order by子句。

栗子

select t.*,ntile(3) over(order by sale) rn from test t;

6.ratio_to_report()

用来计算，该行的某个字段占该区此字段总和的百分比。此函数，不能跟order by子句。
语法如下

ratio_to_report(expr) over (query_partition_clause)

栗子

select t.*,ratio_to_report(sale) over(partition by id) rn from tes t;

7.统计函数max(),min(),avg(),sum(),count()

栗子

select t.*,sum(sale) over(partition by id order by sale) rn from test t;
select t.*,sum(sale) over(partition by id order by sale range between unbounding preceding and current row) rn from test t;

二者等价，即求当前行和之前的行的和。

select t.*,max(sale) over(partition by id order by sale) rn from test t;

求当前行和之前的行的最大值。

select t.*,min(sale) over(partition by id order by sale) rn from test t;

求当前行和之前的行的最小值。