[Challenge] 從 Log 中計算 Server 停機時間

Beyond Relational TSQL Challenge 37

This is a challenge to identify the downtime of servers from the log data generated by a monitoring application.

There is a monitoring application that keeps checking the status of several servers at 5 minute intervals. When the monitoring application finds that a particular server is down, it adds a record into the log table.

• Sample Data

ServerName DownTime
---------- -------------------
Server1       2010-01-03 10:00
Server1       2010-01-03 12:00
Server1       2010-01-03 12:05
Server1       2010-01-03 12:10
Server1       2010-01-03 14:05
Server1       2010-01-03 17:35
Server1       2010-01-03 17:45
Server1       2010-01-03 17:50
Server1       2010-01-03 17:55
Server2       2010-01-03 09:55
Server2       2010-01-03 10:00

Note that the monitoring application inserts a record only when the server is down. The polling takes place every 5 minutes. In the above example, "Server1" was down at 10:00. There is no entry for 10:05 which indicates that when the monitoring application checked at 10:05, the server was up. So the first down time is for 00:05 minutes at 10:00 am.

• Expected Results

Server  DownTime             DownFor
------- -------------------- -------
Server1     2010-01-03 10:00 00:05
Server1     2010-01-03 12:00 00:15
Server1     2010-01-03 14:05 00:05
Server1     2010-01-03 17:35 00:05
Server1     2010-01-03 17:45 00:15
Server2     2010-01-03 09:55 00:10

• Rules
1. Assume that the current status of the server - after the last reading - is 'up'.
2. It is possible to have the server down for more than 99 hours and in such a case, the actual number of hours should be displayed.
3. No order should be assumed in the input data.
4. Output should be sorted by Server Name and Down Time columns.
5. There will be no duplicates in the input data.

• 個人解法

IF OBJECT_ID('TC37_Data','U') IS NOT NULL 
  DROP TABLE TC37_Data
GO

CREATE TABLE TC37_Data (
  ServerName VARCHAR(20),
  DownTime VARCHAR(20) 
)
GO

INSERT INTO TC37_Data (ServerName, DownTime)
SELECT 'Server1', '2010-01-03 10:00' UNION ALL
SELECT 'Server1', '2010-01-03 12:00' UNION ALL
SELECT 'Server1', '2010-01-03 12:05' UNION ALL
SELECT 'Server1', '2010-01-03 12:10' UNION ALL
SELECT 'Server1', '2010-01-03 14:05' UNION ALL
SELECT 'Server1', '2010-01-03 17:35' UNION ALL
SELECT 'Server1', '2010-01-03 17:45' UNION ALL
SELECT 'Server1', '2010-01-03 17:50' UNION ALL
SELECT 'Server1', '2010-01-03 17:55' UNION ALL
SELECT 'Server2', '2010-01-03 09:55' UNION ALL
SELECT 'Server2', '2010-01-03 10:00' 

;
WITH CTE1 AS
(
  SELECT 
    ServerName , 
    DownTime , 
    CASE ISDATE(DownTime)
      WHEN 1 THEN CAST(DownTime AS smalldatetime)
      ELSE NULL
    END AS Date
  FROM TC37_Data 
)
,CTE2 AS 
(
  SELECT
    T1.ServerName , 
    T1.DownTime , 
    T1.Date
  FROM CTE1 AS T1 
    LEFT JOIN CTE1 AS T2 ON T1.ServerName = T2.ServerName 
                           AND T1.Date = DATEADD(mi,5,T2.Date)
  WHERE T2.ServerName IS NULL
  UNION ALL
  SELECT 
    T2.ServerName , 
    T2.DownTime , 
    T1.Date
  FROM CTE2 AS T2
    JOIN CTE1 AS T1 ON T2.ServerName = T1.ServerName 
                      AND DATEADD(mi,5,T2.Date) = T1.Date
)
SELECT 
  T.ServerName , 
  T.DownTime , 
  RIGHT(REPLICATE('0',2) + CAST(T.mi / 60 AS varchar(2)),2) + 
  ':' + 
  RIGHT(REPLICATE('0',2) + CAST(T.mi % 60 AS varchar(2)),2) AS DownFor
FROM
  (
    SELECT ServerName , DownTime , COUNT(*) * 5 AS mi
    FROM CTE2 
    GROUP BY ServerName , DownTime
  ) AS T
ORDER BY ServerName , DownTime

把連續停機時間歸類為一個群組，只要計算群組內有多少資料，就可以計算多少分鐘，因此利用 Self JOIN 把每一筆資料都往後加五分鐘，假如沒有對應資料，代表此筆資料為連續停機時間的第一筆資料，找出每個群組的第一筆資料後就可以利用 CTE 來跑出連續停機時間。

Solution 中另一種群組方式 ~~

;
WITH CTE AS
(
  SELECT 
    ServerName , 
    DownTime ,
    DATEADD(mi,-5 * ROW_NUMBER() OVER(PARTITION BY ServerName ORDER BY DownTime) , DownTime) AS grp
  FROM tc37_data
)
SELECT 
  ServerName ,
  MIN(DownTime) AS m ,
  CASE 
    WHEN (COUNT(1)*5)/60 < 100 THEN RIGHT(100 + ((COUNT(1)*5) / 60),2) 
    ELSE CAST(COUNT(1) * 5 / 60 AS varchar(20)) 
  END 
  + ':' 
  + RIGHT(100 + (COUNT(1) * 5) % 60,2) AS c
FROM CTE
GROUP BY ServerName , grp
ORDER BY ServerName , MIN(DownTime)

參考資料：
• TSQL Challenge 37 - Calculate the downtime and duration of servers based on the monitoring log
• Solution 中不錯的解法