postgresql 获取一天中最活跃用户的时间

xvw2m8pv 于 2023-05-17 发布在 PostgreSQL

关注(0)|答案(1)|浏览(174)

我在尝试解决下一个挑战：
我们有表sessions，其中包含user_id、用户login_time和logout_time。就像这样，一天内有一百万条记录：
| 身份证|用户ID|登录时间|登出时间|
| --------------|--------------|--------------|--------------|
| 1|五四六|2023-05-10 09：30：33| 2023-05-10 09：52：33|
| 二|二百四十五|2019 -05-10 01：00：56| 2019 -05-10 01：37：56|
| 三|五四六|2023-05-10 19：22：25|零|
（如果会话仍处于活动状态，则注销时间可以为空）
我们需要找出系统在什么时候有最多的登录用户。
我能想到的最好的解决方案是：

SELECT minutes::timestamp, COUNT(user_id) as active_users
FROM generate_series(timestamp '2023-05-10 00:00:00', timestamp '2023-05-10 23:59:59', interval '10 min') t(minutes)
INNER JOIN sessions as s
    on minutes between s.login_time and (CASE
    WHEN logout_time IS NULL THEN '2023-05-10 23:59:59'
    ELSE logout_time
    END)
GROUP BY minutes
ORDER BY active_users DESC
LIMIT 1;

但它有严重的缺陷：
1.大约需要35秒完成
1.它的工作周期为10分钟-我想提高精度到分钟，甚至可能是秒，如果有可能在5-10秒。
1.我不确定我是否在朝着正确的方向前进：）
请帮助我优化查询或扔在我的查询，解决类似的任务的一些例子。

postgresql

来源：https://stackoverflow.com/questions/76238338/get-the-time-of-the-day-with-most-active-users

1条答案

按热度按时间

7kjnsjlb1#

对于您的查询，您可以 * 尝试 * 以下索引：

create index idx_sessions_range
on sessions (login_time, logout_time desc);

另外，我想知道取消登录/注销的旋转是否会更有效--所以我们不需要使用generate_series和join。它还可以为您提供达到最大用户数的确切时间点：

select *
from (
    select x.ts, 
        sum(sum(x.cnt)) over(order by x.ts) cnt_active_users
    from sessions s
    cross join lateral ( 
        values
            (s.login_time,   1), 
            (s.logout_time, -1)
    ) x(ts, cnt)
    where s.login_time < date '2023-05-11'
        and (s.logout_time >= date '2023-05-10' or s.logout_time is null)
    group by x.ts
) x
where ts >= date '2023-05-10' and ts < date '2023-05-11'
order by cnt_active_users desc, ts
limit 1

子查询过滤与搜索周期重叠的会话，取消透视并计算每个时间点的活动用户计数。外部查询用于过滤出搜索窗口之外的数据点。

赞(0）回复(0）举报 2023-05-17

我来回答

postgresql 获取一天中最活跃用户的时间

1条答案

相关问题

热门标签

最新问答