我需要按天、国家/地区等汇总一些会话。该表有每个会话的交易金额(以当地货币计(和一个字段,其中exchange_rate为交易时间的欧元。像这样:
amount | currency | exchange_rate | date | country
然而,我运行了sum(amount/exchange_rate(,对于大约0.5%的行,exchange_rate的值为0,因此它抛出了错误";不能除以0";。
我试着用案例运行它,当:
sum(case when exchange_rate = 0 then sum(amount) else sum(amount/exchange_rate) end) as volume
但显然嵌套的sum是不允许的。有人知道我如何才能得到上面逻辑上应该产生的结果,但在case-when语句中没有嵌套的和吗?
假设0
的汇率表明金额已经是EUR
货币,则可以执行:
sum(amount / case when exchange_rate = 0 then 1 else exchange_rate end)
我想你想要:
sum(case when exchange_rate = 0 then amount else amount/exchange_rate end) as volume
或者输入更少:
sum(amount / coalesce(nullif(exchange_rate, 0), 1)) as volume
本质上,当汇率为零时,您希望将其视为1
。你可以做:
sum(case when exchange_rate = 0
then amount
else amount/exchange_rate
end
) as volume