萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 累計值查詢技巧:自關聯子查詢 vs 視窗函數 SUM() OVER()

場景:銀行帳戶流水逐筆餘額

-- 範例資料
TxnID | TxnDate               | Amount
1     | 2006-11-03 02:33:42   | 10000.00
2     | 2006-11-03 02:34:50   | -500.00
3     | 2006-11-03 02:35:04   |  250.00
4     | 2006-11-03 02:42:19   | -124.25

寫法一:相關子查詢 (SQL 2000/2005 相容)

SELECT t1.TxnID, t1.TxnDate, t1.Amount,
       (SELECT SUM(t2.Amount) FROM Transactions t2 WHERE t2.TxnID <= t1.TxnID) AS Balance
FROM Transactions t1
ORDER BY t1.TxnID;

缺點:O(N²) 掃描,大量資料極慢;優點:版本相容性極佳。

寫法二:視窗函數 SUM() OVER (SQL 2012+ 標準、高效)

SELECT TxnID, TxnDate, Amount,
       SUM(Amount) OVER (ORDER BY TxnID ROWS UNBOUNDED PRECEDING) AS Balance
FROM Transactions
ORDER BY TxnID;
  • ROWS UNBOUNDED PRECEDING:從分區起始到當前行 (含);
  • 單掃描 O(N),記憶體內排序/雜湊,效能碾壓子查詢;
  • 支援 PARTITION BY AccountID 多帳戶並行計算。

結果集

TxnID  TxnDate                 Amount    Balance
1      2006-11-03 02:33:42.340  10000.00  10000.00
2      2006-11-03 02:34:50.467   -500.00   9500.00
3      2006-11-03 02:35:04.857    250.00   9750.00
4      2006-11-03 02:42:19.763   -124.25   9625.75

兩大優勢

  1. 無需儲存冗餘餘額欄位:即時計算、永遠一致、省空間;
  2. 可審計追溯:逐行對帳,異常交易即時定位 (如餘額突降)。

擴展:庫存扣減、累計銷售、運行總計

同樣模式:SUM(Qty) OVER (PARTITION BY ProductID ORDER BY TxnDate ROWS UNBOUNDED PRECEDING) 即得即時庫存/累計銷售。

copyright © 萬盛學電腦網 all rights reserved