Float與Real數(shù)據(jù)類型的陷阱

SQL Prompt根據(jù)數(shù)據(jù)庫(kù)的對(duì)象名稱、語(yǔ)法和代碼片段自動(dòng)進(jìn)行檢索，為用戶提供合適的代碼選擇。自動(dòng)腳本設(shè)置使代碼簡(jiǎn)單易讀--當(dāng)開(kāi)發(fā)者不大熟悉腳本時(shí)尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。本文介紹了使用Float或Real數(shù)據(jù)類型的危險(xiǎn)。

浮點(diǎn)數(shù)據(jù)類型可容納非常大的數(shù)字，但是缺少了精度。它們對(duì)于某些類型的科學(xué)計(jì)算很方便，但是在更廣泛地使用時(shí)很危險(xiǎn)，因?yàn)樗鼈儠?huì)引入較大的舍入誤差。

浮點(diǎn)運(yùn)算就是為了避免計(jì)算中的溢出錯(cuò)誤而容忍和管理近似。在現(xiàn)實(shí)世界中，我們通常關(guān)心數(shù)字的準(zhǔn)確性，而會(huì)犧牲空間和資源以避免溢出。

科學(xué)是在誤差范圍內(nèi)工作的，而精確在商業(yè)會(huì)計(jì)中至關(guān)重要。當(dāng)我還是一名初級(jí)程序員時(shí)，我曾經(jīng)寫過(guò)一種我認(rèn)為是銀行計(jì)算交易利潤(rùn)非常合適的方法。在一百萬(wàn)英鎊中，最多也就一兩便士的誤差，我很滿意。它使用了我們當(dāng)時(shí)用來(lái)開(kāi)發(fā)財(cái)務(wù)軟件包的PL / 1編譯器中固有的計(jì)算。我向他們展示了精心制作的應(yīng)用程序，他們感到震驚。冷酷無(wú)情的銀行家們毫不留情地表示一百萬(wàn)英鎊沒(méi)了幾分錢。他們不會(huì)接受的。我被迫用精確的匯編代碼編寫一個(gè)精確的二進(jìn)制編碼的十進(jìn)制（BCD）程序包。

SQL Prompt具有代碼分析規(guī)則（BP023），該規(guī)則將提醒您使用Float或Real數(shù)據(jù)類型，這是因?yàn)樗鼈兛赡軙?huì)引入許多組織通常在其SQL Server數(shù)據(jù)上常規(guī)執(zhí)行的那種計(jì)算方式。

近似數(shù)的數(shù)據(jù)類型

浮點(diǎn)運(yùn)算是在優(yōu)先考慮節(jié)省內(nèi)存的同時(shí)，提供了一種涉及大量運(yùn)算的通用方法的時(shí)代設(shè)計(jì)出來(lái)的。盡管它對(duì)于許多類型的科學(xué)計(jì)算（尤其是那些符合浮點(diǎn)算術(shù)雙精度IEEE 754標(biāo)準(zhǔn)的科學(xué)計(jì)算）仍然有用，但它必然是一種折衷方案。線索就是這種數(shù)據(jù)和算術(shù)的名稱：“近似”。浮點(diǎn)數(shù)不能精確表示所有實(shí)數(shù)：此外，浮點(diǎn)運(yùn)算不能精確表示所有算術(shù)運(yùn)算。但是，即使不總是精確地保留數(shù)字，它們可以保留的數(shù)字的幅度范圍也遠(yuǎn)大于其他數(shù)字類型。

使用浮點(diǎn)運(yùn)算引起的問(wèn)題是由于復(fù)雜計(jì)算過(guò)程中的四舍五入而引起的，如果數(shù)據(jù)處于“不良條件”狀態(tài)，則最常見(jiàn)的問(wèn)題就是輸入中的細(xì)微變化會(huì)在輸出中放大。隨著數(shù)字表示精度的提高，這種不精確性已經(jīng)不那么明顯了，但是它們?nèi)匀淮嬖?。在使用有效但不能用浮點(diǎn)數(shù)表示的數(shù)字時(shí)，還存在一些深?yuàn)W的限制，例如tan（π/ 2），但這些可能僅會(huì)激發(fā)數(shù)學(xué)家的興趣。

SQL Server浮點(diǎn)數(shù)據(jù)類型

SQL標(biāo)準(zhǔn)具有三個(gè)浮點(diǎn)，近似數(shù)據(jù)類型、REAL、DOUBLE PRECISION和FLOAT（n）。 SQL Server符合此要求，只是它沒(méi)有DOUBLE PRECISION數(shù)據(jù)類型，而改用FLOAT（53）。 FLOAT（24）和FLOAT（53）數(shù)據(jù)類型對(duì)應(yīng)于IEEE 754標(biāo)準(zhǔn)中的Binary32（Single）和Binary64（double），并分別存儲(chǔ)在4和8字節(jié)中，并分別保留7和16位數(shù)字。當(dāng)計(jì)算產(chǎn)生與使用還使用IEEE 754的.NET框架的應(yīng)用程序相同的結(jié)果很重要時(shí)，它們很有用。當(dāng)數(shù)字的大小超過(guò)DECIMAL數(shù)據(jù)類型所允許的最大值（38位）時(shí)，還需要雙精度類型，但精度下降。當(dāng)然，近似數(shù)不能可靠地用于任何相等性檢驗(yàn)中，例如WHERE子句。

使用REAL數(shù)據(jù)類型的計(jì)算（單精度）

我將嘗試REAL數(shù)據(jù)類型。FLOAT(24)數(shù)據(jù)類型或更小的數(shù)據(jù)類型以相同的方式反應(yīng)。在SQL Server中使用浮點(diǎn)數(shù)進(jìn)行實(shí)驗(yàn)時(shí)，要記住的第一件事是，SSMS以掩蓋微小差異的方式呈現(xiàn)浮點(diǎn)數(shù)。例如：

SELECT Convert(REAL,0.100000001490116119384765625)

…得到0.1

為了更準(zhǔn)確地看到浮點(diǎn)數(shù)中存儲(chǔ)了什么值，您必須使用STR（）函數(shù)，指定實(shí)際需要的精度。

這已經(jīng)令人擔(dān)憂。畢竟，我們正在處理具有數(shù)百萬(wàn)行的數(shù)據(jù)，因此，除非像“銀行家四舍五入”之類的結(jié)果取平均值，否則小錯(cuò)誤就會(huì)堆積起來(lái)。這個(gè)錯(cuò)誤已經(jīng)接近我在引言中提到的“百萬(wàn)英鎊的便士”（1/240000000）！

讓我們避免使用0.1，并將其歸結(jié)為奇怪的浮點(diǎn)數(shù)。1除以3怎么樣？這肯定不是問(wèn)題吧？

哎呀，它錯(cuò)了。好的，這是一個(gè)很小的錯(cuò)誤，但請(qǐng)記住我關(guān)于銀行家的故事。答案是對(duì)還是錯(cuò)，穿著灰色西裝的男人沒(méi)有灰色陰影。在商學(xué)院，只有一個(gè)標(biāo)記和一個(gè)叉。沒(méi)有表示“足夠近”的標(biāo)志。

一個(gè)簡(jiǎn)單的測(cè)試是將數(shù)字1除以1到20。會(huì)出什么問(wèn)題呢？

我們可以存儲(chǔ)浮點(diǎn)數(shù)和數(shù)值計(jì)算的結(jié)果，將它們都轉(zhuǎn)換為字符串，然后比較字符串（請(qǐng)注意，字符串STR()可以放在前導(dǎo)空格中，這會(huì)使情況變得復(fù)雜）。

現(xiàn)在，如果我們列出那些數(shù)字不匹配的行呢？

啊! 只有在除數(shù)為1、2、4、8或16的情況下，結(jié)果才正確。

如果您希望某種程度上的浮點(diǎn)數(shù)是準(zhǔn)確的，而數(shù)值版本卻不正確，則以下是在Excel中計(jì)算出的數(shù)值商：

使用FLOAT（25）或更高（雙精度）的計(jì)算

如果使用雙精度浮點(diǎn)數(shù)FLOAT(25)或更高的精度，則所有測(cè)試都將通過(guò)，因?yàn)镾TR()函數(shù)最多允許小數(shù)點(diǎn)右邊16位。如果大于16，則結(jié)果將被截?cái)?。雙精度數(shù)據(jù)類型具有16位數(shù)字，而單精度數(shù)據(jù)類型具有7位數(shù)字。您還將看到單精度數(shù)據(jù)類型正確獲取了前七個(gè)數(shù)字。同樣，雙精度會(huì)正確獲取前16位數(shù)字。我們可以擴(kuò)大數(shù)字以查看近似值。

DECLARE @FirstApproximate FLOAT(53) = 10000000000000000.1
SELECT Str(@FirstApproximate,40,16) AS BigNumberWithaDecimal

那小部分消失了，不是嗎？這可能只是微小的差異，但是在某些計(jì)算中，它可能會(huì)引起問(wèn)題。

結(jié)論

浮點(diǎn)算法在存儲(chǔ)上既快速又經(jīng)濟(jì)，但提供了近似的結(jié)果。它適用于條件良好的科學(xué)應(yīng)用，但不適用于財(cái)務(wù)計(jì)算，因?yàn)樨?cái)務(wù)計(jì)算要求數(shù)字是“正確”或“錯(cuò)誤”。它在數(shù)據(jù)庫(kù)中還具有額外的缺點(diǎn)，因?yàn)槟荒芸煽壳乙恢碌販y(cè)試兩個(gè)近似數(shù)是否相等。

說(shuō)永遠(yuǎn)不要在SQL數(shù)據(jù)類型或算術(shù)中使用浮點(diǎn)數(shù)是不正確的。在SQL標(biāo)準(zhǔn)中，有一個(gè)特定的近似類型。如今，在有適當(dāng)要求的SQL Server中，我始終堅(jiān)持使用雙精度浮點(diǎn)數(shù)據(jù)類型。它們非常適合用于建模天氣系統(tǒng)或繪制軌跡等目的，但不適用于普通組織可能使用數(shù)據(jù)庫(kù)的計(jì)算類型。

如果發(fā)現(xiàn)錯(cuò)誤使用了這些類型，則應(yīng)改用合適的DECIMAL/ NUMERIC類型。如果您知道需要浮點(diǎn)算法并可以解釋原因，那么您可能足夠了解避免浮點(diǎn)的陷阱。