统计所有数据里，每个project_name中每天反应时间最小的3个接口；

[fcy0306]

使用的是postgresql数据库


表名：loginfo，表结构如下：
id project_name 接口名 开始时间 结束时间
1 user com.user1 2020-09-08 09:41:02.000000 2020-09-08 09:41:02.115037
2 user com.user2 2020-09-08 09:41:03.000000 2020-09-08 09:41:03.886752
3 user com.user3 2020-09-08 09:41:04.000000 2020-09-08 09:41:04.234567
4 msag com.msag1 2020-09-08 09:42:04.000000 2020-09-08 09:42:04.115037
5 msag com.msag2 2020-09-08 09:42:05.000000 2020-09-08 09:42:05.295037



结束时间减去开始时间即为反应时间
要求实现查询格式：以9-8这天的数据为例
project_name 接口名 反应时间
user com.user1 115037
user com.user2 234567
user com.user3 886752
msag com.msag1 115037
msag com.msag2 295037

[文盲老顾]

with loginfo as (
	select 1 as id,'user' as project_name,'com.user1' as 接口名,'2020-09-08 09:41:02.000000' as 开始时间,'2020-09-08 09:41:02.115037' as 结束时间
	union all select 2,'user','com.user2','2020-09-08 09:41:03.000000','2020-09-08 09:41:03.886752'
	union all select 3,'user','com.user3','2020-09-08 09:41:04.000000','2020-09-08 09:41:04.234567'
	union all select 4,'msag','com.msag1','2020-09-08 09:42:04.000000','2020-09-08 09:42:04.115037'
	union all select 5,'msag','com.msag2','2020-09-08 09:42:05.000000','2020-09-08 09:42:05.295037'
)
select * from (
	select *,datediff(ms,开始时间,结束时间) as ms
		,row_number() over(partition by project_name order by datediff(ms,开始时间,结束时间)) as rank_num 
	from loginfo
) a
where rank_num<=3
order by project_name,rank_num

[fcy0306]

引用 1 楼 文盲老顾 的回复:

with loginfo as (
	select 1 as id,'user' as project_name,'com.user1' as 接口名,'2020-09-08 09:41:02.000000' as 开始时间,'2020-09-08 09:41:02.115037' as 结束时间
	union all select 2,'user','com.user2','2020-09-08 09:41:03.000000','2020-09-08 09:41:03.886752'
	union all select 3,'user','com.user3','2020-09-08 09:41:04.000000','2020-09-08 09:41:04.234567'
	union all select 4,'msag','com.msag1','2020-09-08 09:42:04.000000','2020-09-08 09:42:04.115037'
	union all select 5,'msag','com.msag2','2020-09-08 09:42:05.000000','2020-09-08 09:42:05.295037'
)
select * from (
	select *,datediff(ms,开始时间,结束时间) as ms
		,row_number() over(partition by project_name order by datediff(ms,开始时间,结束时间)) as rank_num 
	from loginfo
) a
where rank_num<=3
order by project_name,rank_num

大佬，<=3那里报错了，我用的是postsql数据库