您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 直方圖查詢實戰：從輔助數字表到即時計算的兩種高效能寫法

編輯：關於SqlServer

前言

直方圖將數值分布劃分為若干等寬/等深區間 (Bin)，統計各區間頻次，是效能分析、資料探勘的基礎工具。本文以 Samples(measid, dt, value) 效能計數器採樣表為例，給定 @numsteps (區間數)、@measid、日期範圍，示範兩種純 T-SQL 產生直方圖的高效寫法。

情境與參數

DECLARE @numsteps INT = 5, @measid INT = 1,
        @fromdt DATETIME = '20030101', @todt DATETIME = '20030102';
-- 範例資料：measid=1 在此區間 MIN=26, MAX=50
-- 期望 5 區間：(25,30] (30,35] (35,40] (40,45] (45,50]

解法一：輔助數字表 + 派生區間表 (模組化、可重用)

步驟 1：建立輔助數字表 Nums

CREATE TABLE dbo.Nums(n INT PRIMARY KEY);
INSERT INTO dbo.Nums(n) SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT 1))
FROM sys.columns c1 CROSS JOIN sys.columns c2;

步驟 2：建立 Table-Valued Function 產生區間表

CREATE FUNCTION dbo.fn_Steps
(
    @measid INT, @numsteps INT, @fromdt DATETIME, @todt DATETIME
)
RETURNS TABLE
AS RETURN
(
    WITH Bounds AS (
        SELECT
            MIN(value) AS mn, MAX(value) AS mx
        FROM dbo.Samples
        WHERE measid = @measid AND dt >= @fromdt AND dt < @todt
    ),
    Steps AS (
        SELECT
            n AS step,
            mn + CAST(ROUND(1.0 * (mx - mn + 1) / @numsteps * (n - 1), 0) AS INT) - 1 AS lower_bound,
            mn + CAST(ROUND(1.0 * (mx - mn + 1) / @numsteps * n, 0) AS INT) - 1 AS upper_bound
        FROM dbo.Nums, Bounds
        WHERE n BETWEEN 1 AND @numsteps
    )
    SELECT step, lower_bound, upper_bound FROM Steps
);

關鍵點：

使用 1.0 * 強制浮點除法，避免整數截斷。
ROUND(..., 0) 四捨五入處理無法整除的區間寬度。
下界 不包含、上界包含 (lower_bound, upper_bound]，符合標準直方圖定義。

步驟 3：主查詢 JOIN 區間表聚合

SELECT s.step, s.lower_bound, s.upper_bound, COUNT(smp.value) AS freq
FROM dbo.fn_Steps(1, 5, '20030101', '20030102') AS s
LEFT JOIN dbo.Samples AS smp
    ON smp.measid = 1
   AND smp.dt >= '20030101' AND smp.dt < '20030102'
   AND smp.value > s.lower_bound AND smp.value <= s.upper_bound
GROUP BY s.step, s.lower_bound, s.upper_bound
ORDER BY s.step;

優點：邏輯清晰、區間表可獨立測試、適用動態區間數、易封裝為函數/預存程序。

解法二：即時計算區間號 (無輔助表、單一查詢)

WITH Params AS (
    SELECT
        MIN(value) AS mn,
        MAX(value) AS mx,
        1.0 * (MAX(value) - MIN(value) + 1) / @numsteps AS step_size
    FROM dbo.Samples
    WHERE measid = @measid AND dt >= @fromdt AND dt < @todt
),
Binned AS (
    SELECT
        value,
        FLOOR((value - mn) / step_size) + 1 AS step
    FROM dbo.Samples, Params
    WHERE measid = @measid AND dt >= @fromdt AND dt < @todt
)
SELECT step, COUNT(*) AS freq
FROM Binned
GROUP BY step
ORDER BY step;

優點：無需輔助表、單一陳述式、適合臨時分析。缺點：區間邊界不顯式輸出，需額外計算才能顯示區間範圍。

效能與適用場景對比

維度	解法一 (輔助表+函數)	解法二 (即時計算)
物件依賴	需 Nums 表、TVF	零物件依賴
區間邊界輸出	原生支援	需額外計算
動態區間數	參數化靈活	參數化靈活
執行計畫	兩階段：Bounds→Steps→Join	單一流程：Compute Scalar→Hash Match
適用場景	報表系統、重複使用、需邊界顯示	臨時分析、探勘、腳本遷移

進階擴展：等深直方圖 (NTILE)

若需每區間筆數近似相等 (百分位數)，可用 NTILE(@numsteps) OVER (ORDER BY value)：

SELECT NTILE(5) OVER (ORDER BY value) AS step, COUNT(*) AS freq
FROM dbo.Samples
WHERE measid = 1 AND dt >= '20030101' AND dt < '20030102'
GROUP BY NTILE(5) OVER (ORDER BY value)
ORDER BY step;

注意：NTILE 需 SQL Server 2005+，且無法直接得到數值邊界，需配合 MIN/MAX OVER (PARTITION BY step) 取得。