金融產品/資產管理系統常需從十餘張表關聯匯總計算每日收益、費用、管理費等指標，寫入彙總表供報表查詢。本文展示標準 WITH (CTE) → CREATE VIEW → INSERT SELECT 模式完整代碼結構。
GL_PRODUCTASSETS (產品-資產映射)、PRODUCT_INFO (產品主檔)、ASSETS_INFO (資產主檔)PRODUCT_COST_INFO (依產品/資產、費用類型存儲托管費/管理費/投顧費/浮動管理費等)PRODUCT_FMFEE (每日浮動管理費記錄)
CREATE OR REPLACE VIEW PARAMETER_VIEW AS
WITH
-- 1. 基礎關聯：產品-資產映射 + 主檔資訊
BASE AS (
SELECT
g.PBS_MAINPRO_NO AS PRODUCT_ID,
p.PI_NO, p.PI_NAME, p.PI_START_DATE, p.PI_END_DATE,
p.PI_CUSTOMER_YIELD/100 AS PROD_YIELD,
a.AI_NO, a.CAS_ASSETS_NA, a.DONO,
a.CAS_ISSUE_SUM, a.CAS_YIELD_RATE/100 AS ASSET_YIELD,
a.CAS_VALUE_DATE, a.CAS_DUE_DATE,
g.GL_STATE
FROM GL_PRODUCTASSETS g
LEFT JOIN PRODUCT_INFO p ON g.PBS_MAINPRO_NO = p.PI_NO
LEFT JOIN ASSETS_INFO a ON g.CAS_ASSETS_NU = a.AI_NO
WHERE g.GL_STATE = 1
),
-- 2. 浮動管理費彙總 (每產品累計)
FMFEE_SUM AS (
SELECT PRODUCT_ID, SUM(FMFEE) AS FMFEE_TOTAL
FROM PRODUCT_FMFEE
GROUP BY PRODUCT_ID
),
-- 3. 當日浮動管理費
FMFEE_TODAY AS (
SELECT PRODUCT_ID, SUM(FMFEE) AS FMFEE_DAILY
FROM PRODUCT_FMFEE
WHERE INPUT_DATE = TRUNC(SYSDATE)
GROUP BY PRODUCT_ID
),
-- 4. 費率表拆解 (按費用類型、對象類型 PIVOT)
COST_RATES AS (
SELECT
FEEOBJECTNO,
MAX(CASE WHEN FEEOBJECTTYPE='Product' AND FEETYPE='托管費' THEN FEERATIO/100 END) AS PROD_CUSTODY_RATE,
MAX(CASE WHEN FEEOBJECTTYPE='Product' AND FEETYPE='管理費' THEN FEERATIO/100 END) AS PROD_MGMT_RATE,
MAX(CASE WHEN FEEOBJECTTYPE='Product' AND FEETYPE='投顧費' THEN FEERATIO/100 END) AS PROD_ADV_RATE,
MAX(CASE WHEN FEEOBJECTTYPE='Asset' AND FEETYPE='托管費' THEN FEERATIO/100 END) AS ASSET_CUSTODY_RATE,
MAX(CASE WHEN FEEOBJECTTYPE='Asset' AND FEETYPE='管理費' THEN FEERATIO/100 END) AS ASSET_MGMT_RATE,
MAX(CASE WHEN FEEOBJECTTYPE='Asset' AND FEETYPE='投顧費' THEN FEERATIO/100 END) AS ASSET_ADV_RATE,
SUM(CASE WHEN FEEOBJECTTYPE='Product' AND FEETYPE NOT IN ('托管費','管理費','投顧費','浮動管理費') AND FEERATIO>1 THEN FEERATIO ELSE 0 END) AS PROD_OTHER_FEE_AMT,
SUM(CASE WHEN FEEOBJECTTYPE='Product' AND FEETYPE NOT IN ('托管費','管理費','投顧費','浮動管理費') AND FEERATIO<1 THEN FEERATIO ELSE 0 END) AS PROD_OTHER_FEE_RATE
FROM PRODUCT_COST_INFO
GROUP BY FEEOBJECTNO
),
-- 5. 組裝完整計算基礎集
CALC_BASE AS (
SELECT
b.*,
NVL(f1.FMFEE_TOTAL,0) AS FMFEE_TOTAL,
NVL(f2.FMFEE_DAILY,0) AS FMFEE_DAILY,
NVL(c.PROD_CUSTODY_RATE,0) AS PROD_CUSTODY_RATE,
NVL(c.PROD_MGMT_RATE,0) AS PROD_MGMT_RATE,
NVL(c.PROD_ADV_RATE,0) AS PROD_ADV_RATE,
NVL(c.ASSET_CUSTODY_RATE,0) AS ASSET_CUSTODY_RATE,
NVL(c.ASSET_MGMT_RATE,0) AS ASSET_MGMT_RATE,
NVL(c.ASSET_ADV_RATE,0) AS ASSET_ADV_RATE,
NVL(c.PROD_OTHER_FEE_AMT,0) AS PROD_OTHER_FEE_AMT,
NVL(c.PROD_OTHER_FEE_RATE,0) AS PROD_OTHER_FEE_RATE,
TRUNC(SYSDATE) AS CALC_DATE,
FLOOR(TRUNC(SYSDATE) - TO_DATE(b.PI_START_DATE,'YYYY-MM-DD HH24:MI:SS')) AS PROD_ELAPSED_DAYS,
FLOOR(TRUNC(SYSDATE) - TO_DATE(b.CAS_VALUE_DATE,'YYYY-MM-DD HH24:MI:SS')) AS ASSET_ELAPSED_DAYS
FROM BASE b
LEFT JOIN FMFEE_SUM f1 ON b.PRODUCT_ID = f1.PRODUCT_ID
LEFT JOIN FMFEE_TODAY f2 ON b.PRODUCT_ID = f2.PRODUCT_ID
LEFT JOIN COST_RATES c ON b.PI_NO = c.FEEOBJECTNO -- 產品費率
-- 資產費率需額外 JOIN ON b.AI_NO = c.FEEOBJECTNO (此處簡化)
)
-- 6. 最終指標計算
SELECT
PRODUCT_ID, PI_NO, PI_NAME, PROD_YIELD, PI_START_DATE, PI_END_DATE,
AI_NO, CAS_ASSETS_NA, DONO, CAS_ISSUE_SUM, ASSET_YIELD, CAS_VALUE_DATE, CAS_DUE_DATE,
CALC_DATE,
PROD_ELAPSED_DAYS, ASSET_ELAPSED_DAYS,
-- 產品每日/累計收益
PROD_YIELD * CAS_ISSUE_SUM / 365 AS PROD_DAILY_INCOME,
PROD_YIELD * CAS_ISSUE_SUM / 365 * PROD_ELAPSED_DAYS AS PROD_TOTAL_INCOME,
-- 產品各費用
PROD_MGMT_RATE * CAS_ISSUE_SUM / 365 AS PROD_DAILY_MGMT_FEE,
PROD_CUSTODY_RATE * CAS_ISSUE_SUM / 365 AS PROD_DAILY_CUSTODY_FEE,
PROD_ADV_RATE * CAS_ISSUE_SUM / 365 AS PROD_DAILY_ADV_FEE,
PROD_OTHER_FEE_AMT + PROD_OTHER_FEE_RATE * CAS_ISSUE_SUM / 365 AS PROD_DAILY_OTHER_FEE,
-- 資產每日/累計收益
ASSET_YIELD * CAS_ISSUE_SUM / 365 AS ASSET_DAILY_INCOME,
ASSET_YIELD * CAS_ISSUE_SUM / 365 * ASSET_ELAPSED_DAYS AS ASSET_TOTAL_INCOME,
-- 資產各費用
ASSET_MGMT_RATE * CAS_ISSUE_SUM / 365 AS ASSET_DAILY_MGMT_FEE,
ASSET_CUSTODY_RATE * CAS_ISSUE_SUM / 365 AS ASSET_DAILY_CUSTODY_FEE,
ASSET_ADV_RATE * CAS_ISSUE_SUM / 365 AS ASSET_DAILY_ADV_FEE,
-- 淨收益彙總
(PROD_YIELD * CAS_ISSUE_SUM / 365
- PROD_MGMT_RATE * CAS_ISSUE_SUM / 365
- PROD_CUSTODY_RATE * CAS_ISSUE_SUM / 365
- PROD_ADV_RATE * CAS_ISSUE_SUM / 365
- (PROD_OTHER_FEE_AMT + PROD_OTHER_FEE_RATE * CAS_ISSUE_SUM / 365)
+ FMFEE_DAILY
+ ASSET_YIELD * CAS_ISSUE_SUM / 365
- ASSET_MGMT_RATE * CAS_ISSUE_SUM / 365
- ASSET_CUSTODY_RATE * CAS_ISSUE_SUM / 365
- ASSET_ADV_RATE * CAS_ISSUE_SUM / 365
) AS NET_DAILY_INCOME
FROM CALC_BASE;
-- 目標表結構對應視圖欄位
CREATE TABLE DAILY_PRODUCT_ASSET_SUMMARY (
CALC_DATE DATE,
PRODUCT_ID VARCHAR2(20),
PI_NO VARCHAR2(20),
PI_NAME VARCHAR2(100),
PROD_DAILY_INCOME NUMBER(18,4),
PROD_TOTAL_INCOME NUMBER(18,4),
PROD_DAILY_MGMT_FEE NUMBER(18,4),
PROD_DAILY_CUSTODY_FEE NUMBER(18,4),
PROD_DAILY_ADV_FEE NUMBER(18,4),
PROD_DAILY_OTHER_FEE NUMBER(18,4),
ASSET_DAILY_INCOME NUMBER(18,4),
ASSET_TOTAL_INCOME NUMBER(18,4),
ASSET_DAILY_MGMT_FEE NUMBER(18,4),
ASSET_DAILY_CUSTODY_FEE NUMBER(18,4),
ASSET_DAILY_ADV_FEE NUMBER(18,4),
NET_DAILY_INCOME NUMBER(18,4),
-- 其他維度欄位...
CONSTRAINT PK_DAILY_SUMMARY PRIMARY KEY (CALC_DATE, PRODUCT_ID)
);
-- 每日排程插入 (可用 DBMS_SCHEDULER 或作業系統排程)
INSERT INTO DAILY_PRODUCT_ASSET_SUMMARY (
CALC_DATE, PRODUCT_ID, PI_NO, PI_NAME,
PROD_DAILY_INCOME, PROD_TOTAL_INCOME,
PROD_DAILY_MGMT_FEE, PROD_DAILY_CUSTODY_FEE, PROD_DAILY_ADV_FEE, PROD_DAILY_OTHER_FEE,
ASSET_DAILY_INCOME, ASSET_TOTAL_INCOME,
ASSET_DAILY_MGMT_FEE, ASSET_DAILY_CUSTODY_FEE, ASSET_DAILY_ADV_FEE,
NET_DAILY_INCOME
)
SELECT
CALC_DATE, PRODUCT_ID, PI_NO, PI_NAME,
PROD_DAILY_INCOME, PROD_TOTAL_INCOME,
PROD_DAILY_MGMT_FEE, PROD_DAILY_CUSTODY_FEE, PROD_DAILY_ADV_FEE, PROD_DAILY_OTHER_FEE,
ASSET_DAILY_INCOME, ASSET_TOTAL_INCOME,
ASSET_DAILY_MGMT_FEE, ASSET_DAILY_CUSTODY_FEE, ASSET_DAILY_ADV_FEE,
NET_DAILY_INCOME
FROM PARAMETER_VIEW
WHERE CALC_DATE = TRUNC(SYSDATE); -- 或指定業務日期
COMMIT;
MAX(CASE...) 將垂直存儲的費率轉為橫向欄位，避免多次 JOIN 同一表，大幅提升效能。INSERT SELECT，修改邏輯只改視圖，不動表結構與排程代碼。CALC_DATE、ELAPSED_DAYS 明確記錄計算基準，支援歷史回補、跨日對賬。(CALC_DATE, PRODUCT_ID) 保冪等性，重跑不產生重複。PRODUCT_INFO, ASSETS_INFO, PRODUCT_COST_INFO) 建立關聯欄位索引。PRODUCT_FMFEE 建立 (PRODUCT_ID, INPUT_DATE) 複合索引支援當日/累計查詢。DAILY_PRODUCT_ASSET_SUMMARY 按 CALC_DATE Range 分區) 提升歷史資料管理與查詢效能。
