postgreSQL 除法运算返回0

1. 场景

postgreSQL 除法运算返回0

2. 原因

postgreSQL 进行除法运算，应该使用round+numeric 类型，否则当计算结果小于1 时返回0，大于1 时只返回整数部分.

3. 解决办法

如实例：

select tp.*,tsn.shot_num,tsn.sensor_id,ts.binding_state
        ,(tp.lc_red_alert-tsn.shot_num::int) as gap
        ,round(cast((tp.lc_red_alert-tsn.shot_num::int) as numeric) /cast(tp.lc_red_alert as numeric) ,4) as residual_life_span
        from t_param as tp
        join t_sensor as ts  on tp.tooling_id=ts.tooling_id
        join t_shot_num as tsn on tsn.sensor_id=ts.sensor_id

参考：
https://blog.csdn.net/qq_35461048/article/details/108060877