南大通用GBase 8a 增加数学运算后的小数点后精度位数

GBase 8a 可以通过函数运算的第二个参数指定小数点后的精度位数,而默认的数字运算为4位小数,更高的精度需要用decimal类型,通过指定的精度来进行计算。

默认的数字运算

默认2个数字运算,只有4位小数。

gbase> select 1/1024000;
+-----------+
| 1/1024000 |
+-----------+
|    0.0000 |
+-----------+
1 row in set (Elapsed: 00:00:00.01)

将数字cast成decimal并指定精度,来实现更高的小数点精度位数

注意,指定的是10位,结果为14位,GBase默认额外多出来4位结果,避免精度丢失。

gbase> select cast(1 as decimal(60,10))/1024000;
+-----------------------------------+
| cast(1 as decimal(60,10))/1024000 |
+-----------------------------------+
|                  0.00000097656250 |
+-----------------------------------+
1 row in set (Elapsed: 00:00:00.00)

如果结果集需要10为,可以用round等函数指定

gbase> select round(cast(1 as decimal(60,10))/1024000,10);
+---------------------------------------------+
| round(cast(1 as decimal(60,10))/1024000,10) |
+---------------------------------------------+
|                                0.0000009766 |
+---------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

对于表字段类型,默认也是以decimal的精度为准的

默认字段运算结果,也是定义的精度+4位


gbase> create table tdecimal(num decimal(60,8));
Query OK, 0 rows affected (Elapsed: 00:00:00.38)

gbase> insert into tdecimal values (1),(2.3456789);
Query OK, 2 rows affected (Elapsed: 00:00:00.11)
Records: 2  Duplicates: 0  Warnings: 0

gbase> select * from tdecimal;
+------------+
| num        |
+------------+
| 1.00000000 |
| 2.34567890 |
+------------+
2 rows in set (Elapsed: 00:00:00.06)

gbase> select num,num/123456 from tdecimal;
+------------+----------------+
| num        | num/123456     |
+------------+----------------+
| 1.00000000 | 0.000008100052 |
| 2.34567890 | 0.000019000121 |
+------------+----------------+
2 rows in set (Elapsed: 00:00:00.01)