SQLite的ROUND函数的坑和解决方案，sqliteround函数

SQLite的ROUND函数的坑和解决方案，sqliteround函数

SQLite内置的round函数，有时候其反应和我们预期的反应不一样，粗略说来，就是double的精度问题，无法精确的表达所有实数，只是在近似的表达着它们。

下面就是round函数抽风的例子和替代的解决方案：

sqlite> select  5.75*0.9, round(5.75*0.9, 2),  FLOOR( 5.75*100*0.9+0.5)/100.0;
+----------+--------------------+--------------------------------+
| 5.75*0.9 | round(5.75*0.9, 2) | FLOOR( 5.75*100*0.9+0.5)/100.0 |
+----------+--------------------+--------------------------------+
| 5.175    | 5.17               | 5.18                           |
+----------+--------------------+--------------------------------+
1 row in set

sqlite> select 10.25*1.1, ROUND(10.25*1.1, 2), FLOOR(10.25*100*1.1+0.5)/100.0;
+-----------+---------------------+--------------------------------+
| 10.25*1.1 | ROUND(10.25*1.1, 2) | FLOOR(10.25*100*1.1+0.5)/100.0 |
+-----------+---------------------+--------------------------------+
| 11.275    | 11.28               | 11.28                          |
+-----------+---------------------+--------------------------------+
1 row in set

有的情况下（python使用sqlalchemy时，在创建视图时，如果视图使用了FLOOR，视图创建失败），我们无法使用SQLite的FLOOR函数和CEIL函数，此时我们还有一种替代方案：
先将REAL类型的数值转换为字符串，转换后的字符串会有一个小数点，然后将小数点替换为一个无效字符（比如下划线'_'），然后再将其转换为数值，进行运算。
具体方案如下：

sqlite> select REPLACE(5.75*100*0.9+0.5, '.', '_')/100.0;
+-------------------------------------------+
| REPLACE(5.75*100*0.9+0.5, '.', '_')/100.0 |
+-------------------------------------------+
| 5.18                                      |
+-------------------------------------------+
1 row in set

其转换过程大致是这样的：
运算后的数是一个浮点数：
sqlite> select  TYPEOF(5.75*100*0.9+0.5);
+--------------------------+
| TYPEOF(5.75*100*0.9+0.5) |
+--------------------------+
| real                     |
+--------------------------+
1 row in set

将其转换为字符串：
sqlite> select   LOWER(5.75*100*0.9+0.5);
+-------------------------+
| LOWER(5.75*100*0.9+0.5) |
+-------------------------+
| 518.0                   |
+-------------------------+
1 row in set

替换字符串中的小数点：
sqlite> select REPLACE(5.75*100*0.9+0.5, '.', '_');
+-------------------------------------+
| REPLACE(5.75*100*0.9+0.5, '.', '_') |
+-------------------------------------+
| 518_0                               |
+-------------------------------------+
1 row in set

将替换后的字符串参与运算：
sqlite> select REPLACE(5.75*100*0.9+0.5, '.', '_')/100.0;
+-------------------------------------------+
| REPLACE(5.75*100*0.9+0.5, '.', '_')/100.0 |
+-------------------------------------------+
| 5.18                                      |
+-------------------------------------------+
1 row in set

sqlite> 

因为FLOOR函数得到的是一个integer，我们如果想模拟FLOOR函数，可以将替换后的字符串除以1，因为没有小数，SQLite就自动将其转换为integer了。
如下所示：

sqlite> select REPLACE(5.75*100*0.9+0.5, '.', '_')/1;
+---------------------------------------+
| REPLACE(5.75*100*0.9+0.5, '.', '_')/1 |
+---------------------------------------+
| 518                                   |
+---------------------------------------+
1 row in set

可以看到，因为除完之后没有小数，它的类型直接变成了integer。
sqlite> select TYPEOF(REPLACE(5.75*100*0.9+0.5, '.', '_')/1);
+-----------------------------------------------+
| TYPEOF(REPLACE(5.75*100*0.9+0.5, '.', '_')/1) |
+-----------------------------------------------+
| integer                                       |
+-----------------------------------------------+
1 row in set

而直接除100的话，因为有小数，它的类型就变成了real。
sqlite> select TYPEOF(REPLACE(5.75*100*0.9+0.5, '.', '_')/100);
+-------------------------------------------------+
| TYPEOF(REPLACE(5.75*100*0.9+0.5, '.', '_')/100) |
+-------------------------------------------------+
| real                                            |
+-------------------------------------------------+
1 row in set

用integer除以integer的话，就是取整了：
sqlite> select REPLACE(5.75*100*0.9+0.5, '.', '_')/1/100;
+-------------------------------------------+
| REPLACE(5.75*100*0.9+0.5, '.', '_')/1/100 |
+-------------------------------------------+
| 5                                         |
+-------------------------------------------+
1 row in set

sqlite> select TYPEOF(REPLACE(5.75*100*0.9+0.5, '.', '_')/1/100);
+---------------------------------------------------+
| TYPEOF(REPLACE(5.75*100*0.9+0.5, '.', '_')/1/100) |
+---------------------------------------------------+
| integer                                           |
+---------------------------------------------------+
1 row in set

sqlite> 

完。