系统中的并发用户 [英] Concurrent users in the system

问题描述

这是以下线程的第二个也是最后一个阶段:如何在SQL中旋转该表

This is the second and final stage from the following thread: How to pivot this table in SQL

总而言之，这是我目前工作的这家公司的财务团队的要求.我们正在尝试获取该表以及一年中任何给定日期的Microsoft NAV并发用户数.我的第一个线程(您可以在上面看到)是按照以下说明对原始数据进行转换或旋转:

In summary this is a request from the finance team at this company I currently work for. We are trying to get to this table with the number of concurrent Microsoft NAV users at any given day throughout the year. My first thread (which you can see above) was on transforming or pivoting this raw data as explained below:

原始Raw数据结构示例

Example of original Raw data structure

Session ID |   Event Datetime     | Event Type
     1         2017-07-01 00:00      Logon
     1         2017-07-02 01:00      Logoff
     2         2017-07-03 00:00      Logon
     2         2017-07-04 01:00      Logoff

到目前为止，在此过程中，我们已经能够(在你们的帮助下)将上面的原始数据表转换为下表:

On this journey so far we have been able to transform (with the help of you guys) the raw data table above into the following table:

Session ID |      LogonTime           |      LogoffTime
    1      |     2017-07-01 00:00     |      2017-07-02 01:00 
    2      |     2017-07-03 00:00     |      2017-07-04 01:00

第二步(也是最后一步)是使用一年中该特定日期范围内每天登录系统的最大用户数来构建此表.

The second and last step now is to build this table with the max number of users that logged on the system per day within this specific date range throughout the year.

我一直在研究它，并获得了下面的代码.我觉得自己快到了-但是每天最多会话数的最终结果对我来说似乎还不太正确.我得到的代码如下:

I have been playing around with it and got to the code below. I feel I am nearly there - but the final results of max sessions per day don't seem quite right yet to me. The code I got to is the following:

  WITH range AS (SELECT cast('2017-07-10'as datetime) AS [start_date]  , cast('2017-12-31'as datetime) AS [end_date])   -- inclusive bounds
, cte AS (
   SELECT *
   FROM   [NAV_licenses], range r
   WHERE  [LogonTime]  <= r.[end_date]
   AND    [LogoffTime] >= r.[start_date]
   and [Session ID] =1
   )
, ct AS (
   SELECT log_date, sum(ct) OVER (ORDER BY log_date, ct) AS session_ct
   FROM  (
      SELECT [LogoffTime] AS log_date, -1 AS ct FROM cte
      UNION ALL
      SELECT [LogonTime], 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 [log_date]
ORDER  BY [log_date];

此查询的输出应为下表:

The output of this query should be the following table:

Log_Date        |   Max_Sessions
2017-07-10      |        1
2017-08-10      |        4
2017-09-10      |        6

但是，我当天获得了多个结果.我们欢迎任何关于如何改进此查询的想法/建议！

However I am getting multiple results for the same day. Any ideas / suggestions on how to improve this query are more than welcome!

这是Microsoft SQL Azure(RTM)-12.0.2000.8.

This is Microsoft SQL Azure (RTM) - 12.0.2000.8 by the way.

非常感谢，安德烈(Andre)

Thanks a lot, Andre

推荐答案

有一种更简单的方法来获得该结果:

There is a much easier way to get that result:

WITH cte_logon
    AS (
           SELECT aul.sessionId
                 ,aul.EventDateTime
                 ,seq = RANK() OVER (PARTITION BY aul.sessionId ORDER BY aul.EventDateTime)
             FROM ActiveUsersLog AS aul
            WHERE aul.EventType = 'Logon'
       )
    ,cte_logoff
    AS (
           SELECT aul.sessionId
                 ,aul.EventDateTime
                 ,seq = RANK() OVER (PARTITION BY aul.sessionId ORDER BY aul.EventDateTime)
             FROM ActiveUsersLog AS aul
            WHERE aul.EventType = 'Logoff'
       )
SELECT WorkDate = CONVERT(DATE, o.EventDateTime)
    ,SessionCount = COUNT(*)
  FROM cte_logon AS o
      LEFT OUTER JOIN cte_logoff AS f
          ON o.sessionId = f.sessionId
             AND o.seq = f.seq
    GROUP BY CONVERT(DATE, o.EventDateTime)
    ORDER BY CONVERT(DATE, o.EventDateTime)

这是结果集:

WorkDate    SessionCount
2017-11-01  12
2017-11-02  12
2017-11-03  13
2017-11-04  14
2017-11-05  22
2017-11-06  14
2017-11-07  14
2017-11-08  20
2017-11-09  10
2017-11-10  17
2017-11-11  20
2017-11-12  11
2017-11-13  16
2017-11-14  17
2017-11-15  20
2017-11-16  20
2017-11-17  17
2017-11-18  15
2017-11-19  19
2017-11-20  18
2017-11-21  16
2017-11-22  19
2017-11-23  21
2017-11-24  8
2017-11-25  17
2017-11-26  10
2017-11-27  16
2017-11-28  8
2017-11-29  12
2017-11-30  18
2017-12-01  20
2017-12-02  28
2017-12-03  17
2017-12-04  19
2017-12-05  8
2017-12-06  13
2017-12-07  12
2017-12-08  16
2017-12-09  18
2017-12-10  16
2017-12-11  9
2017-12-12  24
2017-12-13  24
2017-12-14  13
2017-12-15  20
2017-12-16  17
2017-12-17  17
2017-12-18  12
2017-12-19  15
2017-12-20  20
2017-12-21  19
2017-12-22  22
2017-12-23  22
2017-12-24  24
2017-12-25  18
2017-12-26  17
2017-12-27  17
2017-12-28  19
2017-12-29  20
2017-12-30  18

这篇关于系统中的并发用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！