DB2 拉鏈表 (SCD Type 2) 預存程序實戰實現

業務場景與資料模型

依據帳戶餘額表 T_DEPBAL 與交易流水表 T_JOURNAL，產生拉鏈表 T_DEPCHG，記錄每筆餘額變動的生效區間 (START_DATE ~ END_DATE) 及區間平均餘額 (AVG_BAL)。區間範圍固定為 2006-01-01 至 2050-12-31。

核心表結構

-- 拉鏈表 (目標表)
CREATE TABLE T_DEPCHG (
  ACCOUNT_NO INT,
  START_DATE DATE,
  END_DATE   DATE,
  BALANCE    DOUBLE,
  AVG_BAL    DOUBLE
);

-- 來源餘額表
CREATE TABLE T_DEPBAL (
  ACCOUNT_NO INT,
  BALANCE    DOUBLE
);

-- 交易流水表
CREATE TABLE T_JOURNAL (
  TRANS_DATE DATE,
  ACCOUNT_NO INT,
  AMT        DOUBLE,
  FLAG       DOUBLE  -- 0:支出, 1:收入
);

預存程序核心邏輯 (簡化版)

CREATE PROCEDURE BAL()
RESULT SETS 1
MODIFIES SQL DATA
BEGIN
  DECLARE v_account INT;
  DECLARE v_balance DOUBLE DEFAULT 0;
  DECLARE v_start_date DATE DEFAULT '2006-01-01';
  DECLARE v_end_date DATE;
  DECLARE v_trans_date DATE;
  DECLARE v_amt DOUBLE DEFAULT 0;
  DECLARE v_var_balance DOUBLE DEFAULT 0;
  DECLARE v_all DOUBLE DEFAULT 0;
  DECLARE v_days INT DEFAULT 0;
  DECLARE v_bt_days INT DEFAULT 0;
  DECLARE v_avg_bal DOUBLE DEFAULT 0;
  DECLARE v_max_date DATE;
  DECLARE at_end INT DEFAULT 0;

  -- 帳戶游標
  DECLARE cur_account CURSOR WITH RETURN FOR SELECT * FROM T_DEPBAL;
  -- 交易彙總游標 (每日淨流入)
  DECLARE cur_journal CURSOR FOR
    SELECT ACCOUNT_NO, TRANS_DATE,
           COALESCE(SUM(CASE WHEN FLAG=0 THEN AMT END),0) AS OUT_AMT,
           COALESCE(SUM(CASE WHEN FLAG=1 THEN AMT END),0) AS IN_AMT,
           COALESCE(SUM(CASE WHEN FLAG=1 THEN AMT END),0) -
           COALESCE(SUM(CASE WHEN FLAG=0 THEN AMT END),0) AS NET_AMT
    FROM T_JOURNAL
    WHERE ACCOUNT_NO = v_account
    GROUP BY TRANS_DATE, ACCOUNT_NO
    ORDER BY TRANS_DATE;
  -- 最大交易日期游標
  DECLARE max_date_cur CURSOR FOR
    SELECT MAX(TRANS_DATE) FROM T_JOURNAL WHERE ACCOUNT_NO = v_account;

  OPEN cur_account;
  DELETE FROM T_DEPCHG;  -- 全量重建

  out_loop: LOOP
    FETCH cur_account INTO v_account, v_balance;
    IF SQLCODE = 100 THEN LEAVE out_loop; END IF;

    SET v_var_balance = v_balance;
    SET v_all = 0;
    SET v_start_date = DATE('2006-01-01');

    OPEN cur_journal;
    fetch_loop: LOOP
      FETCH cur_journal INTO v_account, v_trans_date, v_out, v_in, v_amt;
      IF SQLCODE = 100 THEN LEAVE fetch_loop; END IF;

      SET v_end_date = v_trans_date;
      SET v_days = DAYS(v_end_date) - DAYS(DATE('2006-01-01'));
      SET v_bt_days = DAYS(v_end_date) - DAYS(v_start_date);
      SET v_all = v_all + (v_var_balance * v_bt_days);
      SET v_avg_bal = v_all / v_days;

      INSERT INTO T_DEPCHG VALUES(v_account, v_start_date, v_end_date, v_var_balance, v_avg_bal);

      SET v_var_balance = v_var_balance + v_amt;
      SET v_start_date = v_end_date;
    END LOOP;
    CLOSE cur_journal;

    -- 處理尾段區間 (最後交易日 ~ 2050-12-31)
    OPEN max_date_cur;
    FETCH max_date_cur INTO v_max_date;
    CLOSE max_date_cur;

    IF v_start_date <= v_max_date THEN
      SET v_days = DAYS('2050-12-31') - DAYS(DATE('2006-01-01'));
      SET v_bt_days = DAYS('2050-12-31') - DAYS(v_start_date);
      SET v_all = v_all + (v_var_balance * v_bt_days);
      SET v_avg_bal = v_all / v_days;
      INSERT INTO T_DEPCHG VALUES(v_account, v_start_date, DATE('2050-12-31'), v_var_balance, v_avg_bal);
    END IF;
  END LOOP;
  CLOSE cur_account;
END

關鍵算法說明

1. 區間加權平均：AVG_BAL = Σ(餘額 × 天數) / 總天數，精確反映區間內每日餘額加權。
2. 雙層游標巢狀：外層遍歷帳戶，內層遍歷該帳戶交易流水，符合 SCD Type 2 逐筆建檔邏輯。
3. 尾段補齊：最後一筆交易後至 2050-12-31 自動生成最終區間，確保拉鏈閉合。

呼叫 CALL BAL() 即可完成全量拉鏈表重建，配合排程實現日終/月終自動化。