我試圖找出一種方法來計算以下模型的兩個新列：
鏈接：
任何人都可以分享我如何實作這一目標的任何例子嗎？
uj5u.com熱心網友回復：
您可以使用SUM()視窗函式和CASE運算式來檢查第一筆交易：
SELECT *,
CASE
WHEN ROW_NUMBER() OVER (ORDER BY TRANSACTION_DATE, TRANSACTION_ID) = 1 THEN AMOUNT
ELSE SUM(AMOUNT) OVER (ORDER BY TRANSACTION_DATE, TRANSACTION_ID) - AMOUNT
END OPENING_BALANCE,
SUM(AMOUNT) OVER (ORDER BY TRANSACTION_DATE, TRANSACTION_ID) CLOSING_BALANCE
FROM TRANSATION_TABLE;
請參閱演示。
uj5u.com熱心網友回復：
select f.TRANSACTION_ID, f.TRANSACTION_DATE, f.AMOUNT,CASE WHEN sum(s.AMOUNT)-f.AMOUNT >0
THEN sum(s.AMOUNT)-f.AMOUNT ELSE s.AMOUNT END AS OPENING_BALANCE,sum(s.AMOUNT) as CLOSING_BALANCE
from TRANSATION_TABLE f inner join TRANSATION_TABLE s on f.TRANSACTION_ID >= s.TRANSACTION_ID
group by f.amount order by f.TRANSACTION_ID asc
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/339031.html
標籤：sql
上一篇：雙切片資料幀