postgresql窗口函数

窗口函数使用语法
function() over([partition by] [order by])

• over:窗口函数关键字
• partition by:对结果集进行分组
• order by:设定结果集的分组数据排序

窗口函数说明

• 窗口函数既可以显示聚集前的数据，又可以显示聚集后的数据
• 窗口函数都是最后一步执行，而且仅位于order by字句之前
• partition by子句可以称为查询分区子句，非常类似于group by都是将数据按照边界值分组，而over之前的函数在每一个分组之内进行，如果超出了分组，则函数会重新计算
• order by子句会让输入的数据强制排序。order by子句对于诸如的row_number(),lead(),lag()等函数是必须，否则结果无意义
• 如果只使用partition by子句，未指定order by此时聚合是分组内的聚合
• 当同一个select查询中存在多个窗口函数时，他们相互之间是没有影响的

postgresql内置窗口函数

函数名称	返回类型	描述
row_number()	bigint	在其分区中的当前行号，从1开始计算
rank()	bigint	有间隔的当前行排名；与它的第一个相同行的row_number相同
dense_rank()	bigint	没有间隔的当前行排名；这个函数计数对等组
percent_rank	double precision	当前行的相对排名：（rank-1）/(总行数-1)
cume_dist()	double precision	当前行的相对排名：（前面的行数或与当前行相同的行数）/（总行数）
ntile(num_buckets integer)	integer	从1到参数的整数范围，尽可能相等的划分分区
lag(value any [, offset integer [, default any ]])	类型同value	计算分区当前行的前offset行，返回value。如果没有这样的行，返回default替代。offset和default都是当前计算的结果。如果忽略了则offset默认是1，default默认是null
lead(value any [, offset integer [, default any ]])	类型同value	计算分区当前行的后offset行，返回value。如果没有这样的行，返回default替代。offset和default都是当前计算的结果。如果忽略了则offset默认是1，default默认是null
first_value(value any)	类型同value	返回窗口第一行的计算value值
last_value(value any)	类型同value	返回窗口最后一行的计算value值
nth_value(value any,nth integer)	类型同value	返回窗口的第nth行的计算value，从1开始计数，没有符合的行则返回null

postgresql聚合函数+over

函数	描述
sum	求和
avg	求平均数
count	统计个数
max	最大值
min	最小值

window子句使用：分组后的再次划分

关键字	描述
preceding	往前
following	往后
current row	当前行
unbounded	起点
unbounded preceding	表示从前面的起点
unbounded following	表示到后面的终点

测试

CREATE TABLE public.aa (
	id int4 NULL,
	province varchar(10) NULL,
	city varchar(10) NULL
);

INSERT INTO aa (id,province,city) VALUES 
(1,'广东','广州')
,(2,'广东','深圳')
,(3,'广东','佛山')
,(4,'湖南','长沙')
,(5,'四川','成都')
,(6,'湖南','怀化');

select *,row_number() over (order by province ) as idx from aa
select *,rank() over (order by province ) as idx from aa；

文档来源：
https://www.cnblogs.com/funnyzpc/p/9311281.html
https://blog.csdn.net/weixin_39870155/article/details/111291051