精準度

遇到一個朋友問的,但我不知如何解的需求:

請教有關float(53)的問題

float的data type目前只能儲存38個位數的數值

情況假如是系統產生超過38位數的數值例如(x=0.0000…….1)被進位掉可能為0

但這個數值可能又被其他參數例如LOG10(x)來參考,此時會發生failure

目前我是先轉成字串之後取前38個字串再轉成數值,但這似乎不是很好的作法,而且會犧牲一些精確度

也有試過類似convert(nvarchar(max),’0.000000000000000500119125812236306378730339741119979581′,2)

轉成5.00119125812236E-16存進DB

想請問是否有更好的作法呢?

順代帶問一點

(1)insert into T168(col) values(0.000000000000000500119125812236306378730339741119979581)

(2)insert into T168(col) values(‘0.000000000000000500119125812236306378730339741119979581’)

有何不同,我這邊試的結果(1)會出現以下Error:

Msg 1007, Level 15, State 1, Line 1

The number ‘0.000000000000000500119125812236306378730339741119979581’ is out of the range for numeric representation (maximum precision 38).

但(2)會自動轉成5.00119125812236E-16 科學符號的顯示方式

 

我的解法

想不出用數值來做的方式,因為 float 只有 15 位精準度,其餘是科學記號的次方,或許當字串存,若有逗號,則將逗號後的部分轉成 decimal(38,38)

use tempdb
go
CREATE FUNCTION GetDecimalE(@num varchar(100))
RETURNS varchar(50)
AS
BEGIN
        DECLARE @i INT=0,@z char(1)=’0′,@o varchar(100),@ret varchar(50)
        IF LEFT(@num,2)=’0.’
        BEGIN
                –去掉0.
                SET @num=RIGHT(@num,LEN(@num)-2)
                SET @o=@num
                WHILE @z=’0′
                BEGIN
                        SET @i+=1
                        SET @z=LEFT(@num,1)
                        SET @num=RIGHT(@num,LEN(@num)-1)
                END

                SET @ret=CONVERT(varchar(2),@i-1) + ‘,’ + SUBSTRING(@o,@i,38)
        END
        ELSE
                SET @ret=SUBSTRING(@num,1,38)
        RETURN @ret
END
GO

–測試,但 log10 和 power 函數都是針對 float,所以運算時會將 decimal 轉成 float,不曉得運算後的精準度是否仍可用
——————————————-12345678911234567892123456789312345678941
SELECT dbo.GetDecimalE(‘0.00000000000000000000000000000000000000001’) [0.0000000000000000000000000000000000000000]
SELECT dbo.GetDecimalE(‘0.01234567890123456789012345678901234567890’) [0.01234567890123456789012345678901234567890]
SELECT dbo.GetDecimalE(‘0.1234567890123456789012345678901234567890’) [0.1234567890123456789012345678901234567890]
SELECT dbo.GetDecimalE(‘1.234567890123456789012345678901234567890’) [1.234567890123456789012345678901234567890]

SELECT LOG10(0.12345678901234567890123456789012345678) [ LOG10(0.12345678901234567890123456789012345678)],
LOG10(0.01234567890123456789012345678901234567) [ LOG10(0.01234567890123456789012345678901234567)],
POWER(10,LOG10(0.01234567890123456789012345678901234567)) [POWER(10,LOG10(0.01234567890123456789012345678901234567))],
POWER(10,LOG10(0.01)) [POWER(10,LOG10(0.01))]

DECLARE @s varchar(50)=’0.01234567890123456789012345678901234567890′
DECLARE @r  varchar(50)=dbo.GetDecimalE(@s)
SELECT @r,LOG10(CONVERT(DECIMAL(38,38),’0.’ + SUBSTRING(@r,3,LEN(@s)-2)))-CONVERT(int,LEFT(@r,CHARINDEX(‘,’,@r)-1))

clip_image002

38 位小數是 DECIMAL(38,38)

(1)insert into T168(col) values(0.000000000000000500119125812236306378730339741119979581)  à 解釋成 Decimal,超過最大的精確位數

(2)insert into T168(col) values(‘0.000000000000000500119125812236306378730339741119979581’)  à 從字串轉成 float,所以喪失精準度,但可以存

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: