PostgreSQL：如何在WHERE子句避免除零错误并筛选change>0.3的结果？

问题原因 #

你之前的写法里，psql变量:change会被直接替换成(q2.close-q1.close)/q1.close，而WHERE子句的执行优先级高于SELECT子句。也就是说，PostgreSQL会先计算WHERE里的:change > 0.3，这时候还没执行SELECT里的CASE判断，一旦遇到q1.close=0的行，就直接触发除零错误。

正确写法 #

下面提供三种可靠的解决方式：

方式1：先过滤掉q1.close=0的行 #

既然q1.close=0时要么结果是NULL，要么会触发除零，直接在WHERE里先排除这类行，再计算change条件：

\set start  '2024-05-20'
\set end    '2024-05-31'
\set change (q2.close-q1.close)/q1.close

select   q1.ticker,q1.date,
         :change as change
from quote q1,quote q2
where
    q1.ticker=q2.ticker
and    q1.date= :'start'
and    q2.date= :'end'
and    q1.close != 0  -- 先排除分母为0的情况
and    :change > 0.3;

方式2：用NULLIF处理分母，避免除零 #

如果不想排除q1.close=0的行（只是不想让这些行被筛选出来），可以用NULLIF(q1.close, 0)把0转换成NULL，这样除法结果会变成NULL，NULL和0.3比较时不会触发错误，且不会被选中：

\set start  '2024-05-20'
\set end    '2024-05-31'

select   q1.ticker,q1.date,
         case when q1.close=0 then null 
              else (q2.close-q1.close)/q1.close end as change
from quote q1,quote q2
where
    q1.ticker=q2.ticker
and    q1.date= :'start'
and    q2.date= :'end'
and    (q2.close-q1.close)/NULLIF(q1.close, 0) > 0.3;

方式3：用CTE封装计算结果，再筛选 #

把change的计算逻辑封装到CTE（公共表表达式）中，让WHERE子句直接引用已经处理好的change值，避免重复计算和除零风险：

\set start  '2024-05-20'
\set end    '2024-05-31'

with calculated_data as (
    select   q1.ticker,q1.date,
             case when q1.close=0 then null 
                  else (q2.close-q1.close)/q1.close end as change
    from quote q1,quote q2
    where
        q1.ticker=q2.ticker
    and    q1.date= :'start'
    and    q2.date= :'end'
)
select ticker, date, change
from calculated_data
where change > 0.3;

补充说明 #

• 用CTE封装逻辑可以提升代码的可读性和维护性，避免在多个地方重复编写复杂表达式。
• NULLIF(a, b)函数的作用是：如果a等于b则返回NULL，否则返回a，是处理除零、空值替换场景的常用工具。

内容的提问来源于stack exchange，提问作者showkey