PostgreSQL中如何将计算得到的负值替换为0

我有一张my_table表，表结构及数据如下：

case_id    first_created  last_paid       submitted_time
   3456    2021-01-27     2021-01-29      2021-01-26 21:34:36.566023+00:00
   7891    2021-08-02     2021-09-16      2022-10-26 19:49:14.135585+00:00
   1245    2021-09-13     None            2022-10-31 02:03:59.620348+00:00
   9073    None           None            2021-09-12 10:25:30.845687+00:00
   6891    2021-08-03     2021-09-17      None

我用以下SQL创建了两个计算字段：

select *,
       first_created-coalesce(submitted_time::date) as create_duration,
       last_paid-coalesce(submitted_time::date) as paid_duration 
from my_table;

查询结果里create_duration和paid_duration出现了负值，我希望把这些小于0的字段值替换为0，理想输出如下：

case_id    first_created  last_paid       submitted_time               create_duration paid_duration
   3456    2021-01-27     2021-01-29      2021-01-26 21:34:36.566023+00:00     1                3
   7891    2021-08-02     2021-09-16      2022-10-26 19:49:14.135585+00:00     0                0
   1245    2021-09-13     null            2022-10-31 02:03:59.620348+00:00     0               null
   9073    None           None            2021-09-12 10:25:30.845687+00:00     null             null
   6891    2021-08-03     2021-09-17      null                                 null             null

我尝试了以下代码但没实现需求，请问正确的SQL语句该怎么写？

select  *,
        first_created-coalesce(submitted_time::date) as create_duration,
        last_paid-coalesce(submitted_time::date) as paid_duration, 
        case
            when create_duration < 0 THEN 0
            else create_duration
        end as QuantityText
from my_table

正确SQL语句 #

方法1：用GREATEST函数简化逻辑 #

GREATEST函数返回参数中的最大值，刚好可以把负值替换为0，同时保留NULL（只要参数含NULL，函数返回NULL，符合需求）：

select *,
       GREATEST(first_created - COALESCE(submitted_time::date, 'infinity'::date), 0) as create_duration,
       GREATEST(last_paid - COALESCE(submitted_time::date, 'infinity'::date), 0) as paid_duration
from my_table;

给COALESCE加'infinity'::date兜底，是为了在submitted_time为NULL时，让日期减法结果为NULL，匹配理想输出的要求。

方法2：用CTE复用计算逻辑 #

如果觉得嵌套计算不够清晰，可以先用CTE算出原始差值，再在外层处理负值：

WITH temp_data AS (
    select *,
           first_created - COALESCE(submitted_time::date, 'infinity'::date) as raw_create,
           last_paid - COALESCE(submitted_time::date, 'infinity'::date) as raw_paid
    from my_table
)
select *,
       CASE WHEN raw_create < 0 THEN 0 ELSE raw_create END as create_duration,
       CASE WHEN raw_paid < 0 THEN 0 ELSE raw_paid END as paid_duration
from temp_data;

为什么你的尝试没生效？ #

SQL执行顺序是先处理FROM子句，再计算SELECT里的表达式，同一个SELECT中定义的别名（比如create_duration）还未被解析，无法直接在该子句的其他表达式（比如CASE）中使用，所以代码不符合预期。

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