sql - 计算最大并发用户 session 数

Question

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

示例

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 

这里， session 1 和 2 不是并发的，因为它们只重叠了 10 分钟，而 session 1 和 3 是并发的，因为它们重叠了超过 30 分钟，所以结果是 2 个并发 session 。

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

表定义

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)
);

类似问题

这里有一个类似的问题:Count max number of concurrent user session ，但是并发意味着在同一时间点，就我而言，我需要检查它们是否重叠至少 30 分钟

Answer 1

从每个时间范围的结束(或开始)开始扣除 30 分钟。然后基本上按照我的referenced "simple" answer中概述的进行操作(各处均向正确方向调整 30 分钟)。短于 30 分钟的范围会被先验地消除 - 这是有道理的，因为这些范围永远不会成为 30 分钟连续重叠时段的一部分。也使查询速度更快。

计算 2019 年 10 月的所有日期(示例范围):

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<> fiddle here

请注意，LOCALTIMESTAMP 取决于当前 session 的时区。考虑在表中使用 timestamptz 并改为使用 CURRENT_TIMESTAMP。请参阅:

• Ignoring time zones altogether in Rails and PostgreSQL