如何使用儲存會話數據的 SQL Server 表計算並發應用程序會話數？

我有一個供應商應用程序，它將會話數據儲存在 SQL Server 2008 數據庫表中。它包含的列包括 sessionId、使用者的 IP 地址、創建會話時的日期時間戳（即使用者登錄）和銷毀會話時的日期時間戳（即使用者註銷或系統註銷使用者） .

我的目標是分析此表中的所有記錄，併計算出所有記錄中的平均並發會話數。

現在，不幸的是，由於此問題範圍之外的原因，會話銷毀日期不准確。因此，我對會話的持續時間使用了一個非常粗略的估計：1 小時。在我整理好設計階段之後，我總是可以更改數字。

我確信我可以組合一個儲存過程來獲得平均並發會話數，但我希望我可以通過查詢來完成它。

為了簡化這裡的事情，我們假設表中有 5 條記錄，並且都是在同一天創建的，並且都是 GMT 時間：

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

13:00，第一個會話被銷毀。並發會話數保持在 4，因為會話 2 到 5 仍然存在。

問題是我如何編寫一個查詢來輸出平均並發會話數？可以做到嗎？我想它會涉及同一張表上的多個連接，但我還沒有完全弄清楚從哪裡開始。

該表有不到一百萬條記錄。我可以訪問 2012 年的盒子，如果有幫助，我可以在那裡複製表格。

我發現 SQL 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，並為被破壞的行創建第二個副本，並為這些行分配 -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 ║
╚═════════════════════════╩═════════════════╝

現在我們需要取該值的平均值。我假設您需要按時間加權的平均值，因此缺少的是每次測量應計入的分鐘數。SQL 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。

顯然，這也可以在 SQL Server 2008 中完成。

引用自：https://dba.stackexchange.com/questions/203911