Bayan bugawa game da fasalulluka na bugawa a cikin PostgreSQL, sharhi na farko shine game da matsalolin aiki tare da lambobi na gaske. Na yanke shawarar yin sauri ta hanyar lambar tambayoyin SQL da ke samuwa a gare ni don ganin sau nawa suke amfani da nau'in REAL. Ya bayyana cewa ana amfani da shi sau da yawa, kuma masu haɓakawa ba koyaushe suke fahimtar haɗarin da ke tattare da shi ba. Kuma wannan duk da cewa akwai labarai masu kyau da yawa akan Intanet da kuma kan Habré game da fasalulluka na adana lambobi na gaske a ƙwaƙwalwar kwamfuta da kuma game da aiki tare da su. Don haka, a cikin wannan labarin, zan yi ƙoƙarin yin amfani da irin waɗannan fasalulluka zuwa PostgreSQL, kuma zan yi ƙoƙarin yin saurin duba matsalolin da ke tattare da su, ta yadda masu haɓaka tambayar SQL za su sami sauƙi.
Takaddun PostgreSQL ya faɗi a taƙaice: "Gudanar da irin waɗannan kurakurai da yaduwar su yayin ƙididdigewa shine batun gabaɗayan reshe na lissafin lissafi da kimiyyar kwamfuta, kuma ba a rufe shi a nan" (yayin da yake cikin hikima yana nufin mai karatu ga ma'aunin IEEE 754). Wane irin kurakurai ake nufi anan? Bari mu tattauna su a cikin tsari, kuma nan ba da jimawa ba zai bayyana dalilin da yasa na sake daukar alkalami.
Bari mu ɗauki misali mai sauƙi:
********* ЗАПРОС *********
SELECT 0.1::REAL;
**************************
float4
--------
0.1
(1 строка)
A sakamakon haka, ba za mu ga wani abu na musamman ba - za mu sami 0.1 da ake sa ran. Amma yanzu bari mu kwatanta shi da 0.1:
********* ЗАПРОС *********
SELECT 0.1::REAL = 0.1;
**************************
?column?
----------
f
(1 строка)
Ba daidai ba! Abin al'ajabi! Amma ƙari, ƙari. Wani zai ce, Na san cewa REAL yana da mummunan hali tare da raguwa, don haka zan shigar da lambobi duka a can, kuma komai zai yi kyau tare da su. Ok, bari mu jefa lamba 123 zuwa GASKIYA:
********* ЗАПРОС *********
SELECT 123456789::REAL::INT;
**************************
int4
-----------
123456792
(1 строка)
Kuma ya zama 3 more! Shi ke nan, a karshe ma’adanar bayanai ta manta yadda ake kirgawa! Ko kuwa muna rashin fahimtar wani abu ne? Bari mu gane shi.
Da farko, bari mu tuna da kayan. Kamar yadda ka sani, kowace lamba goma za a iya faɗaɗa zuwa iko na goma. Don haka, lambar 123.456 zata kasance daidai da 1*102 + 2*101 + 3*100 + 4*10-1 + 5*10-2 + 6*10-3. Amma kwamfutar tana aiki tare da lambobi a cikin nau'i na binaryar, saboda haka dole ne a wakilta su a cikin nau'i na fadada ikon biyu. Saboda haka, lambar 5.625 a cikin binary ana wakilta a matsayin 101.101 kuma zai kasance daidai da 1 * 22 + 0 * 21 + 1 * 20 + 1 * 2-1 + 0 * 2-2 + 1 * 2-3. Kuma idan tabbataccen iko na biyu koyaushe suna ba da cikakken lambobi goma (1, 2, 4, 8, 16, da sauransu), to tare da marasa kyau komai ya fi rikitarwa (0.5, 0.25, 0.125, 0,0625, da sauransu). Matsalar ita ce Ba kowane ƙima ba ne za a iya wakilta a matsayin ƙaƙƙarfan juzu'i na binary. Don haka, sanannen 0.1 ɗinmu a cikin nau'in juzu'i na binary yana bayyana azaman ƙimar lokaci-lokaci 0.0(0011). Sakamakon haka, ƙimar ƙarshe na wannan lamba a ƙwaƙwalwar kwamfuta za ta bambanta dangane da zurfin bit.
Yanzu ne lokacin da za a tuna yadda ake adana lambobi na ainihi a ƙwaƙwalwar kwamfuta. Gabaɗaya magana, lamba ta gaske ta ƙunshi manyan sassa uku - alama, mantissa da mai magana. Alamar na iya zama ko dai ƙari ko ragi, don haka an keɓe bit guda ɗaya don shi. Amma adadin ragowa na mantissa da mai magana an ƙaddara ta ainihin nau'in. Don haka, don nau'in GASKIYA, tsayin mantissa shine 23 ragowa (biti ɗaya daidai da 1 ana ƙara shi a fakaice zuwa farkon mantissa, sakamakon shine 24), kuma ma'anar ta shine 8 rago. Jimlar ita ce 32 bits, ko 4 bytes. Kuma na nau'in PRECISION DOUBLE, tsawon mantissa zai zama 52 bits, kuma abin da ke sama zai zama 11 bits, don jimlar 64 bits, ko 8 bytes. PostgreSQL baya goyan bayan daidaito mafi girma don lambobi masu iyo.
Bari mu tattara lambar mu ta decimal 0.1 zuwa cikin nau'ikan PRECISION na GASKIYA da BIYU. Tun da alamar da darajar mai magana iri ɗaya ne, za mu mai da hankali kan mantissa (Na ƙetare abubuwan da ba a bayyane suke ba na adana ƙimar ma'auni da sifili na gaske, tunda suna dagula fahimta da shagaltuwa daga ainihin. na matsalar, idan sha'awar, duba IEEE 754 misali). Me za mu samu? A cikin layi na sama zan ba da "mantissa" don nau'in REAL (la'akari da zagaye na karshe ta hanyar 1 zuwa lambar wakilci mafi kusa, in ba haka ba zai zama 0.099999 ...), kuma a cikin layin ƙasa - don nau'in DOUBLE PRECISION:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
Babu shakka waɗannan lambobi ne mabanbanta guda biyu! Don haka, idan aka kwatanta, lambar farko za ta kasance tare da sifili kuma, don haka, za ta kasance mafi girma fiye da na biyu (la'akari da zagayawa - wanda aka yiwa alama a cikin m). Wannan yana bayyana shubuha daga misalan mu. A cikin misali na biyu, an jefa ƙayyadaddun lamba 0.1 a sarari zuwa nau'in DOUBLE PRECISION, sannan idan aka kwatanta da adadin nau'in REAL. Dukansu an rage su zuwa nau'in iri ɗaya, kuma muna da ainihin abin da muke gani a sama. Bari mu gyara tambayar ta yadda komai ya kasance:
********* ЗАПРОС *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 строка)
Kuma lalle ne, ta hanyar aiwatar da rage sau biyu na lamba 0.1 zuwa GASKIYA da DOUBLE PRECISION, muna samun amsar kaciyar:
********* ЗАПРОС *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 строка)
Wannan kuma ya bayyana misali na uku a sama. Lambar 123 mai sauki ce ba shi yiwuwa a daidaita mantissa cikin rago 24 (23 bayyane + 1 bayyana). Matsakaicin adadin da zai iya shiga cikin 24 bits shine 224-1 = 16. Saboda haka, lambar mu 777 an karkata ta zuwa mafi kusa da wakilci 215. Ta hanyar canza nau'in zuwa RASHIN PRECISION, ba mu ƙara ganin wannan yanayin:
********* ЗАПРОС *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 строка)
Shi ke nan. Sai dai itace babu mu'ujiza. Amma duk abin da aka bayyana shine dalili mai kyau don tunani game da nawa kuke buƙatar ainihin nau'in REAL. Wataƙila babbar fa'idar amfani da ita ita ce saurin ƙididdigewa tare da sanannen asarar daidaito. Amma wannan zai zama yanayin yanayi na duniya wanda zai ba da hujjar yin amfani da irin wannan akai-akai? Kar kayi tunani.
source: www.habr.com