Mawonekedwe Osawoneka a Mitundu Yeniyeni, kapena Samalani ndi REAL

Pambuyo pofalitsa zolemba za mawonekedwe a kulemba mu PostgreSQL, ndemanga yoyamba inali yokhudza zovuta zogwira ntchito ndi manambala enieni. Ndinaganiza zoyang'ana mofulumira pamakina a mafunso a SQL omwe alipo kwa ine kuti ndiwone kangati amagwiritsa ntchito mtundu wa REAL. Zikuwoneka kuti zimagwiritsidwa ntchito nthawi zambiri, ndipo opanga samamvetsetsa nthawi zonse kuopsa kwake. Ndipo izi ngakhale zili ndi nkhani zambiri zabwino pa intaneti komanso pa Habré zokhudzana ndi kusunga manambala enieni pamakumbukidwe apakompyuta komanso kugwira nawo ntchito. Chifukwa chake, m'nkhaniyi ndiyesera kugwiritsa ntchito zinthu zotere ku PostgreSQL, ndipo ndiyesera kuyang'ana mwachangu zovuta zomwe zikugwirizana nazo, kuti zikhale zosavuta kuti oyambitsa mafunso a SQL apewe.

Zolemba za PostgreSQL zimanena momveka bwino kuti: "Kuwongolera zolakwika zotere komanso kufalitsa kwawo panthawi yowerengera ndi nkhani ya nthambi yonse ya masamu ndi sayansi yamakompyuta, ndipo sizinafotokozedwe apa" (pomwe amalozera owerenga mwanzeru mulingo wa IEEE 754). Ndi zolakwika zotani zomwe zikunenedwa apa? Tiyeni tikambirane mwatsatanetsatane, ndipo posachedwa zidzadziwika chifukwa chake ndinatengeranso cholembera.

Tiyeni titenge mwachitsanzo pempho losavuta:

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

Zotsatira zake, sitidzawona chilichonse chapadera - tidzapeza zomwe tikuyembekezera 0.1. Koma tsopano tiyeni tifanizire ndi 0.1:

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

Osafanana! Zozizwitsa bwanji! Koma kupitirira, zambiri. Wina anganene, ndikudziwa kuti REAL imachita moyipa ndi tizigawo tating'ono, ndiye ndilowetsamo manambala athunthu pamenepo, ndipo zonse zikhala bwino nawo. Chabwino, tiyeni tiponye nambala 123 ku REAL:

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

Ndipo zidakhala 3 zinanso! Ndizo zomwe, databaseyi idayiwala kuwerengera! Kapena kodi sitikumvetsa chinachake? Tiyeni tiganizire.

Choyamba, tiyeni tikumbukire materiel. Monga mukudziwa, nambala iliyonse ya decimal imatha kukulitsidwa kukhala mphamvu khumi. Kotero, chiwerengero cha 123.456 chidzakhala chofanana ndi 1 * 102 + 2 * 101 + 3 * 100 + 4 * 10-1 + 5 * 10-2 + ​​6 * 10-3. Koma kompyuta imagwira ntchito ndi manambala mu mawonekedwe a binary, chifukwa chake amayenera kuyimiridwa ngati kukula kwa mphamvu ziwiri. Choncho, chiwerengero cha 5.625 mu binary chikuyimiridwa ngati 101.101 ndipo chidzakhala chofanana ndi 1 * 22 + 0 * 21 + 1 * 20 + 1 * 2-1 + 0 * 2-2 + 1 * 2-3. Ndipo ngati mphamvu zabwino ziwiri nthawi zonse zimapereka manambala athunthu (1, 2, 4, 8, 16, etc.), ndiye kuti ndi zolakwika zonse zimakhala zovuta kwambiri (0.5, 0.25, 0.125, 0,0625, etc.). Vuto ndiloti Si decimal iliyonse yomwe ingaimilidwe ngati kagawo kakang'ono kakang'ono. Chifukwa chake, 0.1 yathu yodziwika bwino m'magawo a binary imawoneka ngati mtengo wanthawi zonse 0.0(0011). Chifukwa chake, mtengo womaliza wa nambala iyi mu kukumbukira kwamakompyuta udzasiyana malinga ndi kuya pang'ono.

Ino ndi nthawi yokumbukira momwe manambala enieni amasungidwira mu kukumbukira kwa kompyuta. Nthawi zambiri, nambala yeniyeni imakhala ndi magawo atatu - chizindikiro, mantissa ndi exponent. Chizindikirocho chikhoza kuphatikizira kapena kuchotsera, kotero gawo limodzi laperekedwa kwa icho. Koma kuchuluka kwa tizidutswa ta mantissa ndi exponent kumatsimikiziridwa ndi mtundu weniweni. Chifukwa chake, pamtundu WENIWENI, kutalika kwa mantissa ndi 23 bits (chimodzi chofanana ndi 1 chimawonjezedwa koyambirira kwa mantissa, ndipo zotsatira zake ndi 24), ndipo choyimira ndi 8 bits. Zonse ndi 32 bits, kapena 4 byte. Ndipo pamtundu wa DOUBLE PRECISION, kutalika kwa mantissa kudzakhala 52 bits, ndipo exponent idzakhala 11 bits, pa chiwerengero cha 64 bits, kapena 8 byte. PostgreSQL sichirikiza kulondola kwapamwamba kwa manambala oyandama.

Tiyeni tinyamule nambala yathu ya decimal 0.1 mumitundu yonse ya REAL ndi DOUBLE PRECISION. Popeza chizindikiro ndi mtengo wa exponent ndizofanana, timayang'ana kwambiri mantissa (ndikusiya dala zinthu zosadziwikiratu zosungirako zomwe zimafunikira ndi ziro zenizeni, chifukwa zimasokoneza kumvetsetsa ndikusokoneza kwenikweni. zavuto, ngati mukufuna, onani muyezo wa IEEE 754). Kodi tipeza chiyani? Pamzere wapamwamba ndipereka "mantissa" yamtundu wa REAL (potengera kuzunguliridwa komaliza ndi 1 mpaka nambala yoyimira yapafupi, apo ayi idzakhala 0.099999 ...), ndipo pansi - chifukwa mtundu wa DOUBLE PRECISION:

0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001

Mwachiwonekere awa ndi manambala awiri osiyana kotheratu! Chifukwa chake, poyerekeza, nambala yoyamba imakutidwa ndi ziro ndipo, chifukwa chake, idzakhala yayikulu kuposa yachiwiri (poganizira zozungulira - zomwe zalembedwa molimba mtima). Izi zikufotokozera kusamveka bwino kwa zitsanzo zathu. Muchitsanzo chachiwiri, nambala yotchulidwa momveka bwino 0.1 imaponyedwa ku mtundu wa DOUBLE PRECISION, ndiyeno poyerekeza ndi chiwerengero cha mtundu wa REAL. Zonsezi zimachepetsedwa kukhala zofanana, ndipo tili ndi zomwe tikuwona pamwambapa. Tiyeni tisinthe funso kuti zonse zichitike:

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

Ndipo zowonadi, pochepetsa kawiri nambala 0.1 kukhala REAL ndi DOUBLE PRECISION, timapeza yankho lamwambiwu:

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

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

Izi zikufotokozeranso chitsanzo chachitatu pamwambapa. Nambala 123 ndiyosavuta ndizosatheka kuyika mantissa mu 24 bits (23 momveka + 1 amatanthauza). Chiwerengero chochuluka chomwe chingagwirizane ndi 24 bits ndi 224-1 = 16. Choncho, nambala yathu 777 imazungulira pafupi ndi 215.

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

Ndizomwezo. Zikuoneka kuti palibe zozizwitsa. Koma zonse zomwe zafotokozedwa ndi chifukwa chabwino choganizira momwe mukufunira mtundu wa REAL. Mwina phindu lalikulu la ntchito yake ndi liwiro la kuwerengera ndi kutayika kodziwika kwa kulondola. Koma kodi ichi chingakhale chochitika chapadziko lonse chomwe chingalungamitse kugwiritsira ntchito kaŵirikaŵiri kotereku? Osaganiza.

Source: www.habr.com