求和SQL优化

SELECT id
,currencyCode
,(CASE WHEN total<=3 THEN amount ELSE (3 - last_total)/conversion_rate END) AS target_amount
,(CASE WHEN total<=3 THEN target_amount ELSE 3 - last_total END) AS target_amount
FROM (
SELECT t.*
,t.amount*r.conversion_rate as target_amount
,SUM(t.amount*r.conversion_rate) OVER(ORDER BY t.amount*r.conversion_rate,id) as total
,SUM(t.amount*r.conversion_rate) OVER(ORDER BY t.amount*r.conversion_rate,id) - t.amount*r.conversion_rate as last_total
,r.conversion_rate
FROM t_money t, t_rates r
WHERE t.currencyCode = r.from_currency
AND r.to_currency = 'usd'
)
WHERE total<=3
OR total>3
AND last_total<3
ORDER BY total;


--------------
SELECT id,
currencyCode,
(CASE
WHEN total <= 3 THEN
amount
ELSE
(3 - last_total)/nvl((select nvl(conversion_rate, 1)
from t_rates
where from_currency = currencycode
and upper(to_currency) = 'USD'),
1)
END)amount,
(CASE
WHEN total <= 3 THEN
target_amount
ELSE
3 - last_total
END) AS target_amount
FROM (SELECT t.*,
t.amount * r.conversion_rate as target_amount,
SUM(t.amount * r.conversion_rate) OVER(ORDER BY t.amount * r.conversion_rate, id) as total,
SUM(t.amount * r.conversion_rate) OVER(ORDER BY t.amount * r.conversion_rate, id) - t.amount * r.conversion_rate as last_total
FROM t_money t, t_rates r
WHERE t.currencyCode = r.from_currency
AND r.to_currency = 'usd')
WHERE total <= 3
OR total > 3
AND last_total < 3
ORDER BY total;