第10章 取整错误

老板要求你根据每个程序员修复每个Bug所花费的时间，来计算并给出一个关于项目开发时间成本的报表。每个在Accounts表中的程序员都有不同的时薪，因此你统计出每个程序员花在修复每个Bug上的时间，并用其乘以对应的时薪。

Rounding-Errors/intro/cost-per-bug.sql
SELECT b.bug_id, b.hours * a.hourly_rate AS cost_per_bug FROM Bugs AS b
   JOIN Accounts AS a ON (b.assigned_to = a.account_id);

要实现这样的查询功能，你需要在Bugs和Accounts表中创建新的列。这些新的列都应该支持浮点数，因为你需要精确地统计这些开销。你决定将这些列的类型定义为FLOAT，因为这种类型支持浮点数。

Rounding-Errors/intro/float-columns.sql
ALTER TABLE Bugs ADD COLUMN hours FLOAT;  ALTER TABLE Accounts ADD COLUMN hourly_rate FLOAT;

通过分析与Bug相关的工作日志及程序员的时薪，你更新了这些新列，测试了相关数据，然后结束了当天的工作。

第二天，你的老板拿了份项目开销报表出现在你的办公室里。“这些数字不正确，”他咬牙切齿地说道：“我自己手动算过一次，而你的报告是错的——虽然很小，只差几美元。你怎么解释这个问题？”你开始冒冷汗，这样简单的计算哪里会出问题呢？

整型是一个很有用的数据类型，但只能存储整数，比如1、327或者-19之类的。它不能表述像2.5这样的浮点数。如果数据对精度要求很高，你需要使用另一种数据类型来取代整型。比如，算钱的时候通常需要精确到小数点后两位，像 $19.95 这样。

因此，本章的目标就是存储非整数类型的数字，并且在基本运算中使用它们。还有另一个目标，运算的结果必须正确。当然，这个目标是最基本的要求，实现它是理所当然的。

大多数编程语言都支持实数类型，使用关键字float或者double。SQL也使用相同的关键字支持类似的数据类型。很多程序员很自然地就会在需要使用浮点数的地方使用SQL FLOAT类型，因为他们习惯于使用float类型编程。

SQL中的FLOAT类型，就和其他大多数编程语言的float类型一样，根据IEEE 754标准使用二进制格式编码实数数据。你需要了解一些浮点数的定义规范，才能有效地使用这个数据类型。

很多程序员并不清楚浮点类型的特性：并不是所有在十进制中描述的信息都能使用二进制存储。出于一些必要的因素，浮点数通常会舍入到一个非常接近的值。

让我们更加直观地了解这个取整操作。举例来说，1/3用一个无限循环的十进制可以表示为0.333…，真实的值无法完整地写出来，因为需要写无限多个3。小数点后数字的个数表示了这个数字的精确度，因此，无限循环地写下3，能够无限接近于1/3的精确值。

折中的办法是限制精度，选择一个尽可能接近原始值的数字，比如0.333。然而，这也意味着这个数字不是我们所希望的那个值。

即使提高了精度，仍旧不能将这三个近似值加起来得到1.0。这是使用有限精度的数表示无限小数时的必要妥协。

这意味着，你能想到的某些合理的数是无法用有限精度表示的。你可能觉得这样问题不大，因为你确实不可能输入一个无限小数，因而认为，既然实际输入的值是有限精度的，那么也应该能以相同的精度存储？但很不幸，事实并非如此。

IEEE 754使用二进制表示浮点数。十进制中的无限小数在二进制中的表达方式是完全不同的。然而一些十进制有限小数，比如59.95，在二进制中却需要表示为无限小数。FLOAT类型无法表达无限小数，因而，它存储了二进制表示中最接近59.95的值，用十进制表示等于59.950000762939。

有些值碰巧在两种表达方式中能达到相同的精度，从理论上来说，如果你了解IEEE 754标准的细节，就可以预测一个给定的十进制数如何以二进制形式存储。但在实际中，大多数的人不会关心每一个他们正在使用的浮点数的实际精度。你无法保证一个FLOAT类型的列中存储的值都是符合精度需求的，因此你的程序应该认为这一列中的每个值都是经过舍入的。

有些数据库支持其他相类似的数据类型DOUBLE PRECISION和REAL。包括FLOAT在内的三种数据类型的理论精度对于不同的数据库实现来说不尽相同，但它们都使用有限个二进制位来存储浮点数，因此它们都有着相似的取整行为。

有些数据库能够通过某种方式弥补数据的不精确性，输出我们所期望的值。

Rounding-Errors/anti/select-rate.sql
SELECT hourly_rate FROM Accounts WHERE account_id = 123;  Returns: 59.95

但FLOAT类型的列中实际存储的数据可能并不完全等于它的值。如果将这个值扩大十亿倍，就能看到其中的区别：

Rounding-Errors/anti/magnify-rate.sql
SELECT hourly_rate * 1000000000 FROM Accounts WHERE account_id = 123;  Returns: 59950000762.939

你可能希望上面那个扩大的查询返回的结果应该为59950000000.000。这说明IEEE 754标准的二进制模式将59.95转化成了一个可以用有限精度表示的值。在这个例子中，取整后的值与原值的误差为千万分之一以内，对于大多数的运算来说已经足够精确了。

然而，对于某些运算来说这样的误差还是不可容忍的的。最简单的例子就是用FLOAT进行比较操作。

Rounding-Errors/anti/inexact.sql
SELECT * FROM Accounts WHERE hourly_rate = 59.95;  Result: empty set; no rows match.

我们知道在hourly_rate列中实际存储的值比59.95要稍微大一点点。即使你给account_id为123的hourly_rate赋值为59.95，之前的那个查询也会以失败而告终。

通常的变通方案是将浮点数视作“近似相等”，即两个值之间的差值足够小就认为它们相等。我们将两个值相减，并使用SQL中的ABS()函数取其绝对值。如果结果为0，则表示两个数绝对相等。如果结果足够小，那我们就认为这两个数是近似相等的。下面的这个查询能够返回正确的结果：

Rounding-Errors/anti/threshold.sql
SELECT * FROM Accounts WHERE ABS(hourly_rate - 59.95) < 0.000001;

然而，即使是如此细小的差值在对精度要求较高的比较中也会失败：

Rounding-Errors/anti/threshold.sql
SELECT * FROM Accounts WHERE ABS(hourly_rate - 59.95) < 0.0000001;

由于十进制数和取整后的二进制数的绝对值不相同，我们需要合适的阈值。

另一个由于使用非精确的FLOAT造成误差的情况，是使用合计函数计算很多值的时候。比如，如果使用SUM()函数计算一列中的所有值，那最终得到的总和会受到这一列中所有非精确值的影响。

Rounding-Errors/anti/cumulative.sql
SELECT SUM( b.hours * a.hourly_rate ) AS project_cost FROM Bugs AS b JOIN Accounts AS a ON (b.assigned_to = a.account_id);

非精确浮点数所积累的影响对于求和之外的合计运算来说会更大。虽然误差看起来非常小，但其累加起来的效果不可忽视。比如，如果你用1精确地乘以1.0，那无论执行多少次，结果总是1。然而，如果这个乘数因子实际上是0.999，结果就完全不同。如果你用1乘以0.999一千次，你得到的结果将约等于0.3677。这样的操作执行次数越多，误差就越大。

实际中需要连续多次进行浮点数乘法运算的例子，是在金融项目中计算复利。使用非精确浮点数所造成的误差看起来很小，但会不断累加。因此，在金融项目中使用精确值是非常重要的。

实际上，任何使用FLOAT、REAL或者DOUBLE PRECISION类型的设计都有可能是反模式。大多数应用程序使用的浮点数的取值范围并不需要达到IEEE 754标准所定义的最大/最小区间。

似乎在SQL中使用FLOAT类型是很自然的事情，毕竟它和大多数编程语言中的浮点类型所使用的关键字是一样的。但对于浮点数的存储，我们还有更好的选择。

当你需要存储的数据的取值范围很大，大于INTEGER和NUMERIC这两个类型所支持的范围时，FLOAT就是你的选择。科学计算类的程序就是FLOAT通常的应用场合。

Oracle使用FLOAT类型表示的是一个精确值，而BINARY_FLOAT类型是一个非精确值，使用的是IEEE 754标准编码。

使用SQL中的NUMERIC或DECIMAL类型来代替FLOAT及与其类似的数据类型进行固定精度的小数存储。

Rounding-Errors/soln/numeric-columns.sql
ALTER TABLE Bugs ADD COLUMN hours NUMERIC(9,2);  ALTER TABLE Accounts ADD COLUMN hourly_rate NUMERIC(9,2);

这些数据类型精确地根据你定义这一列时指定的精度来存储数据。通过类似于指定VARCHAR的长度的方式，将精度作为类型参数来定义列的类型。其精度所指的是，在这一列中的每个值最多所能包含的有效数字的个数。精度为9意味着你可以存储123456789，而12345678901则为非法值。

你也可以使用该类型的第二参数来指定其刻度。这里的刻度即指小数点后的位数。小数部分的数字也算在其有效位中，因此，精度9刻度2意味着可以存储1234567.89，而12345678.91或者123456.789都是非法值。

应用在某一列上的精度和刻度会影响到这一列中的每一行记录，也就是说，你无法在某些行上存储刻度为2的记录，同时又在另一些行上存储刻度为4的记录。在SQL中一列的数据类型对于这一列中的每条记录都是通用的（就如同定义了VARCHAR(20)意味着每一行都只能存储最大长度为20的字符串）。

NUMERIC和DECIMAL的优势之处在于，它们不会像FLOAT类型那样对存储的有理数进行舍入操作。假设你输入59.95，就可以确信实际存储的数据就是59.95。当使用存储的数据与原始数据59.95进行比较时，必然返回两者相等。

Rounding-Errors/soln/exact.sql
SELECT hourly_rate FROM Accounts WHERE hourly_rate = 59.95;  Returns: 59.95

同样地，如果你按比例将值扩展十亿倍，就可以得到所期望的值：

Rounding-Errors/soln/magnify-rate-exact.sql
SELECT hourly_rate * 1000000000 FROM Accounts WHERE hourly_rate = 59.95;  Returns: 59950000000

NUMERIC和DECIMAL这两个类型的行为是一样的，两者没有任何区别。DEC也是DECIMAL的简称。

你仍旧无法存储无限精度的数据，诸如1/3，但至少我们对十进制数的这些约束有了更深的了解。

如果你需要精确地表示十进制数，使用NUMERIC类型。FLOAT类型无法表示很多十进制的有理数，因此它们应该当成非精确值来处理。