SQL更新表计算百分比结果为0，求正确实现方法

需要更新table1表的rate字段，计算规则为：rate = round(score / sum(score) * 100, 2)。

原始表数据 #

+-------+------+
| score | rate |
+-------+------+
|    49 |    0 |
|    27 |    0 |
|    26 |    0 |
|    28 |    0 |
|     7 |    0 |
|     6 |    0 |
|     7 |    0 |
|    13 |    0 |
|    12 |    0 |
|    13 |    0 |
|    13 |    0 |
|     3 |    0 |
|     6 |    0 |
|    13 |    0 |
|     5 |    0 |
|     5 |    0 |
|    10 |    0 |
|   707 |    0 |
+-------+------+

期望结果表 #

+-------+-------+
| score | rate  |
+-------+-------+
|    49 | 5.16  |
|    27 | 2.84  |
|    26 | 2.74  |
|    28 | 2.95  |
|     7 | 0.74  |
|     6 | 0.63  |
|     7 | 0.74  |
|    13 | 1.37  |
|    12 | 1.26  |
|    13 | 1.37  |
|    13 | 1.37  |
|     3 | 0.32  |
|     6 | 0.63  |
|    13 | 1.37  |
|     5 | 0.53  |
|     5 | 0.53  |
|    10 | 1.05  |
|   707 | 74.42 |
+-------+-------+

尝试的SQL（执行后rate始终为0） #

update table1 
set rate = round((score / (select sum(score) 
                           from table1) * 100)::numeric, 2);

解决方案 #

问题根源是整数除法：score和sum(score)均为整数类型，整数相除时会直接取整（比如49/950的整数结果为0），后续乘以100仍为0，最终round后还是0。

只需将除法运算中的任意一个操作数转换为数值类型，即可触发浮点除法，修正后的SQL如下：

方法1：转换sum(score)为numeric

update table1 
set rate = round((score / (select sum(score)::numeric from table1) * 100), 2);

方法2：转换score为numeric

update table1 
set rate = round(((score::numeric / (select sum(score) from table1)) * 100), 2);

执行上述任意SQL后，rate字段即可得到符合预期的计算结果。

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