SQL 時間序列抵消查詢：B 表依時間順序扣減 A 表金額

問題重述

表 A（明細流水）：ID, DATE, AMOUNT

表 B（額度）：ID, QUOTA

需依 DATE 遞增，用 B 表 QUOTA 依序扣減 A 表 AMOUNT，輸出扣減後**餘額 > 0** 的明細行。

範例資料

A 表：
ID  DATE        AMOUNT
A1  2015-01-01  200
A1  2015-02-02  200
A1  2015-03-10  300
A1  2015-03-20  100
B1  2015-01-02  100
B1  2015-02-25  300

B 表：
ID  QUOTA
A1  500
B1  300

預期輸出：
ID  DATE        AMOUNT
A1  2015-03-10  200
A1  2015-03-20  100
B1  2015-02-25  100

標準 SQL 解法（相容 MySQL 8.0+, PostgreSQL, SQL Server, Oracle）

WITH cte AS (
    SELECT 
        a.ID,
        a.DATE,
        a.AMOUNT,
        b.QUOTA,
        SUM(a.AMOUNT) OVER (PARTITION BY a.ID ORDER BY a.DATE ROWS UNBOUNDED PRECEDING) AS running_total
    FROM A a
    JOIN B b ON a.ID = b.ID
)
SELECT ID, DATE, 
       CASE 
           WHEN running_total - QUOTA < 0 THEN 0
           ELSE running_total - QUOTA - LAG(running_total - QUOTA, 1, 0) OVER (PARTITION BY ID ORDER BY DATE)
       END AS REMAIN_AMOUNT
FROM cte
WHERE running_total - QUOTA > 0
ORDER BY ID, DATE;

邏輯說明

1. running_total：視窗函數計算各 ID 依日期累積金額。
2. running_total - QUOTA：扣減額度後的**累積餘額**。
3. LAG(...) 取上一筆的累積餘額，兩者相減即為**本筆扣減後實際剩餘金額**。
4. WHERE running_total - QUOTA > 0 過濾掉已被完全抵消的筆。

MySQL 5.7 / 無視窗函數環境的替代寫法

SELECT t2.ID, t2.DATE,
       CASE 
           WHEN (SELECT SUM(t1.AMOUNT) FROM A t1 WHERE t1.DATE <= t2.DATE AND t1.ID = t2.ID) - t3.QUOTA < 0 THEN 0
           ELSE (SELECT SUM(t1.AMOUNT) FROM A t1 WHERE t1.DATE <= t2.DATE AND t1.ID = t2.ID) - t3.QUOTA
                - COALESCE((SELECT SUM(t1.AMOUNT) FROM A t1 WHERE t1.DATE < t2.DATE AND t1.ID = t2.ID) - t3.QUOTA, 0)
       END AS REMAIN
FROM A t2
JOIN B t3 ON t2.ID = t3.ID
WHERE (SELECT SUM(t1.AMOUNT) FROM A t1 WHERE t1.DATE <= t2.DATE AND t1.ID = t2.ID) - t3.QUOTA > 0
ORDER BY t2.ID, t2.DATE;

⚠️ 子查詢重複計算效能較差，大數據量請升級資料庫版本使用視窗函數。

索引建議

CREATE INDEX idx_a_id_date ON A(ID, DATE);
CREATE INDEX idx_b_id ON B(ID);

總結

使用視窗函數 SUM() OVER (PARTITION BY ID ORDER BY DATE) 計算累積金額，配合 LAG 取差值得到各筆扣減後餘額，標準 SQL 簡潔高效。