SQL 報表設計技巧：預計算日期/時欄位實現高效小時統計

問題

即時對 DATETIME 欄位做 DATEPART(hour, ...) 分組統計，大資料量下掃描慢、無法有效使用索引。

解決方案：表結構預計算

CREATE TABLE EventLog (
    EventID INT IDENTITY PRIMARY KEY,
    StartTime DATETIME NOT NULL DEFAULT GETDATE(),  -- 完整時間戳
    StartDate DATETIME NOT NULL DEFAULT CONVERT(DATETIME, CONVERT(CHAR(10), GETDATE(), 110)), -- 僅日期
    StartHour INT NOT NULL DEFAULT DATEPART(hour, GETDATE()), -- 僅小時 0-23
    EventType INT NOT NULL CHECK (EventType IN (1,2,3)) DEFAULT 1  -- 1=Insert,2=Update,3=Delete
)

插入資料時自動填入

INSERT INTO EventLog (StartTime, EventType) VALUES (GETDATE(), 3)  -- StartDate/StartHour 由 DEFAULT 自動產生

高效統計查詢

SELECT
    StartDate AS ReportDate,
    StartHour AS ReportHour,
    CASE EventType WHEN 1 THEN 'Insert' WHEN 2 THEN 'Update' WHEN 3 THEN 'Delete' END AS EventType,
    COUNT(*) AS TotalCount
FROM EventLog
GROUP BY StartDate, StartHour, EventType
ORDER BY StartDate, StartHour
COMPUTE SUM(COUNT(*)) BY StartDate, StartHour  -- SQL 2000 語法，新版用 GROUPING SETS

優勢

• StartDate, StartHour 可建立索引，WHERE StartDate = '2024-01-01' 直接 Index Seek。
• 避免查詢時計算函數，CPU 開銷極低。
• 適用於即時儀表板、每小時排程報表。

現代替代 (SQL 2008+)

使用計算欄位 (PERSISTED) 或索引檢視表，維護更自動化。