Snowflake中如何将SQL计算得到的负值更新为0

方案1：使用内置GREATEST函数（推荐） #

Snowflake提供的GREATEST函数可直接返回多个输入值中的最大值，将你的原有计算逻辑与0同时传入该函数，所有小于0的计算结果会自动被替换为0，实现代码最简洁：

GREATEST(
    CASE 
        WHEN round(months_between(CURRENT_DATE, nvl(bpi.StartDate, cli.StartDate))) > round(months_between(nvl(bpi.EndDate, cli.EndDate), nvl(bpi.StartDate, cli.StartDate)))
            THEN round(months_between(nvl(bpi.EndDate, cli.EndDate), nvl(bpi.StartDate, cli.StartDate)))
        ELSE round(months_between(CURRENT_DATE, nvl(bpi.StartDate, cli.StartDate)), 1)
    END,
    0
) AS Months_Used

如果需要保留小数位一致性，可将第二个参数改为0.0即可。

方案2：外层嵌套CASE语句（兼容特殊场景） #

如果场景不支持直接用GREATEST，也可以在外层套一层CASE做负值判断，对应你的业务逻辑完整代码如下：

CASE
    WHEN (
        CASE 
            WHEN round(months_between(CURRENT_DATE, nvl(bpi.StartDate, cli.StartDate))) > round(months_between(nvl(bpi.EndDate, cli.EndDate), nvl(bpi.StartDate, cli.StartDate)))
                THEN round(months_between(nvl(bpi.EndDate, cli.EndDate), nvl(bpi.StartDate, cli.StartDate)))
            ELSE round(months_between(CURRENT_DATE, nvl(bpi.StartDate, cli.StartDate)), 1)
        END
    ) < 0 THEN 0
    ELSE (
        CASE 
            WHEN round(months_between(CURRENT_DATE, nvl(bpi.StartDate, cli.StartDate))) > round(months_between(nvl(bpi.EndDate, cli.EndDate), nvl(bpi.StartDate, cli.StartDate)))
                THEN round(months_between(nvl(bpi.EndDate, cli.EndDate), nvl(bpi.StartDate, cli.StartDate)))
            ELSE round(months_between(CURRENT_DATE, nvl(bpi.StartDate, cli.StartDate)), 1)
        END
    )
END AS Months_Used

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