postgreSQL窗口函数总结

postgreSQL窗口函数总结 1

窗口函数说明 2

row_number/rank/dense_rank的区别 2

窗口函数语句 2

1 准备数据 3

1.1 创建测试的表test1 3

1.2 插入数据到test1表中 3

2 rank over 窗口函数使用 3

2.1 按照分区查看每行的个数 3

2.2 按照分区和排序查看每行的数据 4

2.3 查看每个部门最高的数据 4

3 row_number over 窗口函数的使用 5

3.1 显示数据的行号 5

3.1.1 顺序显示行号 5

3.1.2 获取一段内的数据 5

3.2 显示分区的个数 6

3.3 按照department分组wages排序显示数据 6

3.4 查看每个部门的最高的数据 7

4 dense_rank窗口函数使用 7

4.1 rank与dense_rank的区别 7

4.2 dense_rank 窗口函数的显示 7

4.3 rank 窗口函数的显示 8

5 rank/row_number/dense_rank比较 8

6 percent_rank 窗口函数的使用 9

6.1 计算分组中的比例 9

7 grouping sets 函数的使用 10

7.1 先按照wages分组再按照department进行分组 10

8 聚合函数+窗口函数使用 11

8.1 查看一个部门的个数 11

8.2 统计每个部门的wages之和 11

8.3 按照排序统计每个部门的wages之和 12

8.4 按照分组和排序统计数据 12

8.5 window子句使用 13

8.5.1 windom子句的说明 13

8.5.2 执行的SQL语句 13

8.6 窗口函数中的序列函数 14

8.6.1 序列函数的说明 14

8.6.2 执行的语句 14

9 first_value\last_value使用 15

9.1 first_value和last_value说明 15

9.2 执行的SQL 15

窗口函数说明

1、我们都知道在SQL中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的,但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。

2、在所有的SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by字句之前。

3、Partition By子句可以称为查询分区子句,非常类似于Group By,都是将数据按照边界值分组,而Over之前的函数在每一个分组之内进行,如果超出了分组,则函数会重新计算。

4、order by子句会让输入的数据强制排序。Order By子句对于诸如row_number()，lead()，LAG()等函数是必须的，因为如果数据无序，这些函数的结果就没有任何意义。因此如果有了Order By子句，则count()，min()等计算出来的结果就没有任何意义。

5、如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合。

6、当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的。

row_number/rank/dense_rank的区别

这三个窗口函数的使用场景非常多,区别分别为:

1、row_number()从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列

2、rank() 生成数据项在分组中的排名，排名相等会在名次中留下空位

3、dense_rank() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

注意：

rank和dense_rank的区别在于排名相等时会不会留下空位。

窗口函数语句

<窗口函数>

OVER ([PARTITION BY <列清单>]

ORDER BY <排序用列清单>)

over:窗口函数关键字

partition by:对结果集进行分组

order by:设定结果集的分组数据排序

聚合函数:聚合函数（SUM、AVG、COUNT、MAX、MIN）

内置函数:rank、dense_rank、row_number、percent_rank、grouping sets、first_value、last_value、nth_value等专用窗口函

1 准备数据

1.1 创建测试的表test1

create table test1(
department varchar(50),
number numeric,
wages numeric
);

1.2 插入数据到test1表中

insert into test1 values
('发展部','8','6000'),
('发展部','10','5200'),
('销售部','1','5000'),
('销售部','3','4800'),
('发展部','7','4200'),
('销售部','4','4800'),
('发展部','9','4500'),
('私立部','5','3500'),
('私立部','2','3900'),
('发展部','11','5200');

2 rank over 窗口函数使用

rank():返回行号,对比值重复时行号重复并间断， 即返回 1,2,2,4...

2.1 按照分区查看每行的个数

select *,rank() over(partition by department) cn from test1;

2.2 按照分区和排序查看每行的数据

select *,rank() over(partition by department order by wages desc) cn from test1;

2.3 查看每个部门最高的数据

select * from (
select *,rank() over(partition by department order by wages desc) cn from test1)
tn where cn=1;

3 row_number over 窗口函数的使用

row_number():返回行号,对比值重复时行号不重复不间断,即返回 1,2,3,4,5....,不返回 1,2,2,4...

3.1 显示数据的行号

3.1.1 顺序显示行号

select *,row_number() over() cn from test1

3.1.2 获取一段内的数据

select *,row_number() over() cn from test1 limit 4 OFFSET 2

3.2 显示分区的个数

select *,row_number() over(partition by department) cn from test1

3.3 按照department分组wages排序显示数据

select *,row_number() over(partition by department order by wages desc) cn from test1

3.4 查看每个部门的最高的数据

select * from ( select *,row_number() over(partition by department order by wages desc) cn from test1 )
tn where cn =1;

4 dense_rank窗口函数使用

4.1 rank与dense_rank的区别

rank()：返回行号,对比值重复时行号重复并间断， 即返回 1,2,2,4...

dense_rank():返回行号,对比值重复时行号重复但不间断， 即返回 1,2,2,3

注意他两的区别

4.2 dense_rank 窗口函数的显示

select *,dense_rank() over(partition by department order by wages desc) cn from test1;

4.3 rank 窗口函数的显示

select *,rank() over(partition by department order by wages desc) cn from test1;

5 rank/row_number/dense_rank比较

rank():返回行号,对比值重复时行号重复并间断， 即返回 1,2,2,4...
row_number():返回行号,对比值重复时行号不重复不间断,即返回 1,2,3,4,5....,不返回 1,2,2,4...
dense_rank():返回行号,对比值重复时行号重复但不间断， 即返回 1,2,2,3

select department,number,wages,
-- 值同排名相同，同时不保留被占用的排名序号，即总排名号不连续
rank() over(partition by department order by wages desc) as rnl,
-- 值同，排名相同，保留下一个的排名序列号，即总排名连续
dense_rank() over(partition by department order by wages desc) as rn2,
-- 强制按列的结果排序，更像行号。
row_number() over(partition by department order by wages desc) as rn3
from test1;

6 percent_rank 窗口函数的使用

percent_rank()：从当前开始,计算在分组中的比例 (行号-1)*(1/(总记录数-1))

6.1 计算分组中的比例

select *,percent_rank() over(partition by department order by wages desc) cn from test1;

7 grouping sets 函数的使用

7.1 先按照wages分组再按照department进行分组

在以下结果中可以看出wages有相同的显示了null值,如果想做唯一数据去掉该条件即可
select department,wages,count(1) from test1 group by grouping sets(department,(department,wages)) order by department;

8 聚合函数+窗口函数使用

8.1 查看一个部门的个数

select department,number,wages,count(*) over() from test1 where department = '发展部';

8.2 统计每个部门的wages之和

select department,number,wages,sum(wages) over(partition by department) from test1;

8.3 按照排序统计每个部门的wages之和

select department,number,wages,sum(wages) over(partition by department ORDER BY wages desc) from test1;

8.4 按照分组和排序统计数据

select department,number,wages,
sum(wages) over() sum1,
sum(wages) over (order by department) sum2,
sum(wages) over (partition by department) sum3,
sum(wages) over ( partition by department order by wages desc) sum4
from test1
order by department desc;

8.5 window子句使用

8.5.1 windom子句的说明

我们在上面已经通过使用partition by子句将数据进行了分组的处理,如果我们想要更细粒度的划分,我们就要引入window子句了。

window子句:

- preceding（preceding）：往前

- following(following)：往后

- current row(current row)：当前行

- unbounded(unbounded)：起点

- unbounded preceding 表示从前面的起点

- unbounded following：表示到后面的终点

8.5.2 执行的SQL语句

select department,number,wages,
--所有行相加
sum(wages) over() as sum1,
-- 统计按照department组内的sum
sum(wages) over(partition by department) as sum2,
-- 统计按照department分组wages排序sum
sum(wages) over(partition by department order by wages) as sum3,
-- 表示从前面的起点到当前的行统计sum
sum(wages) over(partition by department order by wages rows between unbounded preceding and current row) as sum4,
-- 表示往前1行到当前的行的统计
sum(wages) over(partition by department order by wages rows between 1 preceding and current row) as sum5,
-- 表示往前1行到往后1行的统计
sum(wages) over(partition by department order by wages rows between 1 preceding and 1 following )as sum6,
-- 表示当前的行到后面的重点统计
sum(wages) over(partition by department order by wages rows between current row and unbounded following) as sum7
from test1;

8.6 窗口函数中的序列函数

8.6.1 序列函数的说明

常用的序列函数有下面几个:

ntile(ntile)

ntile(n),用于将分组数据按照顺序切分成n片，返回当前切片值

ntile不支持rows between，

比如 ntile(2) over(partition by cost order by name rows between 3 preceding and current row)

8.6.2 执行的语句

select department,number,wages,
-- 全局数据进行分割
ntile(3) over() as sample1,
-- 按照分组,将数据今个
ntile(3) over(partition by department) sample2,
-- 按照排序对数据进行分割
ntile(3) over(order by department) sample3,
-- 按照分组和排序进行数据分割
ntile(3) over(partition by department order by wages) sample4
from test1 order by department desc;

9 first_value\last_value使用

9.1 first_value和last_value说明

first_value取分组内排序后,截止到当前行,第一个值

last_value取分组内排序后,截止到当前行,最后一个值,如果有重复值获取获取最后一个

以下函数在greenplum才可使用

nth_value用来取结果集每一个分组的指定行数的字段值。(如果不存在返回null)

9.2 执行的SQL

select department,number,wages,
first_value(number) over(partition by department order by wages desc)as f1,
last_value(number) over(partition by department order by wages desc) as f2
from test1;