这是以下线程的第二个也是最后一个阶段:
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。
谢谢,
安德烈