ProHoster > Блог > Kev tswj hwm > Unreal Features of Real Types, los yog ceev faj nrog REAL

Unreal Features of Real Types, los yog ceev faj nrog REAL

Tom qab tshaj tawm lus hais txog cov yam ntxwv ntawm kev ntaus ntawv hauv PostgreSQL, thawj qhov kev tawm tswv yim yog hais txog cov teeb meem ntawm kev ua haujlwm nrog cov lej tiag. Kuv txiav txim siab los saib sai ntawm cov cai ntawm SQL cov lus nug muaj rau kuv kom pom tias lawv siv hom REAL npaum li cas. Nws hloov tawm tias nws tau siv ntau zaus, thiab cov neeg tsim khoom tsis tas yuav nkag siab txog qhov txaus ntshai tom qab nws. Thiab qhov no txawm hais tias muaj ntau ntau cov ntawv zoo hauv Is Taws Nem thiab ntawm Habré txog cov yam ntxwv ntawm kev khaws cov lej tiag hauv lub computer nco thiab txog kev ua haujlwm nrog lawv. Yog li ntawd, hauv tsab xov xwm no kuv yuav sim siv cov yam ntxwv zoo li no rau PostgreSQL, thiab yuav sim ua tib zoo saib xyuas cov teeb meem cuam tshuam nrog lawv, kom nws yuav yooj yim dua rau SQL query developers kom zam lawv.

Cov ntaub ntawv PostgreSQL hais succinctly: "Kev tswj cov kev ua yuam kev thiab lawv propagation thaum lub sij hawm xam yog cov ntsiab lus ntawm tag nrho cov ceg ntawm kev ua lej thiab computer science, thiab tsis muaj nyob rau ntawm no" (thaum ua tib zoo xa tus nyeem ntawv mus rau IEEE 754 tus qauv). Cov kev ua yuam kev zoo li cas ntawm no? Cia peb tham txog lawv nyob rau hauv kev txiav txim, thiab nws yuav sai sai no paub meej tias yog vim li cas kuv thiaj li coj cwj mem dua.

Cia peb ua piv txwv ib qho kev thov yooj yim:

********* ЗАПРОС *********
SELECT 0.1::REAL;
**************************
float4
--------
    0.1
(1 строка)

Yog li ntawd, peb yuav tsis pom dab tsi tshwj xeeb - peb yuav tau txais qhov kev cia siab 0.1. Tab sis tam sim no cia peb piv nws rau 0.1:

********* ЗАПРОС *********
SELECT 0.1::REAL = 0.1;
**************************
?column?
----------
f
(1 строка)

Tsis sib npaug! Qhov txuj ci tseem ceeb! Tab sis ntxiv, ntxiv. Ib tug neeg yuav hais tias, Kuv paub tias REAL coj tus cwj pwm tsis zoo nrog cov feem, yog li kuv yuav sau tag nrho cov lej nyob ntawd, thiab txhua yam yuav zoo nrog lawv. Ok, cia peb pov tus lej 123 rau REAL:

********* ЗАПРОС *********
SELECT 123456789::REAL::INT;
**************************
   int4   
-----------
123456792
(1 строка)

Thiab nws tau hloov mus ua 3 ntxiv! Qhov ntawd yog nws, lub database thaum kawg tsis nco qab yuav suav li cas! Los yog peb to taub yuam kev? Cia peb xav txog nws.

Ua ntej, cia peb nco txog cov khoom siv. Raws li koj paub, txhua tus lej lej tuaj yeem nthuav dav rau hauv lub zog ntawm kaum. Yog li, tus lej 123.456 yuav muab sib npaug rau 1 * 102 + 2 * 101 + 3 * 100 + 4 * 10-1 + 5 * 10-2 + ​​6 * 10-3. Tab sis lub khoos phis tawj ua haujlwm nrog cov lej hauv daim ntawv binary, yog li lawv yuav tsum tau sawv cev rau hauv daim ntawv nthuav dav hauv lub zog ntawm ob. Yog li, tus lej 5.625 hauv binary yog sawv cev li 101.101 thiab yuav muab sib npaug rau 1 * 22 + 0 * 21 + 1 * 20 + 1 * 2-1 + 0 * 2-2 + 1 * 2-3. Thiab yog tias lub zog zoo ntawm ob ib txwm muab cov lej lej tag nrho (1, 2, 4, 8, 16, thiab lwm yam), ces nrog qhov tsis zoo txhua yam yog qhov nyuaj (0.5, 0.25, 0.125, 0,0625, thiab lwm yam). Qhov teeb meem yog qhov ntawd Tsis yog txhua tus lej tuaj yeem sawv cev ua tus lej binary finite. Yog li, peb qhov tsis zoo 0.1 nyob rau hauv daim ntawv ntawm binary feem tshwm sim raws li lub sij hawm tus nqi 0.0(0011). Yog li ntawd, tus nqi kawg ntawm tus lej no hauv lub computer nco yuav txawv nyob ntawm qhov tob me ntsis.

Tam sim no yog lub sijhawm kom nco ntsoov tias cov lej tiag tiag khaws cia hauv lub computer nco li cas. Feem ntau hais lus, tus lej tiag muaj peb qhov tseem ceeb - kos npe, mantissa thiab exponent. Cov kos npe tuaj yeem yog ntxiv lossis rho tawm, yog li ib qho me ntsis faib rau nws. Tab sis tus naj npawb ntawm cov khoom ntawm mantissa thiab exponent yog txiav txim los ntawm hom tiag tiag. Yog li, rau hom REAL, qhov ntev ntawm mantissa yog 23 khoom (ib me ntsis sib npaug rau 1 yog implicitly ntxiv rau qhov pib ntawm mantissa, thiab qhov tshwm sim yog 24), thiab tus exponent yog 8 khoom. Tag nrho yog 32 ntsis, lossis 4 bytes. Thiab rau hom DOUBLE PRECISION, qhov ntev ntawm mantissa yuav yog 52 me ntsis, thiab tus exponent yuav yog 11 khoom, rau tag nrho ntawm 64 khoom, lossis 8 bytes. PostgreSQL tsis txhawb siab dua rau cov lej taw tes ntab.

Cia peb ntim peb tus lej lej 0.1 rau hauv ob qho tib si REAL thiab DOUBLE PRECISION hom. Txij li thaum kos npe thiab tus nqi ntawm tus exponent yog tib yam, peb yuav tsom mus rau mantissa (Kuv txhob txwm tshaj tawm cov yam ntxwv tsis pom tseeb ntawm kev khaws cov txiaj ntsig ntawm tus exponent thiab pes tsawg tus nqi tiag tiag, vim lawv nyuaj rau kev nkag siab thiab cuam tshuam los ntawm cov ntsiab lus. ntawm qhov teeb meem, yog xav paub, saib IEEE 754 tus qauv). Peb yuav tau dab tsi? Nyob rau hauv cov kab saum toj kawg nkaus kuv yuav muab "mantissa" rau hom REAL (siv rau hauv tus account qhov sib npaug ntawm qhov kawg me ntsis los ntawm 1 mus rau tus lej sawv cev ze tshaj plaws, txwv tsis pub nws yuav yog 0.099999 ...), thiab hauv kab hauv qab - rau hom DOUBLE PRECISION:

0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001

Obviously cov no yog ob tus lej sib txawv kiag li! Yog li ntawd, thaum sib piv, thawj tus lej yuav padded nrog xoom thiab, yog li ntawd, yuav ntau dua tus thib ob (siv rau hauv tus account rounding - ib tug cim nyob rau hauv bold). Qhov no piav qhia qhov tsis meej pem los ntawm peb cov piv txwv. Hauv qhov piv txwv thib ob, tus lej qhia meej meej 0.1 raug muab pov rau hauv DOUBLE PRECISION hom, thiab tom qab ntawd muab piv nrog ntau hom REAL. Ob leeg raug txo rau tib hom, thiab peb muaj raws nraim li peb pom saum toj no. Cia peb hloov cov lus nug kom txhua yam poob rau hauv qhov chaw:

********* ЗАПРОС *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 строка)

Thiab qhov tseeb, los ntawm kev ua kom txo tau ob npaug ntawm tus lej 0.1 rau REAL thiab DOUBLE PRECISION, peb tau txais cov lus teb rau qhov kev sib tw:

********* ЗАПРОС *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************

      float8       
-------------------
0.100000001490116
(1 строка)

Qhov no kuj piav txog qhov piv txwv thib peb saum toj no. Tus lej 123 yog qhov yooj yim Nws yog tsis yooj yim sua kom haum lub mantissa rau hauv 24 khoom (23 qhia meej + 1 implied). Tus lej siab tshaj plaws uas tuaj yeem haum rau 24 cov khoom yog 224-1 = 16. Yog li ntawd, peb tus lej 777 yog sib npaug rau qhov ze tshaj plaws sawv cev 215. Los ntawm kev hloov hom rau DOUBLE PRECISION, peb tsis pom qhov xwm txheej no lawm:

********* ЗАПРОС *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
   int4   
-----------
123456789
(1 строка)

Yog tag nrho. Nws hloov tawm tias tsis muaj txuj ci tseem ceeb. Tab sis txhua yam uas tau piav qhia yog qhov laj thawj zoo los xav txog seb koj xav tau hom REAL npaum li cas. Tej zaum qhov zoo tshaj plaws ntawm nws txoj kev siv yog qhov ceev ntawm kev suav nrog kev paub tsis raug. Tab sis qhov no puas yog qhov xwm txheej thoob ntiaj teb uas yuav ua rau pom tseeb qhov kev siv ntau zaus ntawm hom no? Tsis txhob xav.

Tau qhov twg los: www.hab.com

Ntxiv ib saib