我正在嘗試計算貸款的未來余額。我沒有對我正在處理的 Oracle 資料庫的寫權限，所以我不能使用 create table。我將有權訪問包括賬戶 ID、當前余額、利率、付款金額、付款頻率和剩余攤銷在內的賬戶資訊。根據這些資訊，我想計算出某筆付款后的貸款余額。以下是我現在的作業：
WITH mortgage_details (account_id, start_date, balance, annual_interest, pmt, pmt_freq, remain_amort) AS (
SELECT 1, TRUNC(SYSDATE, 'MM'), 34798, 2, 670, 'BW', 5 FROM DUAL UNION ALL
SELECT 2, TRUNC(SYSDATE, 'MM'), 50000, 10, 660, 'M', 6 FROM DUAL
),
mtg_final as
(
select mtg.*,
case when pmt_freq = 'BW' then 26
when pmt_freq = 'M' then 12
end as pmt_freq_updated
from mortgage_details mtg
)
select * from mtg_final
where account_id = 1
MODEL
DIMENSION BY (rownum rownumber)
-- Change Amount, Payment and Interest Rate here!
MEASURES (balance, 0 b, 0 c, pmt d, annual_interest e)
RULES SEQUENTIAL ORDER ITERATE (500) UNTIL (balance[ITERATION_NUMBER 1] <= 0) (
b[ITERATION_NUMBER 1] = balance[cv(rownumber)] * e[cv(rownumber)] / 1200,
d[ITERATION_NUMBER 1] = least(d[cv(rownumber)], balance[cv(rownumber)] b[cv(rownumber)]),
c[ITERATION_NUMBER 1] = d[cv(rownumber)] - b[cv(rownumber)],
balance[ITERATION_NUMBER 2] = balance[cv(rownumber)-1] - c[cv(rownumber) - 1],
e[ITERATION_NUMBER 2] = e[cv(rownumber)-1],
d[ITERATION_NUMBER 2] = d[cv(rownumber)-1]
)
order by rownumber
但是，問題是我沒有使用表中的 pmt_freq 和 leave_amort 欄位，而是硬編碼了一個 1200。我想用 pmt_freq* leave_amort 之類的東西替換這個 1200 但是當我嘗試這個時，我得到一個錯誤“列不這里允許”。這是我收到該錯誤時嘗試的方法：
WITH mortgage_details (account_id, start_date, balance, annual_interest, pmt, pmt_freq, remain_amort) AS (
SELECT 1, TRUNC(SYSDATE, 'MM'), 34798, 2, 670, 'BW', 5 FROM DUAL UNION ALL
SELECT 2, TRUNC(SYSDATE, 'MM'), 50000, 10, 660, 'M', 6 FROM DUAL
),
mtg_final as
(
select mtg.*,
case when pmt_freq = 'BW' then 26
when pmt_freq = 'M' then 12
end as pmt_freq_updated
from mortgage_details mtg
)
select * from mtg_final
where account_id = 1
MODEL
DIMENSION BY (rownum rownumber)
-- Change Amount, Payment and Interest Rate here!
MEASURES (balance, 0 b, 0 c, pmt d, annual_interest e)
RULES SEQUENTIAL ORDER ITERATE (500) UNTIL (balance[ITERATION_NUMBER 1] <= 0) (
b[ITERATION_NUMBER 1] = balance[cv(rownumber)] * e[cv(rownumber)] / (100*remain_amort*pmt_freq_updated),
d[ITERATION_NUMBER 1] = least(d[cv(rownumber)], balance[cv(rownumber)] b[cv(rownumber)]),
c[ITERATION_NUMBER 1] = d[cv(rownumber)] - b[cv(rownumber)],
balance[ITERATION_NUMBER 2] = balance[cv(rownumber)-1] - c[cv(rownumber) - 1],
e[ITERATION_NUMBER 2] = e[cv(rownumber)-1],
d[ITERATION_NUMBER 2] = d[cv(rownumber)-1]
)
order by rownumber
uj5u.com熱心網友回復：
在不審查您的整個方法的情況下，一種與您已有的方法非常接近的方法是在pmt_freq您的.remain_amortPARTITIONMODEL
PARTITION BY account_id（無論如何，如果您曾經為多個帳戶運行此查詢，您可能會想要這樣做）。
這就是它的樣子：
WITH mortgage_details (account_id, start_date, balance, annual_interest, pmt, pmt_freq, remain_amort) AS (
SELECT 1, TRUNC(SYSDATE, 'MM'), 34798, 2, 670, 'BW', 5 FROM DUAL UNION ALL
SELECT 2, TRUNC(SYSDATE, 'MM'), 50000, 10, 660, 'M', 6 FROM DUAL
)
select *
from mortgage_details
where account_id = 1
MODEL
-- Add the partition to keep results in your model separated by account_id
-- Since account_id uniquely identifies pmt_freq and remain_amort, we can
-- safely put them here too
PARTITION BY (account_id, pmt_freq, remain_amort)
DIMENSION BY (rownum rownumber)
-- Change Amount, Payment and Interest Rate here!
MEASURES (balance, 0 b, 0 c, pmt d, annual_interest e)
RULES SEQUENTIAL ORDER ITERATE (500) UNTIL (balance[ITERATION_NUMBER 1] <= 0) (
b[ITERATION_NUMBER 1] = balance[cv(rownumber)] * e[cv(rownumber)] / (100 * DECODE(cv(pmt_freq),'BW',26,'M',12,0)*cv(remain_amort)),
d[ITERATION_NUMBER 1] = least(d[cv(rownumber)], balance[cv(rownumber)] b[cv(rownumber)]),
c[ITERATION_NUMBER 1] = d[cv(rownumber)] - b[cv(rownumber)],
balance[ITERATION_NUMBER 2] = balance[cv(rownumber)-1] - c[cv(rownumber) - 1],
e[ITERATION_NUMBER 2] = e[cv(rownumber)-1],
d[ITERATION_NUMBER 2] = d[cv(rownumber)-1]
)
order by rownumber
uj5u.com熱心網友回復：
您可以將pmt_freq_updatedand添加remain_amort到MEASURES子句中，然后，如果需要，在規則中使用pmt_freq_updated[1]and參考它們remain_amort[1]（因為當您可以在第一排）。
如果您為列使用有意義的名稱，它也會有所幫助。
WITH mortgage_details (
account_id,
start_date,
balance,
annual_interest,
pmt,
pmt_freq,
remain_amort
) AS (
SELECT 1, TRUNC(SYSDATE, 'MM'), 34798, 2, 670, 'BW', 5 FROM DUAL UNION ALL
SELECT 2, TRUNC(SYSDATE, 'MM'), 50000, 10, 660, 'M', 6 FROM DUAL
),
mtg_final as
(
select mtg.*,
case
when pmt_freq = 'BW' then 26
when pmt_freq = 'M' then 12
end as pmt_freq_updated
from mortgage_details mtg
)
SELECT account_id,
key,
ROUND(balance, 2) AS balance,
dt,
ROUND(interest_amt, 2) AS interest_amt,
payment,
pmt_freq_updated,
remain_amort
FROM mtg_final
--where account_id = 1
MODEL
PARTITION BY (account_id)
DIMENSION BY (1 AS key)
MEASURES (
balance,
start_date dt,
pmt_freq,
pmt,
1 annual_interest/100 AS annual_interest,
0 AS interest_amt,
0 AS payment,
pmt_freq_updated,
remain_amort
)
RULES SEQUENTIAL ORDER
ITERATE (100) UNTIL (balance[ITERATION_NUMBER] <= 0)
(
dt[ITERATION_NUMBER 2] = dt[ITERATION_NUMBER 1] INTERVAL '1' DAY,
annual_interest[key>1] = annual_interest[1],
interest_amt[key] = balance[cv(key)]
* (
POWER(
annual_interest[1],
1/(ADD_MONTHS(TRUNC(dt[cv(key)], 'YYYY'), 12)
- TRUNC(dt[cv(key)], 'YYYY'))
) - 1
),
payment[key]
= CASE
WHEN (pmt_freq[1] = 'M' AND dt[cv(key)] = LAST_DAY(dt[cv(key)]))
OR (pmt_freq[1] = 'BW' AND MOD(cv(key), 14) = 0)
OR (pmt_freq[1] = 'W' AND MOD(cv(key), 7) = 0)
THEN pmt[1]
END,
balance[key>1] = balance[cv(key)-1]
interest_amt[cv(key) - 1]
- COALESCE(payment[cv(key)], 0)
)
order by account_id, dt
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/472544.html