Bardzo często podczas programowania stajemy przed dylematem zaokrąglania liczb. Potrzeby są najróżniejsze. Czasami zaokrąglamy w górę czasami w dół, czasami standardowo a czasami po prostu odcinamy część ułamkową. Może też wystąpić konieczność zaokrąglenia według zadanej dokładności. Czasami też będziemy musieli poradzić sobie z zadaniem zaokrąglenia ceny do np. 5 groszy. W MySQL możemy wszystkie te rzeczy zrobić w prosty sposób korzystając z gotowych funkcji lub wykonując odpowiednie obliczenia.
W MySQL mamy kilka funkcji zdefiniowanych do zaokrąglania liczb:
- ceil() - zaokrągla liczby ułamkowe w górę;
- floor() - zaokrągla liczby ułamkowe w dół;
- round() - zaokrągla liczby ułamkowe zgodnie z przyjętymi standardami;
- truncate() - odcina część ułamkową liczby lub zmienia do pełnych dziesiątek, setek itp.
Funkcja ceil() zaokrągla liczbę ułamkową w górę:
- SELECT CEIL(-5.1) = -5
- SELECT CEIL(5.1) = 6
- SELECT CEIL(0.1) = 1
- SELECT CEIL(-0.1) = 0
- SELECT CEIL(-5.94032) = -5
- SELECT CEIL(5.94032) = 6
- SELECT CEIL(5.5) = 6
- SELECT CEIL(liczba) FROM tabela; # wynik : zaokrąglone w górę do liczby całkowitej pole liczba
Funkcja floor() zaokrągla liczby ułamkowe w dół (i wbrew pozorom nie jest to proste odcięcie części ułamkowej - patrz liczby ujemne):
- SELECT FLOOR(-5.1) = -6
- SELECT FLOOR(5.1) = 5
- SELECT FLOOR(0.1) = 0
- SELECT FLOOR(-0.1) = -1
- SELECT FLOOR(-5.94032) = -6
- SELECT FLOOR(5.94032) = 5
- SELECT FLOOR(5.5) = 5
- SELECT FLOOR(-5.5) = -6
- SELECT FLOOR(liczba) FROM tabela; # wynik : zaokrąglone w dół do liczby całkowitej pole liczba
Funkcja round() zaokrągla liczby ułamkowe według standardowych reguł, czyli najmniej znaczącą 5 zaokrągla w górę (przy liczbach ujemnych w dół):
- SELECT ROUND(-5.1) = -5
- SELECT ROUND(5.1) = 5
- SELECT ROUND(0.1) = 0
- SELECT ROUND(-0.1) = 0
- SELECT ROUND(-5.94032) = -6
- SELECT ROUND(5.94032) = 6
- SELECT ROUND(5.5) = 6
- SELECT ROUND(-5.5) = -6
- SELECT ROUND(liczba) FROM tabela; # wynik : zaokrąglone do liczby całkowitej pole liczba
Ponadto funkcja round() posiada dodatkową opcję oznaczającą dokładność obliczeń. Dokładność oznacza ilość miejsc po przecinku, które będą uwzględniane podczas zaokrąglania, np.:
- SELECT ROUND(5.94032, 2) = 5.94
- SELECT ROUND(5.96549, 2) = 5.97
- SELECT ROUND(5.99549, 2) = 6.00
- SELECT ROUND(-5.99549, 2) = -6.00
- SELECT ROUND(liczba, 2) FROM tabela; # wynik : zaokrąglone do dwóch miejsc po przecinku pole liczba
Domyślną wartością jest 0, czyli zaokrąglanie do liczb całkowitych. Opcja dokładność ma jeszcze jedno ciekawe zastosowanie. Umożliwia zaokrąglanie liczb do pełnych dziesiątek, setek, tysięcy itd. Wystarczy wpisać jako dokładność liczbą ujemną np.:
- SELECT ROUND(593049, -1) = 593050
- SELECT ROUND(593049, -2) = 593000
- SELECT ROUND(593049, -3) = 593000
- SELECT ROUND(593049, -4) = 590000
- SELECT ROUND(593049, -5) = 600000
- SELECT ROUND(liczba, -2) FROM tabela; # wynik : zaokrąglone do setek pole liczba
Podobnie jak funkcja round() działa funkcja truncate(), z tym że nie zaokrągla ona wartości a jedynie obcina końcówkę:
- SELECT TRUNCATE(54.324, 0); # wynik : 54
- SELECT TRUNCATE(-54.324, 0); # wynik : -54
Drugi parametr tej funkcji to parametr precyzji wskazujący jaka część ułamka ma pozostać:
- SELECT TRUNCATE(54.324, 2); # wynik : 54.32
- SELECT TRUNCATE(54.324, 1); # wynik : 54.3
- SELECT TRUNCATE(-54.324, 2); # wynik : -54.32
- SELECT TRUNCATE(-54.324, 1); # wynik : -54.3
Podając parametr w postaci liczby ujemnej możemy zerować najmniej znaczące cyfry znajdujące się po lewej stronie separatora dziesiętnego (.):
- SELECT TRUNCATE(5432.4, -2); # wynik : 5400
- SELECT TRUNCATE(5432.4, -1); # wynik : 5430
- SELECT TRUNCATE(-5432.4, -2); # wynik : -5400
- SELECT TRUNCATE(-5432.4, -1); # wynik : -5430
- SELECT TRUNCATE(5432.4, -4); # wynik : 0
Istnieją też inne metody różnego rodzaju zaokrąglania liczb według potrzeb:
1. Aby odciąć część ułamkową najprościej zastosować funkcję truncate() lub zastosować odejmowanie dzielenia modulo:
- SELECT -34.549 - (-34.549%1); #wynik : -34.000
- SELECT 34.549 - (34.549%1); # wynik : 34.000
- SELECT cena - (cena%1) FROM tabela; #wynik : cena z odciętymi groszami
- SELECT TRUNCATE(cena, 0) FROM tabela; #wynik : cena z odciętymi groszami
2. Aby odciąć tylko część części ułamkowej stosujemy funkcję truncate() z parametrem precyzji lub odejmowanie dzielenia modulo z mnożeniem i dzieleniem:
- SELECT 34.549 - (((34.549*100)%1)/100); # wynik : 34.54
- SELECT 34.549 - (((34.549*10)%1)/10); # wynik : 34.5
- SELECT -34.549 - (((-34.549*100)%1)/100); # wynik : -34.54
- SELECT -34.549 - (((-34.549*10)%1)/10); # wynik : -34.5
- SELECT TRUNCATE(cena , 2) FROM tabela; # wynik : pole cena z odciętymi wartościami mniejszymi niż 1 grosz
- SELECT cena - (((cena*100)%1)/100) FROM tabela; # wynik : pole cena z odciętymi wartościami mniejszymi niż 1 grosz
3. Aby zaokrąglić w dół do wartości dziesiątych, setnych itd. stosujemy metodę dzielenia modulo, przy liczbach ujemnych odejmując dokładność:
- SELECT 593049 - (593049%10); # wynik : 593040
- SELECT 593049 - (593049%100); # wynik : 593000
- SELECT -593049 - (-593049%10) - 10; # wynik : -593050
- SELECT -593049 - (-593049%100) - 100; # wynik : -593100
- SELECT cena - (cena%100) FROM tabela; # wynik : pole cena zaokrąglone do pełnych setek w dol
4. Aby zaokrąglić w górę do wartości dziesiątych, setnych itd. również stosujemy metodę dzielenia modulo tym razem dodając dokładność do liczb dodatnich:
- SELECT 593049 - (593049%10) + 10; # wynik : 593050
- SELECT 593049 - (593049%100) + 100; # wynik : 593100
- SELECT -593049 - (-593049%10); # wynik : -593040
- SELECT -593049 - (-593049%100); # wynik : -593000
- SELECT cena - (cena%100) + 100 FROM tabela; # wynik : pole cena zaokroglone do pelnych setek w gore
5. Aby zaokąglić liczbę do np. 5 groszy (czyli 12,43 do 12,40 a 12,47 do 12,45) w dół wykonujemy:
- SELECT ROUND((12.43*100 - 12.43*100%5)/100, 2); # wynik : 12.40
- SELECT ROUND((12.47*100 - 12.47*100%5)/100, 2); # wynik : 12.45
- SELECT ROUND((cena*100 - cena*100%5)/100, 2); #wynik : pole cena zaokrąglone do 5 groszy w dół
6. Aby zaokąglić liczbę do np. 5 groszy (czyli 12,43 do 12,45 a 12,47 do 12,50) w górę wykonujemy:
- SELECT ROUND((12.43*100 - 12.43*100%5)/100 + 0.05, 2); # wynik : 12.45
- SELECT ROUND((12.47*100 - 12.47*100%5)/100 + 0.05, 2); # wynik : 12.50
- SELECT ROUND((cena*100 - cena*100%5)/100 + 0.05, 2); #wynik : pole cena zaokrąglone do 5 groszy w górę
Problem dotyczy nie tylko MySQLa ale większości języków programowania i reprezentacji liczb zmiennoprzecinkowych w pamięci komputera/interpretera/kompilatora. Liczby te nie są zapisywane tak jak nam się wydaje, czyli tak jak je widzimy (liczba 2.1 zapisywana może być jako 2.099999999999998 a nie jako 2.1). Wszystko zależy też od architektury komputera na której program jest uruchamiany. Dlatego w praktyce programowania bardzo często możemy spotkać się z błędem podliczania takich liczb i niestety trzeba się do tego przyzwyczaić czyli stosować różne zabiegi dodatkowe-zaokrąglające przy podliczaniu. Jednym z nich jest używanie ROUND, gdyż liczba 2.09999999998 zostanie zaokrąglona do 2.1. A czasami działa zwykłe przemnożenie liczby zmiennoprzecinkowej przez 1. :)
Właśnie z tego powodu w swoich programach przechowuję liczby zmiennoprzecinkowe w polach tekstowych. Nie mam problemu z obliczeniami niestety powstaje problem z wydajnością tego rozwiązania. Jednak na szczęście moc współczesnych komputerów jest już na tyle duża, że nie powoduje to żadnych problemów w standardowych rozwiązaniach.
I mały, szybki przykład na koniec:
116,85000001 x 2,0999998 = 245,384976651 = 245,38 :)
116,85 x 2,1 = 245,385 = 245,39
zapisane w bazie w polu double albo float albo decimal wartości :
cena brutto = 116,85
ilosc = 2,1
update pole
wartosc_brutto = cena_brutto * ilosc
daje wynik 116,85 * 2,1 = 245.38
kalkulator / excel
116,85 * 2,1 = 245,39
select SQL
SELECT round(SUM(116.85*2.1),2) = 245,39
prawidłowe
Skąd w takim razie update daje tak dziwną wartość ?
