PG 窗口函数实例

目录

• 已知数据
• 期望查询的结果对应的记录
• 解决过程

已知数据

drop table if exists test cascade;
create table test(id int, name varchar,create_time timestamp);
insert into test values(1,'a','2022-4-29 11:28:19');
insert into test values(1,'b','2022-4-30 11:28:19');
insert into test values(1,'c','2022-5-31 11:28:19');

insert into test values(2,'A','2022-4-30 11:28:19');
insert into test values(2,'B','2022-5-31 11:28:19');

insert into test values(3,'C','2022-4-30 11:28:19');
select * from test;
postgres=# select * from test;
 id | name |     create_time
----+------+---------------------
  1 | a    | 2022-04-29 11:28:19
  1 | b    | 2022-04-30 11:28:19
  1 | c    | 2022-05-31 11:28:19
  2 | A    | 2022-04-30 11:28:19
  2 | B    | 2022-05-31 11:28:19
  3 | C    | 2022-04-30 11:28:19
(6 rows)

期望查询的结果对应的记录

1 C  '2022-5-31 11:28:19' 3
2 B  '2022-5-31 11:28:19' 2
3 C  '2022-4-30 11:28:19' 1

解决过程

with temp as (
select id, name, create_time, count(*) over(partition by id  ) from test 
) select *from temp

 id | name |     create_time     | count
----+------+---------------------+-------
  1 | a    | 2022-04-29 11:28:19 |     3
  1 | b    | 2022-04-30 11:28:19 |     3
  1 | c    | 2022-05-31 11:28:19 |     3
  2 | A    | 2022-04-30 11:28:19 |     2
  2 | B    | 2022-05-31 11:28:19 |     2
  3 | C    | 2022-04-30 11:28:19 |     1
(6 rows)

with temp as (
select id, name, create_time, count(*) over(partition by id  order by create_time) from test 
) select *from temp;
 id | name |     create_time     | count
----+------+---------------------+-------
  1 | a    | 2022-04-29 11:28:19 |     1
  1 | b    | 2022-04-30 11:28:19 |     2
  1 | c    | 2022-05-31 11:28:19 |     3
  2 | A    | 2022-04-30 11:28:19 |     1
  2 | B    | 2022-05-31 11:28:19 |     2
  3 | C    | 2022-04-30 11:28:19 |     1

with temp2 as (
	with temp as (
	select id, name, create_time, count(*) over(partition by id  order by create_time) from test 
	) select  id, name, create_time,count, row_number() over(partition by id order by count desc) as row_num from temp  
) sellect id, name, create_time ,count from temp2 where row_num=1;

 id | name |     create_time     | count
----+------+---------------------+-------
  1 | c    | 2022-05-31 11:28:19 |     3
  2 | B    | 2022-05-31 11:28:19 |     2
  3 | C    | 2022-04-30 11:28:19 |     1
(3 rows)