ruby-on-rails - rails : prevent division by zero with postgres

Question

我有一个带有 postgre 数据库的 Rails 应用程序，我正在尝试使用以下语句获取一些指标

Negotiation.find(:all, :conditions => conditions_hash('negotiation'), :select => 'sum(payment_term * amount_after_annualised_base) / sum(amount_after_annualised_base) as pt').first

我如何确保 sum(amount_after_annualised_base) 不为 0(因此不会引发“被零除”错误)？

我找到了这个，但不确定它是否正确/最好/最有效的方法。

Negotiation.find(:all, :conditions => conditions_hash('negotiation'), :select => 'sum(payment_term * amount_after_annualised_base) / (CASE sum(amount_after_annualised_base) WHEN 0 THEN NULL ELSE sum(amount_after_annualised_base) END) as pt').first

谢谢!皮埃尔

Answer 1

使用nullif :

Negotiation.find(:all, :conditions => conditions_hash('negotiation'), :select => 'sum(payment_term * amount_after_annualised_base) / nullif(sum(amount_after_annualised_base),0) as pt').first

这只是您解决方案的简写形式:

Negotiation.find(:all, :conditions => conditions_hash('negotiation'), :select => 'sum(payment_term * amount_after_annualised_base) / (CASE sum(amount_after_annualised_base) WHEN 0 THEN NULL ELSE sum(amount_after_annualised_base) END) as pt').first