我有一個CREDITS包含這些列的表：
- DAY_OPERATIONAL
- TOTAL_LOAN
- CREDITS_PERCENT
我應該將每一行相乘TOTAL_LOAN，CREDITS_PERCENT然后除以SUM(TOTAL_LOAN)那個DAY_OPERATIONAL。
我的代碼不起作用。
我怎樣才能做到這一點？我需要如何正確使用 group by ？
SELECT
(total_loan * credits_percent) / SUM(total_loan)
FROM
credits
WHERE
day_operational = '29.12.2021'
uj5u.com熱心網友回復：
我是這樣理解這個問題的。
tc而tl在這里只是為了顯示使用了哪些值來計算最終的result。
SQL> with credits (day_operational, total_loan, credits_percent) as
2 (select date '2021-12-01', 100, 1 from dual union all
3 select date '2021-12-01', 200, 4 from dual union all
4 --
5 select date '2021-12-02', 500, 5 from dual
6 )
7 select day_operational,
8 sum(total_loan * credits_percent) tc,
9 sum(total_loan) tl,
10 --
11 sum(total_loan * credits_percent) / sum(total_loan) result
12 from credits
13 group by day_operational
14 order by day_operational;
DAY_OPERAT TC TL RESULT
---------- ---------- ---------- ----------
01.12.2021 900 300 3
02.12.2021 2500 500 5
SQL>
所以：
01.12.2021: 100 * 1 200 * 4 = 100 800 = 900
100 200 = 300 --> 900 / 300 = 3
02.12.2021: 500 * 5 = 2500
500 = 500 --> 2500 / 500 = 5
截至day_operational：如果它的資料型別是date（應該是！），那么不要將它與字串進行比較。'29.12.2021'是一個字串。利用
日期文字（正如我所做的那樣）：
date '2021-12-29'或to_date具有適當格式掩碼的函式：to_date('29.12.2021', 'dd.mm.yyyy')
uj5u.com熱心網友回復：
TOTAL_LOAN * CREDITS_PERCENT / SUM(TOTAL_LOAN)
=
CREDITS_PERCENT * (TOTAL_LOAN / SUM(TOTAL_LOAN)
因此
SELECT
day_operational
, credits_percent * RATIO_TO_REPORT(total_loan) OVER (PARTITION BY day_operational)
FROM credits
見RATIO_TO_REPORT
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/402005.html