/(除法)在PostgreSQL ，Oracle 中的使用

背景：

在pg数据库中需要实现判断出当期记录数与上期记录数的比较浮动值，浮动范围不能大于30%，如大于则应过滤出相关数据。

oracle 实现方式：

SELECT distinct (T1.CNT_DQ - T2.CNT_SQ) as 除数 ,
             CASE WHEN T2.CNT_SQ = 0 THEN 0.000001 ELSE T2.CNT_SQ END as 被除数 ,
             (ABS((T1.CNT_DQ - T2.CNT_SQ) / CASE WHEN T2.CNT_SQ = 0 THEN 0.000001
                                              ELSE T2.CNT_SQ END)) as 绝对值,
             30/100 as 浮动比例,
           case when ABS((T1.CNT_DQ - T2.CNT_SQ) / CASE WHEN T2.CNT_SQ = 0 THEN 0.000001
                                              ELSE T2.CNT_SQ END) > 30 / 100 then 1 else 0 end as 比较值1 ,
            case when ABS((T1.CNT_DQ - T2.CNT_SQ) / CASE WHEN T2.CNT_SQ = 0 THEN 0.000001
                                              ELSE T2.CNT_SQ END) > 0.3 then 1 else 0 end as 比较值2
      FROM test_oracle A
      left join  (SELECT COUNT(1) CNT_DQ
            FROM test_oracle A
            WHERE cjrq = '2023-11-01') T1
      on 1=1
      left join  (SELECT COUNT(1) CNT_SQ
            FROM test_oracle A
            WHERE cjrq = '2023-10-31') T2
      on 1=1
      WHERE A.cjrq = '2023-11-01';

PostgreSQL实现方式：

SELECT distinct (T1.CNT_DQ - T2.CNT_SQ) as 除数 ,
             CASE WHEN T2.CNT_SQ = 0 THEN 0.000001 ELSE T2.CNT_SQ END as 被除数 ,
             (ABS((T1.CNT_DQ - T2.CNT_SQ) / CASE WHEN T2.CNT_SQ = 0 THEN 0.000001
                                              ELSE T2.CNT_SQ END)) as 绝对值,
             30/100 as 浮动比例1,
             60 / 100 :: float8 as 浮动比例2 ,
            case when ABS((T1.CNT_DQ - T2.CNT_SQ) / CASE WHEN T2.CNT_SQ = 0 THEN 0.000001
                                              ELSE T2.CNT_SQ END) > 30 / 100 then 1 else 0 end as 比较值1 ,
            case when ABS((T1.CNT_DQ - T2.CNT_SQ) / CASE WHEN T2.CNT_SQ = 0 THEN 0.000001
                                              ELSE T2.CNT_SQ END) > 0.3 then 1 else 0 end as 比较值2 ,
            case when ABS((T1.CNT_DQ - T2.CNT_SQ) / CASE WHEN T2.CNT_SQ = 0 THEN 0.000001
                                              ELSE T2.CNT_SQ END) > 60 / 100 then 1 else 0 end as 比较值3 ,
            case when ABS((T1.CNT_DQ - T2.CNT_SQ) / CASE WHEN T2.CNT_SQ = 0 THEN 0.000001
                                              ELSE T2.CNT_SQ END) > 60 / 100 ::float8 then 1 else 0 end as 比较值4
      FROM test_pgsql A
      left join  (SELECT COUNT(1) CNT_DQ
            FROM test_pgsql A
            WHERE cjrq = '2023-11-01') T1
      on 1=1
      left join  (SELECT COUNT(1) CNT_SQ
            FROM test_pgsql A
            WHERE cjrq = '2023-10-31') T2
      on 1=1
      WHERE A.cjrq = '2023-11-01';

通过pg实现方式，可发现，pg在使用"/"(除法)时，整数除整数最后会得出一个整数，与oracle实现的不一样，如果要在pg中实现和Oracle一样的效果有两种方法：

(1) 整数除整数，对被除数进行case when 判断，判断后被除数就变成小数了，整数除以小数自然得出得也是小数。

eg：

select 30/case when 100 = 0 then 0.00001 else 100 end

(2)整数除整数，对被除数做数据类型转换。

eg：

select 30/100 ::float8,30/100 ::numeric