Postgresql 窗口函数示例

数据准备

 

drop table scores;

create table scores

(

id int ,

lesson varchar(12),

score int);

 

insert into scores(id,lesson,score) values(1,'match',90);

insert into scores(id,lesson,score) values(2,'match',85);

insert into scores(id,lesson,score) values(3,'match',85);

insert into scores(id,lesson,score) values(4,'match',95);

insert into scores(id,lesson,score) values(5,'english',66);

insert into scores(id,lesson,score) values(6,'english',88);

insert into scores(id,lesson,score) values(7,'physics',78);

insert into scores(id,lesson,score) values(8,'physics',70);

insert into scores(id,lesson,score) values(9,'physics',80);

insert into scores(id,lesson,score) values(10,'physics',95);

insert into scores(id,lesson,score) values(11,'physics',95);

 

操作

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

 

select row_number() over(),* from scores limit 2;

 select row_number() over(),* from scores limit 2offset 2;

 

--按lesson分组，score排序，注意红色记录行号不间断

select row_number()over(partitionby lessonorder by score),* from scores;

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

select rank()over(partitionby lessonorder by score),* from scores;

 

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

select dense_rank()over(partitionby lessonorder by score),* from scores;

 

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

 

select percent_rank()over(partition by lessonorderby score),*from scores;