如何使用存储会话数据的Server表计算并发应用程序会话的数量？
我有一个供应商应用程序，它将会话数据存储在Server 2008数据库表中。它包含的列包括sessionId、用户的IP地址、创建会话时的日期时间戳(即用户登录)和会话被销毁时的日期时间戳(即用户注销或系统注销用户)。
我的目标是分析这个表中的所有记录，并计算出所有记录中并发会话的平均数量。
现在，不幸的是，会话销毁日期是不准确的，原因超出了这个问题的范围。因此，我对会议的持续时间使用了一个非常粗略的估计:1小时。在我整理好设计阶段之后，我可以随时更改这个数字。
我确信我可以组合一个存储过程来获得并发会话的平均数量，但我希望能够通过查询来完成它。
为了简化这里的事情，让我们假设表中有5条记录，所有记录都是在同一天创建的，并且都是在格林尼治时间：
sessionId sessionStart sessionEnd Accumulative # of Concurrent Sessions
1 12:00 13:00 1
2 12:15 13:15 2
3 12:30 13:30 3
4 12:45 13:45 4
5 13:00 14:00 4下午1:00，第一次会议被毁。并发会话的数量保持在4，因为会话2到5仍然存在。
问题是如何编写一个将输出并发会话平均数量的查询？能办到吗？我想这将涉及到同一表上的多个连接，但我还没有完全确定从哪里开始。
这张桌子有不到一百万的记录。我可以访问一个2012框，如果它有帮助，我可以复制那里的表。
回答 1
Database Administration用户
发布于 2018-04-14 01:43:30
我发现Server 2012更适合解决这类问题，因为它在ORDER BY窗口聚合的OVER子句中支持SUM。将数据放入临时表：
CREATE TABLE #my_sessions (sessionId INT, sessionStart DATETIME);
INSERT INTO #my_sessions VALUES
(1, '20180413 12:00:00'),
(2, '20180413 12:15:00'),
(3, '20180413 12:30:00'),
(4, '20180413 12:45:00'),
(5, '20180413 13:00:00');我将把查询分成三个部分，这样就更容易理解了。第一个技巧使用一个运行的总计来在每次发生更改时获取并发会话的数量。想象一下，获取数据，为创建会话的行分配1，为被销毁的行再复制一次，为这些行分配a-1。如果按时间顺序计算正在运行的总会话，则每次值更改时以活动会话的数量结束。
SELECT DISTINCT
event_time
, SUM(event_change) OVER (ORDER BY event_time RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) active_sessions
FROM #my_sessions
CROSS APPLY (
VALUES
(sessionStart, 1),
(DATEADD(HOUR, 1, sessionStart), -1)
) ca (event_time, event_change);以下是研究结果：
╔═════════════════════════╦═════════════════╗
║ event_time ║ active_sessions ║
╠═════════════════════════╬═════════════════╣
║ 2018-04-13 12:00:00.000 ║ 1 ║
║ 2018-04-13 12:15:00.000 ║ 2 ║
║ 2018-04-13 12:30:00.000 ║ 3 ║
║ 2018-04-13 12:45:00.000 ║ 4 ║
║ 2018-04-13 13:00:00.000 ║ 4 ║
║ 2018-04-13 13:15:00.000 ║ 3 ║
║ 2018-04-13 13:30:00.000 ║ 2 ║
║ 2018-04-13 13:45:00.000 ║ 1 ║
║ 2018-04-13 14:00:00.000 ║ 0 ║
╚═════════════════════════╩═════════════════╝现在我们需要取这个值的平均值。我假设你想要一个时间加权平均值，所以缺少的是每一个度量应该计算的分钟数。Server 2012引入了LEAD函数，这使得这非常容易。现在查询如下：
SELECT
active_sessions
, DATEDIFF(MINUTE, event_time, LEAD(event_time) OVER (ORDER BY event_time)) minutes_until_change
FROM
(
SELECT DISTINCT
event_time
, SUM(event_change) OVER (ORDER BY event_time RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) active_sessions
FROM #my_sessions
CROSS APPLY (
VALUES
(sessionStart, 1),
(DATEADD(HOUR, 1, sessionStart), -1)
) ca (event_time, event_change)
) active_sessions中间结果集：
╔═════════════════╦══════════════════════╗
║ active_sessions ║ minutes_until_change ║
╠═════════════════╬══════════════════════╣
║ 1 ║ 15 ║
║ 2 ║ 15 ║
║ 3 ║ 15 ║
║ 4 ║ 15 ║
║ 4 ║ 15 ║
║ 3 ║ 15 ║
║ 2 ║ 15 ║
║ 1 ║ 15 ║
║ 0 ║ NULL ║
╚═════════════════╩══════════════════════╝我们需要计算平均值，这是最容易的部分。把这一切结合在一起：
SELECT 1.0 * SUM(active_sessions * minutes_until_change) / SUM(minutes_until_change)
FROM
(
SELECT
active_sessions
, DATEDIFF(MINUTE, event_time, LEAD(event_time) OVER (ORDER BY event_time)) minutes_until_change
FROM
(
SELECT DISTINCT
event_time
, SUM(event_change) OVER (ORDER BY event_time RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) active_sessions
FROM #my_sessions
CROSS APPLY (
VALUES
(sessionStart, 1),
(DATEADD(HOUR, 1, sessionStart), -1)
) ca (event_time, event_change)
) active_sessions
) average_me
WHERE minutes_until_change IS NOT NULL;最终结果为2.5。
显然，这也可以在Server 2008中完成。。
https://dba.stackexchange.com/questions/203911
复制相似问题
腾讯云开发者
