目录

PostgreSQL - 开窗函数汇总

和聚合函数的区别

开窗函数,也叫窗口函数,一般可以用开窗函数来做一些排行之类的统计操作。开窗函数必须搭配over()子句作为查询的条件,否则会报错。over()子句可以用partition by进行分组,以及用order by排序。

聚合函数是将多条记录聚合为一条;而开窗函数是每条记录都会执行,有几条记录执行完还是几条,聚合函数也可以用于开窗函数中。

rank()

对数据进行排名,对于并列的数据会占据之后空余的名次数目,比如第一名有两个数据,即前两条数据排名都为1,第三条数据的名次则为3。

1
2
3
-- 查询一个商品的版本排行(一个商品在数据库中保存了多条记录,用以版本控制)
-- 这里是按照商品的序号分组,然后同一个分组内用版本号逆序排序
select ref_no,"version",rank() over(partition by ref_no order by "version" desc nulls last) from tb_item where updated_on > '2022-01-01';

查询结果如下:

ref_noversionrank
ITM2111-000070181
ITM2111-000070172
ITM2111-000070163
ITM2111-000072101
1
2
-- 不分组,直接按照所有商品的版本号逆序排序(其实就相当于按版本号分组)
select ref_no,"version",rank() over(order by "version" desc nulls last) from tb_item where updated_on > '2022-01-01';

查询结果如下:

ref_noversionrank
ITM2112-000015231
ITM2111-000089231
ITM2111-000070183
ITM2112-000091114
ITM2203-000002114
ITM2201-000014114
ITM2201-000060107

dense_rank()

dense是稠密的意思,因此这个函数效果很rank()差不多,区别在于它不会空出所占的名次,比如第一名有两个数据,即前两条数据排名都为1,第三条数据的名次则为2。

1
2
-- 不分组,直接按照所有商品的版本号逆序排序(其实就相当于按版本号分组)
select ref_no,"version",dense_rank() over(order by "version" desc nulls last) from tb_item where updated_on > '2022-01-01';

查询结果如下:

ref_noversiondense_rank
ITM2112-000015231
ITM2111-000089231
ITM2111-000070182
ITM2112-000091113
ITM2203-000002113
ITM2201-000014113
ITM2201-000060104

row_number()

单纯按照行数排行,不会考虑并列行。

1
2
-- 不分组,直接按照所有商品的版本号逆序排序(其实就相当于按版本号分组)
select ref_no,"version",row_number() over(order by "version" desc nulls last) from tb_item where updated_on > '2022-01-01';

查询结果如下:

ref_noversionrow_number
ITM2112-000015231
ITM2111-000089232
ITM2111-000070183
ITM2112-000091114
ITM2203-000002115
ITM2201-000014116
ITM2201-000060107

参考链接

注意
本文最后更新于 March 7, 2022,文中内容可能已过时,请谨慎使用。