T-SQL 計算不同時段測量值差異：視圖法 vs 暫存表法

需求：對時間序列資料，計算相鄰兩筆記錄的數值差異（如溫度變化）。

基礎資料準備

IF OBJECT_ID('MEASUREMENTS') IS NOT NULL DROP TABLE MEASUREMENTS;
GO
CREATE TABLE MEASUREMENTS (
    WHEN_TAKEN DATETIME NOT NULL,
    TEMPERATURE NUMERIC(4,1)
);
CREATE CLUSTERED INDEX MEASUREMENTS_IDX01 ON MEASUREMENTS(WHEN_TAKEN);
GO

-- 產生 20 筆隨機測試資料
DECLARE @COUNTER INT, @WHENDATE DATETIME, @VAL NUMERIC(4,1),
        @RANDDIFF SMALLINT, @RANDMINS SMALLINT;
SELECT @COUNTER=1, @WHENDATE=GETDATE(), @VAL=50.0;
WHILE (@COUNTER <= 20)
BEGIN
    INSERT MEASUREMENTS VALUES (@WHENDATE, @VAL);
    SELECT @RANDDIFF = CASE WHEN CONVERT(INT,RAND()*100)%2=1 THEN CONVERT(INT,RAND()*100)%21*-1 ELSE CONVERT(INT,RAND()*100)%21 END,
           @RANDMINS = CONVERT(INT,RAND()*100000)%10080;
    SELECT @COUNTER=@COUNTER+1,
           @WHENDATE=DATEADD(MI, @RANDMINS, GETDATE()),
           @VAL=@VAL+@RANDDIFF+RAND();
END
SELECT * FROM MEASUREMENTS;

方法一：標準 SQL + 視圖（邏輯讀 84 次）

CREATE VIEW RANKDATES (WHEN_TAKEN, TEMPERATURE, DATERANK)
AS
SELECT WHEN_TAKEN, TEMPERATURE,
       (SELECT COUNT(DISTINCT WHEN_TAKEN) FROM MEASUREMENTS T1 WHERE T1.WHEN_TAKEN <= T0.WHEN_TAKEN) AS RANK
FROM MEASUREMENTS T0;
GO

SELECT * FROM RANKDATES ORDER BY DATERANK;

SELECT P1_WHEN=V1.WHEN_TAKEN, P2_WHEN=V2.WHEN_TAKEN,
       P1=V1.TEMPERATURE, P2=V2.TEMPERATURE,
       DIFF=(V2.TEMPERATURE-V1.TEMPERATURE)
FROM RANKDATES V1 LEFT JOIN RANKDATES V2 ON V2.DATERANK = V1.DATERANK+1;

方法二：具體化排名至暫存表（建表 40 邏輯讀 + 查詢 52 邏輯讀）

CREATE TABLE #RANKDATES (
    WHEN_TAKEN DATETIME,
    TEMPERATURE NUMERIC(4,1),
    DATERANK INT IDENTITY PRIMARY KEY
);
GO
INSERT #RANKDATES (WHEN_TAKEN, TEMPERATURE)
SELECT WHEN_TAKEN, TEMPERATURE FROM MEASUREMENTS ORDER BY WHEN_TAKEN ASC;
GO

SELECT P1_WHEN=V1.WHEN_TAKEN, P2_WHEN=V2.WHEN_TAKEN,
       P1=V1.TEMPERATURE, P2=V2.TEMPERATURE,
       DIFF=(V2.TEMPERATURE-V1.TEMPERATURE)
FROM #RANKDATES V1 LEFT JOIN #RANKDATES V2 ON V2.DATERANK = V1.DATERANK+1;

DROP TABLE #RANKDATES;

結果範例

P1_WHEN                     P2_WHEN                     P1     P2     DIFF
--------------------------- --------------------------- ------ ------ ------
2002-01-30 20:45:42.610    2002-01-31 09:48:42.773    50.0   63.7   13.7
2002-01-31 09:48:42.773    2002-01-31 17:28:42.820    63.7   18.0  -45.7
2002-01-31 17:28:42.820    2002-01-31 17:41:42.780    18.0   50.1   32.1

結論

資料量小時兩法差異不明顯；大數據量下，暫存表法大幅降低邏輯讀，建議優先採用。現代版本（SQL Server 2012+）可直接使用 LAG()/LEAD() 視窗函數更簡潔高效。