我试图在查询中执行以下计算:
t0.value1::decimal * 10 ^ (-1 * t2.value2::decimal) * t0.value3::decimal as total_value
但是,由于(-1 * t2.value2::decimal)
返回-18,所有结果都四舍五入为0。我试图明确说明小数位数和精度//将其更改为数字,但得到了相同的输出。只要(-1 * t2.value2::decimal)
大于-10且小于10(如果小数位数小于10(,它就可以正常工作。
如何解决?
UPD:这是的数据样本
value1 | value2 | value3 | |
---|---|---|---|
4600000000000000000 | 18 | 4631.012195988508 | |
500000000000000000 | 18 | 3273.4212494812623[/tr>||
18 | 2422.052197425213 | ||
25000000000000 | 18 | <2549.800132829858>||
969000000000000000 | 18 | 4109.547860742057 | |
598000000000000000000000000 | 18 | 2957.913800830533 | |
4200000000000000 | 18 | 3410.6366004760075 | |
8000000000000000000 | 18 | 3902.894047163281 | |
6000000000000000000 | 18 | 4604.587023538565 |
10被解释为小数位数为20的小数。赋予它更高的精度和规模:
t0.value1::decimal * 10::decimal(40,20) ^ (-1 * t2.value2::decimal) * t0.value3::decimal as total_value
这种行为被PostgreSQL开发人员认为是一个错误,并在PostgreSQL v16中的这次提交中得到了修复。该修复将不应用于较旧的版本;更改查询的风险导致稳定的分支";。
x^(-y) = 1/x^y
,这很有效。
select value1::decimal * (1/10 ^(value2::decimal)) * value3::decimal as total_value
from t
total_value |
---|
21302.6561015471368000000000000000000000000000 |
16367.1062474063115000000000000000000000000000 |
63745.003320746450000000000000000000000000000000000000 |
39821.51877059053233000000000000000000000000000000 |
17688.32452896658734000000000000000000000000000000000000000 |
14324.6737219992315000000000000000000000000000000000 |
31223.152377306248000000000000000000000000000000000000 |
27627.52214123139000000000000000000000000000000000000 |