Funkcje liczbowe w SQLite: ROUND, ABS, CEIL, FLOOR i matematyka

SQLite ma więcej matematyki, niż się wydaje

SQLite słynie z minimalizmu, ale ma pełny zestaw funkcji liczbowych: zaokrąglanie, wartość bezwzględną, zaokrąglanie w górę i w dół, potęgi, pierwiastki, logarytmy, trygonometrię i liczby losowe. Większość funkcji matematycznych dodano w SQLite 3.35 (2021), więc każda w miarę współczesna instalacja (dołączona do Pythona, Node, przodka WebSQL w twojej przeglądarce albo oficjalne CLI) ma je gotowe do użycia.

Krótka próbka na początek:

SELECT
    ROUND(3.14159, 2)   AS rounded,
    ABS(-42)            AS absolute,
    CEIL(2.1)           AS ceiling,
    FLOOR(2.9)          AS floor_val,
    POWER(2, 10)        AS two_to_ten,
    SQRT(144)           AS root;

Sześć funkcji, jeden wiersz wyników. Reszta tej strony omawia, do czego służy każda grupa funkcji i jakie pułapki warto znać.

ROUND: funkcja, której użyjesz najczęściej

ROUND(value, digits) zaokrągla do podanej liczby miejsc po przecinku. Drugi argument jest opcjonalny: jeśli go pominiesz, dostaniesz zaokrąglenie do najbliższej liczby całkowitej (ale nadal jako wartość zmiennoprzecinkową):

SELECT
    ROUND(3.14159)       AS no_digits,
    ROUND(3.14159, 2)    AS two_places,
    ROUND(3.14159, 4)    AS four_places,
    ROUND(2.5)           AS half_up,
    ROUND(-2.5)          AS half_down;

Kilka rzeczy, na które warto zwrócić uwagę:

• ROUND(3.14159) zwraca 3.0, a nie 3. Jeśli chcesz liczby całkowitej, użyj CAST(ROUND(x) AS INTEGER) albo po prostu CAST(x AS INTEGER), żeby obciąć część ułamkową.
• SQLite zaokrągla połówki „od zera”: 2.5 daje 3, a -2.5 daje -3. Niektóre bazy stosują zaokrąglanie bankowe (połówki do parzystej), SQLite tego nie robi.
• Argument digits może być ujemny: ROUND(1234.5, -2) zaokrągla do najbliższej setki i daje 1200.

W praktyce najczęściej napiszesz ROUND(price, 2) przy wyświetlaniu kwot.

ROUND a CAST: to nie to samo

Ludzie sięgają po CAST(x AS INTEGER), gdy chcą zaokrąglić, i się na tym przejeżdżają:

SELECT
    CAST(2.9 AS INTEGER)   AS cast_truncates,
    ROUND(2.9)             AS round_rounds,
    CAST(-2.9 AS INTEGER)  AS cast_negative,
    ROUND(-2.9)            AS round_negative;

CAST obcina w stronę zera: po prostu wyrzuca część ułamkową. ROUND zaokrągla do najbliższej liczby całkowitej. Dla 2.9 różnica wynosi całą jednostkę. Wybierz tę funkcję, której zachowania naprawdę potrzebujesz.

ABS, SIGN i znak liczby

ABS(x) zwraca wartość bezwzględną. SIGN(x) zwraca -1, 0 albo 1 zależnie od znaku:

SELECT
    ABS(-17)    AS abs_neg,
    ABS(17)     AS abs_pos,
    ABS(0)      AS abs_zero,
    SIGN(-17)   AS sign_neg,
    SIGN(0)     AS sign_zero,
    SIGN(42.5)  AS sign_pos;

ABS to koń pociągowy, przydatny w zapytaniach typu „jak daleko od siebie są te dwie wartości”. SIGN jest rzadszy, ale przydaje się, gdy chcesz podzielić wiersze według kierunku (obciążenie a uznanie, zysk a strata) bez jawnego CASE.

CEIL, FLOOR i TRUNC

Te funkcje dają wartości całkowite bez zaokrąglania do najbliższej. CEIL zawsze idzie w górę, FLOOR zawsze w dół, a TRUNC zawsze w stronę zera:

SELECT
    CEIL(2.1)    AS ceil_pos,
    CEIL(-2.1)   AS ceil_neg,
    FLOOR(2.9)   AS floor_pos,
    FLOOR(-2.9)  AS floor_neg,
    TRUNC(2.9)   AS trunc_pos,
    TRUNC(-2.9)  AS trunc_neg;

Uważaj na liczby ujemne. FLOOR(-2.9) to -3 (dalej od zera), ale TRUNC(-2.9) to -2 (w stronę zera). Przy liczbach ujemnych FLOOR i TRUNC dają różne wyniki, a wybór złej funkcji to klasyczny błąd o jeden.

CEILING to alias CEIL. Używaj zapisu, który lepiej ci się czyta.

Dzielenie całkowite to prawdziwa pułapka

To nie funkcja, tylko operator /, ale początkujących łapie częściej niż którakolwiek z prawdziwych funkcji matematycznych:

SELECT
    5 / 2           AS int_div,
    5 / 2.0         AS real_div,
    5.0 / 2         AS also_real,
    CAST(5 AS REAL) / 2  AS explicit_cast;

Gdy obie strony są liczbami całkowitymi, SQLite wykonuje dzielenie całkowite i obcina wynik. Gdy tylko jedna strona jest REAL, całe wyrażenie staje się rzeczywiste. Rozwiązanie: upewnij się, że co najmniej jeden argument jest zmiennoprzecinkowy, pisząc 2.0 zamiast 2 albo stosując rzutowanie.

Najbardziej boli to przy odwołaniach do kolumn: total_cents / 100 zwraca liczbę całkowitą. total_cents / 100.0 zwraca kwotę w dolarach, o którą naprawdę chodziło.

MOD i operator %

MOD(x, y) zwraca resztę z dzielenia x / y. Operator % robi to samo:

SELECT
    MOD(17, 5)   AS mod_func,
    17 % 5       AS mod_op,
    MOD(-17, 5)  AS mod_neg,
    MOD(17, 0)   AS mod_zero;

MOD(17, 5) i 17 % 5 zwracają 2. Reszta z dzielenia przez zero zwraca w SQLite NULL, a nie błąd, co jest nietypowe w porównaniu z większością języków. Jeśli ma to dla ciebie znaczenie, najpierw sprawdź dzielnik albo opakuj wywołanie w CASE WHEN y = 0 THEN ... END.

Forma funkcji i operatora są wymienne. Większość osób wybiera %, bo jest krótszy.

POWER, SQRT, EXP, LOG

Do potęg i pierwiastków:

SELECT
    POWER(2, 10)    AS two_to_the_ten,
    POW(2, 0.5)     AS sqrt_via_power,
    SQRT(2)         AS sqrt_two,
    EXP(1)          AS e,
    LOG(100)        AS log10_100,
    LN(EXP(1))      AS ln_of_e;

Kilka uwag, na które ludzie się łapią:

• POW to alias POWER.
• LOG(x) w SQLite ma podstawę 10. LN(x) to logarytm naturalny. LOG(b, x) z dwoma argumentami to logarytm o podstawie b. (To różni się od wielu języków, w których log to logarytm naturalny: wygrała konwencja SQL.)
• SQRT z liczby ujemnej zwraca NULL, a nie błąd.
• POWER(0, 0) zwraca umownie 1.

Przydają się przy procencie składanym, przeliczaniu na decybele, liczeniu odległości, wszędzie tam, gdzie pojawia się matematyka geometryczna lub wykładnicza.

RANDOM i RANDOMBLOB

RANDOM() zwraca 64-bitową liczbę całkowitą ze znakiem, z całego jej zakresu:

SELECT
    RANDOM()                          AS raw_random,
    ABS(RANDOM()) % 100               AS zero_to_99,
    ABS(RANDOM()) % 100 + 1           AS one_to_100,
    ABS(RANDOM() / 9223372036854775807.0) AS unit_interval;

Żeby dostać liczbę z przedziału, opakuj wynik w ABS (bo RANDOM() ma znak) i użyj %. Żeby dostać liczbę rzeczywistą między 0 a 1, podziel przez największą 64-bitową liczbę całkowitą. SQLite nie ma wbudowanego RAND() zwracającego wartość od 0 do 1: budujesz ją sam.

RANDOMBLOB(n) zwraca n bajtów losowych danych, przydatnych do generowania tokenów sesji albo danych testowych. Połącz z HEX(), żeby dostać napis nadający się do wyświetlenia:

SELECT HEX(RANDOMBLOB(8)) AS token;

Każde wywołanie daje nową wartość. Nie oczekuj, że RANDOM() zwróci dwa razy tę samą liczbę w tym samym wierszu: nawet w obrębie jednego wyrażenia każde wywołanie jest niezależne.

Wszystko razem

Mały przykład: obliczanie wartości i zaokrąglanie cen w tabeli produktów.

CREATE TABLE products (id INTEGER, name TEXT, price_cents INTEGER, weight_g REAL);
INSERT INTO products VALUES
    (1, 'Kubek',    1299, 350.5),
    (2, 'Zeszyt',    850, 180.2),
    (3, 'Długopis',  199,  12.0);

SELECT
    name,
    ROUND(price_cents / 100.0, 2)  AS price_dollars,
    CEIL(weight_g / 100)           AS weight_buckets_100g,
    POWER(weight_g, 0.5)           AS sqrt_weight
FROM products;

Kluczowy fragment to price_cents / 100.0: to .0 sprawia, że dzielenie jest rzeczywiste, a potem ROUND formatuje wynik do dwóch miejsc po przecinku. Bez tego 1299 / 100 dałoby 12, a nie 12.99.

Dalej: data i czas

Funkcje liczbowe zajmują się matematyką. Daty i godziny potrzebują własnego zestawu narzędzi: SQLite przechowuje je jako tekst, liczbę rzeczywistą albo całkowitą i daje niewielki, ale sprawny zestaw funkcji do ich parsowania, formatowania i obliczeń. O tym w następnej części.