PostgreSQL分组查询中floor函数计算结果异常的解决方法

问题场景 #

执行以下PostgreSQL查询时，出现floor(total_do_qty::numeric /10)计算结果不符合预期的情况：

• 8417除以10取floor得到841（结果正确）
• 2950除以10取floor得到293（正确结果应为295）

但单独执行简单计算语句时结果正常：

select 2950
, floor(2950::numeric / 10)
, floor(2950::numeric / 10) * 4 
limit 5 

原查询语句：

with debug as (
    select kode_toko_dbo 
    , kode_toko_final
    , nama_toko_final
    , concat (point_category,' - ',point) poin_kategori
    , point
    , sum(total_do_qty) total_do_qty
    , sum(case when upper(replace(item_name,' ','')) ~ upper('^.*Classic.*(6|8)') then floor(total_do_qty::numeric /10)
        when upper(kategori_name_l1) = 'RB FASCIA' then (floor(total_do_qty::numeric /10))
        when upper(replace(item_name,' ','')) ~  upper('^.*Classic.*(3|4)\.5') then floor(total_do_qty::numeric /10)
        else 0 end) reward_toko
    from outputnya 
    group by 1,2,3,4,5
    --order by reward_toko desc 
    )
    select kode_toko_dbo 
    , kode_toko_final 
    , nama_toko_final 
    , poin_kategori
    , point
    , total_do_qty
    , reward_toko   
    from debug
    where kode_toko_final  in ('2012000044','2012000044')
    order by reward_toko desc 

问题原因 #

原查询存在逻辑顺序错误：先对单条记录的total_do_qty取floor再求和，而非先求和符合条件的total_do_qty再取floor。

比如某门店对应多条符合条件的记录，如2949和1，原逻辑会计算floor(2949/10)=294 + floor(1/10)=0，总和为294；但实际预期是先求和2949+1=2950，再计算floor(2950/10)=295，两者结果差异由此产生。

解决方法 #

调整计算顺序：先筛选出符合条件的total_do_qty并求和，再对总和执行floor除以10的操作。修改后的查询语句如下：

with debug as (
    select kode_toko_dbo 
    , kode_toko_final
    , nama_toko_final
    , concat (point_category,' - ',point) poin_kategori
    , point
    , sum(total_do_qty) total_do_qty
    -- 先求和符合条件的total_do_qty，再取floor除以10
    , floor(
        sum(
            case 
                when upper(replace(item_name,' ','')) ~ upper('^.*Classic.*(6|8)') then total_do_qty
                when upper(kategori_name_l1) = 'RB FASCIA' then total_do_qty
                when upper(replace(item_name,' ','')) ~ upper('^.*Classic.*(3|4)\.5') then total_do_qty
                else 0 
            end
        )::numeric / 10
    ) as reward_toko
    from outputnya 
    group by 1,2,3,4,5
    --order by reward_toko desc 
    )
    select kode_toko_dbo 
    , kode_toko_final 
    , nama_toko_final 
    , poin_kategori
    , point
    , total_do_qty
    , reward_toko   
    from debug
    where kode_toko_final  in ('2012000044','2012000044')
    order by reward_toko desc 

验证逻辑 #

修改后的逻辑确保了：

1. 先将所有符合条件的单条total_do_qty累加得到总和
2. 对总和执行floor(总和/10)的计算，结果与单独执行简单计算的逻辑完全一致

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