postgrefind在SQL中的峰值登录时间

Question

给定用户日志

userID logintime           logouttime
123    2020/09/03  10:20   2020/09/03  12:30
124    2020/09/03  1:20    2020/09/03  2:30
125    2020/09/03  2:20    2020/09/03  3:30
126    2020/09/03  3:00    2020/09/03  4:30
127    2020/09/03  2:00    2020/09/03  4:30
128    2020/09/03  1:00    2020/09/03  4:30

目标：找到任何时候登录(活动(用户的峰值？这意味着我需要返回具有最多活动会话的时间戳以及该时间戳中的活动会话数。对于上表，结果应该是：

timestamp        max
2020/09/03  2:20  4

因为在这个时间戳中有4个活动的sesion，这是最高峰。

Sql Server Sql Server表峰值时间也有类似的问题，但我希望在PostgresSQL 中解决这个问题

这就是我尝试的：

Select logintime as time, "login"
from log l1
unoin
Select logouttime as time, "logout"
join log l2
order by time

这应该给我一个有序的时间列，现在应该可以进行窗口计数(登录+1和注销-1(，然后计数列中的最大数量是最活跃的会话。

我在SQL语句中实现此逻辑时遇到问题

Answer 1

就是这样。timedataCTE是真实用户日志表的模拟。

with timedata (userid, logintime, logouttime) as 
(
values
(123, '2020-09-03 10:20'::timestamp, '2020-09-03 12:30'::timestamp),
(124, '2020-09-03 1:20' , '2020-09-03 2:30' ),
(125, '2020-09-03 2:20' , '2020-09-03 3:30' ),
(126, '2020-09-03 3:00' , '2020-09-03 4:30' ),
(127, '2020-09-03 2:00' , '2020-09-03 4:30' ),
(128, '2020-09-03 1:00' , '2020-09-03 4:30' )
),
logged_in_count as 
(
select
t as point_in_time, 
(select count(*) from timedata where t between logintime and logouttime) as cnt
from (select distinct logintime t from timedata) as points_in_time
)
select * from logged_in_count where cnt = (select max(cnt) from logged_in_count);

point_in_time	cnt
2020-09-03 02:20:00.000	4
2020-09-03 03:00:00.000	4