代码之家  ›  专栏  ›  技术社区  ›  Estrobelai

系统中的并发用户

  •  0
  • Estrobelai  · 技术社区  · 7 年前

    这是以下线程的第二个也是最后一个阶段: How to pivot this table in SQL

    总之,这是我目前工作的公司财务团队的一个请求。我们正试图通过一年中任何一天并发Microsoft NAV用户的数量得出这张表。我的第一个线程(您可以在上面看到)是关于转换或旋转这些原始数据的,如下所述:

    原始数据结构示例

    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
    

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

    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
    

    现在的第二步也是最后一步是构建此表,其中包含全年在此特定日期范围内每天登录系统的最大用户数。

    我一直在玩它,并得到了下面的代码。我觉得我就快到了——但每天最大训练次数的最终结果对我来说似乎还不太正确。我得到的代码如下:

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

    此查询的输出应为下表:

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

    然而,我在同一天得到了多个结果。欢迎提出任何关于如何改进此查询的想法/建议!

    顺便说一下,这是Microsoft SQL Azure(RTM)-12.0.2000.8。

    谢谢, 安德烈

    1 回复  |  直到 7 年前
        1
  •  0
  •   John    7 年前

    有一种更简单的方法可以得到这个结果:

    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