[SQL] 分析函數應用 - 統計群組執行時間

在該 FB 社群內的問題，拿來練習視窗函數，問題為

在限定時間內，要統計每個群組起訖時間差，每一筆的下一筆為該資料的結束時間

原 PO 提供的測試資料

typeA 2018/09/01 12:00
typeB 2018/09/01 12:03
typeC 2018/09/01 12:08
typeB 2018/09/01 12:15
typeC 2018/09/01 12:22
typeA 2018/09/01 12:28

原 PO 提供的資料分析

要TypeA,TypeB,TypeC的累計時間噢~
TypeA:(5分鐘)
12:00~12:03=>3分鐘
12:28~12:30=>2分鐘
TypeB:(12分鐘)
12:03~12:08=>5分鐘
12:15~12:22=>7分鐘
TypeC:(13分鐘)
12:08~12:15=>7分鐘
12:22~12:28=>6分鐘

TSQL

-- 製作資料
DECLARE @Temp Table (TypeName char(5) , DataTime datetime)
INSERT INTO @Temp 
SELECT 'typeA' , '2018/09/01 12:00' UNION ALL
SELECT 'typeB' , '2018/09/01 12:03' UNION ALL
SELECT 'typeC' , '2018/09/01 12:08' UNION ALL
SELECT 'typeB' , '2018/09/01 12:15' UNION ALL
SELECT 'typeC' , '2018/09/01 12:22' UNION ALL
SELECT 'typeA' , '2018/09/01 12:28'

-- 實際執行
SELECT
  T.TypeName , 
  SUM(DATEDIFF(mi , StartTime , EndTime))
FROM
  (
    SELECT 
      TypeName ,
      DataTime AS StartTime ,
      -- 利用視窗函數 LEAD 來取得下一筆資料時間，為該筆資料結束時間
      -- 沒有下一筆資料的話，預設以 2018/09/01 12:30 為結束時間
      LEAD(DataTime, 1, '2018/09/01 12:30') OVER (ORDER BY DataTime) AS EndTime
    FROM @Temp
    WHERE DataTime BETWEEN '2018/09/01 12:00' AND '2018/09/01 12:30'
  ) AS T
GROUP BY T.TypeName

每筆資料起訖時間結果

最終群組統計結果

參考資料
• 如何計算各分類佔用分鐘數