postgreSQL 小数点惹得祸

按每个活动的达标率展示

示例表 activity 如下:

activity | need | already | user
A        | 1000 | 1       |  U_A
B        |  500 | 1       |  U_B
B        |  500 | 1       |  U_B
A        | 1000 | 1       |  u_A

开始写SQL:

select 
    activity,
    count(1),
    need,
    round(count(1)::NUMERIC / need::NUMERIC, 2 ) already_rate
from 
    activity 
where
    activity in ('A', 'B')
group by
    activity, need
order by
    already_rate desc,  activity asc;
结果: 
| activity | count | need | already_rate |
| A        | 2     | 1000 | 0.00         |
| B        | 2     |  500 | 0.00         |

上边应该是B 排在A前边才对,且already_rate 也出错了,主要是由于精度没有掌握好,need数如果不都一样,比较随机,这个时候该怎么办呢?
不管咋变, count数不可能比 need数大,自然小数位的个数,可以由need的长度决定比较好,修改SQL如下:

select 
    activity,
    count(1),
    need,
    round(count(1)::NUMERIC / need::NUMERIC, length(need::text) ) already_rate
from 
    activity 
where
    activity in ('A', 'B')
group by
    activity, need
order by
    already_rate desc,  activity asc;
结果: 
| activity | count | need | already_rate |
| B        | 2     |  500 | 0.004        |
| A        | 2     | 1000 | 0.0020       |

完美了!