计算并发用户会话的最大数量 [英] Calculate maximum number of concurrent user sessions

问题描述

我在PostgreSQL 9.6中有一个 UserSession 表，用于存储用户的登录和注销时间，我想计算并发会话的最大数量-仅被认为是并发的如果它们重叠至少30分钟。

I have a UserSession table in PostgreSQL 9.6 that stores user's login and logout time, I want to calculate the maximum number of concurrent sessions - which are only considered to be concurrent if they overlap for at least 30 minutes.

示例

userid      |  starttime                |  endtime  
------------+---------------------------+--------------------------
1           |  01-Oct-19 6:00:00 AM     |    01-Oct-19 11:10:00 AM  
2           |  01-Oct-19 11:00:00 AM    |    01-Oct-19 4:00:00 PM 
3           |  01-Oct-19 10:30:00 AM    |    01-Oct-19 4:00:00 PM 

此处，会话1和2不并发，因为它们仅重叠10分钟，并且会话1和3是并发的，因为它们重叠了30分钟以上，因此结果是2个并发会话。

Here, session 1 and 2 are not concurrent since the they only overlap for 10 mins and session 1 and 3 are concurrent since they overlap for more than 30 mins, So the result is 2 concurrent sessions.

注意：：仅当所有n个会话重叠至少30分钟时，结果才为n。

NOTE: Result will only be n if all n sessions overlap for at least 30 mins.

表定义

CREATE TABLE UserSessions (
    SessionID bigserial NOT NULL,
    UserID bigint NOT NULL,
    StartTime timestamp NOT NULL,
    EndTime timestamp NULL,
    OrganisationID bigint NOT NULL,
    CONSTRAINT PK_SessionsID PRIMARY KEY(SessionID),
    CONSTRAINT FK_UserID FOREIGN KEY(UserID) REFERENCES GlobalUsers(UserID),
    CONSTRAINT FK_OrganisationID FOREIGN KEY(OrganisationID) REFERENCES Organisations(OrganisationID)
);

类似问题

这里有一个类似的问题：最大计数并发用户会话的数量，但在同一时间点存在并发方式，对于我而言，我需要检查它们是否至少重叠30分钟

There is a similar question here: Count max number of concurrent user session, but there concurrent means at the same point in time and in my case I need to check if they overlap for at least 30 mins

推荐答案

从每个时间范围的结束（或开始）减去30分钟。然后基本上按照我的引用的简单答案中的概述进行操作（在所有正确的方向上调整30分钟）。小于30分钟的范围会被事先消除-这是有道理的，因为它们永远不会成为30分钟连续重叠的一部分。

Deduct 30 minutes from the end (or start) of each time range. Then basically proceed as outlined in my referenced "simple" answer (adjusting for the 30 min in the right direction everywhere). Ranges shorter than 30 minutes are eliminated a priori - which makes sense as those can never be part of a 30 minutes period of continuous overlap. Also makes the query faster.

计算2019年10月的所有天数（示例范围）：

Calculating for all days in Oct 2019 (example range):

WITH range AS (SELECT timestamp '2019-10-01' AS start_ts  -- incl. lower bound
                    , timestamp '2019-11-01' AS end_ts)   -- excl. upper bound
, cte AS (
   SELECT userid, starttime
       -- default to current timestamp if NULL
        , COALESCE(endtime, localtimestamp) - interval '30 min' AS endtime
   FROM   usersessions, range r
   WHERE  starttime <  r.end_ts  -- count overlaps *starting* in outer time range
   AND   (endtime   >= r.start_ts + interval '30 min' OR endtime IS NULL)

   )
, ct AS (
   SELECT ts, sum(ct) OVER (ORDER BY ts, ct) AS session_ct
   FROM  (
      SELECT endtime AS ts, -1 AS ct FROM cte
      UNION ALL
      SELECT starttime    , +1       FROM cte
      ) sub
   )
SELECT ts::date, max(session_ct) AS max_concurrent_sessions
FROM   ct, range r
WHERE  ts >= r.start_ts
AND    ts <  r.end_ts            -- crop outer time range
GROUP  BY ts::date
ORDER  BY 1;

db<>小提琴此处

请注意 LOCALTIMESTAMP 取决于当前会话的时区。考虑在表中使用timestamptz，而使用 CURRENT_TIMESTAMP 。请参阅：

Be aware that LOCALTIMESTAMP depends on the time zone of the current session. Consider using timestamptz in your table and CURRENT_TIMESTAMP instead. See:

• 完全忽略Rails和PostgreSQL中的时区

• Ignoring time zones altogether in Rails and PostgreSQL

这篇关于计算并发用户会话的最大数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！