Sql-Server
如何計算工作時間?
我正在處理商業情報報告,我的任務之一是創建用於時間跟踪的報告。
我必須在工作週(週一至週五)內報告工作時間(每天 10 小時)。
我最初的解決方案是使用標量函式。它不適合假期,但它工作。因此,當我嘗試通過以下方式使用我的函式時,它沒有被數據庫引擎優化:
Select [dbo].[fn_GetWorkMinutesInRange](CreatedOn,ResolvedOn,1, 6, 7) From Incidents
這不是優化的數據庫引擎,在我取消它之前它需要 5 多分鐘才能執行。Incidents 表中有大約 20,000 個事件,因此性能不應該受到 imo 的影響。
我已經放棄了標量函式解決方案,並創建了一個創建日曆的表函式。這也無濟於事,因為我看不到如何將這個功能與我的 Incidents 表結合起來。
如果您的報告基於維度建模的數據倉庫(通常是商業智能解決方案的情況),則很可能存在日期維度和時間維度。
如果沒有這樣的對象,創建它們可能非常有用,因為它們變得高度可重用,因為需要其他報告。
在日期維度中,應該有一個 IsHolidayFlag 列。在時間維度中,應該有一個 IsBusinessMinuteFlag 列(根據這個維度的粒度,可能是 IsBusinessSecondFlag)。
如果索引正確,則查詢將相對直接且快速。CROSS JOIN 存在成本(可以創建為可重用的視圖),但對於 200,000 行,它應該可以忽略不計。
SELECT i.IncidentID, 'WorkMinutesInRange' = SUM(CASE WHEN d.IsHolidayFlag = 'N' AND t.IsBusinessMinuteFlag = 'Y' THEN 1 ELSE 0 END) FROM dbo.Incidents i INNER JOIN Dim_Date d ON d.CalendarDate BETWEEN i.CreatedOn AND i.ResolvedOn CROSS JOIN Dim_Time t GROUP BY i.IncidentID
這在偽尺度上看起來很簡單:
Determine number of business days in the date range: D Determine number of Holidays in range H return (D-H)*10*60