最大计数 每天的并发用户会话数

Question

情况

我们有一个PostgreSQL 8.4
数据库，其中包含每行登录日期/时间和注销日期/时间的用户会话。我们的Web应用程序记录该时间，并处理用户未明确注销（会话超时）的情况。因此，在每种情况下都会给出登录日期/时间和注销日期/时间。

目标

我需要每天最多并发会话的用户统计信息。因此，我可以这样说：“在2015年3月16日，并发用户登录的高峰是 6个 。”

一行代表登录名或注销。另外，问题是基于MS
SQL数据库环境而不是PostgreSQL。

注意事项

• 不同用户的会话可以重叠
• 用户可能有重复的会话，该会话仅应计数一次（按用户名分组）
• 会话表的表方案如下所示

表方案：

user_id     |  login_date  |  login_time  |  logout_date  |  logout_time
------------+--------------+--------------+---------------+-------------
USER32      |  2014-03-03  |    08:23:00  |   2014-03-03  |     14:44:00
USER82      |  2014-03-03  |    08:49:00  |   2014-03-03  |     17:18:00
USER83      |  2014-03-03  |    09:40:00  |   2014-03-03  |     17:31:00
USER36      |  2014-03-03  |    09:50:00  |   2014-03-03  |     16:10:00
USER37      |  2014-03-03  |    11:44:00  |   2014-03-03  |     15:21:00
USER72      |  2014-03-03  |    12:52:00  |   2014-03-03  |     12:55:00

例子

以下示例通过Google ChartsAPI作为时间轴进行了说明，应该有助于理解该问题以2015年3月3日这一天为例，除USER78（6个用户）外，所有用户当天均在12:52和12:55之间登录。这是并发登录用户的最大数量，对于给定的时间范围，我每天需要这样的统计信息。

Day         |   MaxNumberOfConcurrentSessions 
------------+--------------------------------
2015-03-01  |                 2 
2015-03-02  |                 3
2015-03-03  |                 6
...

上面的时间轴屏幕截图示例为Google Charts API。

google.setOnLoadCallback(drawChart);

function drawChart() {



  var container = document.getElementById('example5.1');

  var chart = new google.visualization.Timeline(container);

  var dataTable = new google.visualization.DataTable();

  dataTable.addColumn({ type: 'string', id: 'Room' });

  dataTable.addColumn({ type: 'string', id: 'Name' });

  dataTable.addColumn({ type: 'date', id: 'Start' });

  dataTable.addColumn({ type: 'date', id: 'End' });

  dataTable.addRows([

["USER78", '', new Date(2014,03,03,20,38), new Date(2014,03,03,21,14)],

["USER83", '', new Date(2014,03,03,09,40), new Date(2014,03,03,17,31)],

["USER72", '', new Date(2014,03,03,08,43), new Date(2014,03,03,08,43)],

["USER72", '', new Date(2014,03,03,09,40), new Date(2014,03,03,09,40)],

["USER72", '', new Date(2014,03,03,10,03), new Date(2014,03,03,10,06)],

["USER72", '', new Date(2014,03,03,12,52), new Date(2014,03,03,12,55)],

["USER72", '', new Date(2014,03,03,21,13), new Date(2014,03,03,21,13)],

["USER72", '', new Date(2014,03,03,21,37), new Date(2014,03,03,21,38)],

["USER72", '', new Date(2014,03,03,23,14), new Date(2014,03,03,23,15)],

["USER72", '', new Date(2014,03,03,23,27), new Date(2014,03,03,23,28)],

["USER36", '', new Date(2014,03,03,08,05), new Date(2014,03,03,09,17)],

["USER36", '', new Date(2014,03,03,09,50), new Date(2014,03,03,16,10)],

["USER36", '', new Date(2014,03,03,16,12), new Date(2014,03,03,20,29)],

["USER32", '', new Date(2014,03,03,08,23), new Date(2014,03,03,14,44)],

["USER82", '', new Date(2014,03,03,08,49), new Date(2014,03,03,17,18)],

["USER37", '', new Date(2014,03,03,08,04), new Date(2014,03,03,08,06)],

["USER37", '', new Date(2014,03,03,11,44), new Date(2014,03,03,15,21)],

["USER37", '', new Date(2014,03,03,15,34), new Date(2014,03,03,15,51)],

["USER37", '', new Date(2014,03,03,16,12), new Date(2014,03,03,16,14)],

["USER37", '', new Date(2014,03,03,16,52), new Date(2014,03,03,16,54)],

["USER37", '', new Date(2014,03,03,17,07), new Date(2014,03,03,17,08)],

["USER37", '', new Date(2014,03,03,20,20), new Date(2014,03,03,20,24)],

["USER37", '', new Date(2014,03,03,21,03), new Date(2014,03,03,21,20)],

["USER37", '', new Date(2014,03,03,22,42), new Date(2014,03,03,23,05)],

["USER37", '', new Date(2014,03,03,23,51), new Date(2014,03,03,23,56)],

["USER01", '', new Date(2014,03,03,16,11), new Date(2014,03,03,16,12)]

]);



  var options = {

    timeline: { colorByRowLabel: true }

  };



  chart.draw(dataTable, options);

}


<script type="text/javascript" src="https://www.google.com/jsapi?autoload={'modules':[{'name':'visualization',

       'version':'1','packages':['timeline']}]}"></script>

<div id="example5.1" style="width:5000px;height: 600px;"></div>

Answer 1

我将使用序列化登录和注销UNION ALL，“输入”计数为1，“输出”计数为-1。然后使用简单的窗口函数计算运行计数并获得每天的最大值。

由于尚未指定，因此假定：

• “并发”是指在 同一时间点 （不只是在同一天）。
• 会话可以跨越任何时间范围（也就是多天）。
• 每个用户 一次 只能在线 一次 。因此，在我的解决方案中，无需对每个用户进行分组。

• 注销胜过登录。如果两者都同时发生，则首先对注销进行计数（在极端情况下导致较低的并发数量）。

  WITH range AS (SELECT ‘2014-03-01’::date AS start_date – time range
  , ‘2014-03-31’::date AS end_date) – inclusive bounds
  , cte AS (
  SELECT * FROM tbl, range r
  WHERE login_date <= r.end_date
  AND logout_date >= r.start_date
  )
  , ct AS (
  SELECT log_date, sum(ct) OVER (ORDER BY log_date, log_time, ct) AS session_ct
  FROM (
  SELECT logout_date AS log_date, logout_time AS log_time, -1 AS ct FROM cte
  UNION ALL
  SELECT login_date, login_time, 1 FROM cte
  ) sub
  )
  SELECT log_date, max(session_ct) AS max_sessions
  FROM ct, range r
  WHERE log_date BETWEEN r.start_date AND r.end_date – crop actual time range
  GROUP BY 1
  ORDER BY 1;
  

您 可以在中 使用OVERLAPS运算符cte：

AND   (login_date, logout_date) OVERLAPS (r.start_date, r.end_date)

细节：

但这可能不是一个好主意，因为（根据文档）：

每个时间段都被视为代表半开间隔start <= time <
end，除非start和end相等，在这种情况下，它代表那个单个时刻。例如，这意味着 只有一个共同点的两个时间段不会重叠 。

大胆强调我的。范围的上限必须是所需时间范围 后 的第二天。

解释

• 从Postgres 8.4开始可以使用CTE。

• 第1 CTErange只是提供时间范围的便利 一次 。

• 第二CTEcte仅选择相关行：

  • 在范围之前或范围内开始
  • 并在范围之内或之后
  • 第三CTEct使用+/- 1的值序列化“入”点和“出”点，并sum()使用用作窗口函数的聚合函数来计算运行计数。这些从Postgres 8.4开始可用。

• 在最后SELECT修剪的前几天和后几天，合计每天的最大值。Voil谩。

****适用于Postgres 9.6的 SQL
Fiddle 。
Postgres 8.4太旧了，不能再使用了，但是应该可以正常工作。我在测试用例中添加了一行-跨越多天。应该使它更有用。

笔记

我通常会使用timestamp而不是date和time。相同大小，更易于处理。或者，timestamptz如果涉及多个时区。

(login_date, logout_date DESC)最低的指标对性能至关重要。