Funzioni numeriche in SQLite: ROUND, ABS, CEIL, FLOOR e matematica

SQLite ha più matematica di quanto pensi

SQLite è noto per essere minimale, ma include un set completo di funzioni numeriche: arrotondamento, valore assoluto, arrotondamento per eccesso e per difetto, potenze, radici, logaritmi, trigonometria, numeri casuali. La maggior parte delle funzioni matematiche è stata aggiunta in SQLite 3.35 (2021), quindi qualsiasi installazione ragionevolmente moderna (quella inclusa in Python, in Node, l'antenato WebSQL del tuo browser o la CLI ufficiale) le ha già pronte.

Ecco un assaggio prima di entrare nel dettaglio:

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;

Sei funzioni, una riga di risultati. Il resto della pagina spiega a cosa serve ogni famiglia di funzioni e le insidie da conoscere.

ROUND: quella che userai di più

ROUND(value, digits) arrotonda a un certo numero di cifre decimali. Il secondo argomento è facoltativo: se lo ometti, arrotondi all'intero più vicino (ma sempre come valore in virgola mobile):

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;

Alcune cose da notare:

• ROUND(3.14159) restituisce 3.0, non 3. Se vuoi un intero, usa CAST(ROUND(x) AS INTEGER), oppure semplicemente CAST(x AS INTEGER) per troncare.
• SQLite arrotonda la metà allontanandosi dallo zero: 2.5 diventa 3, -2.5 diventa -3. Alcuni database usano l'arrotondamento bancario (la metà va al pari più vicino); SQLite no.
• L'argomento digits può essere negativo: ROUND(1234.5, -2) arrotonda al centinaio più vicino e dà 1200.

In pratica scriverai ROUND(price, 2) per mostrare importi di denaro più di qualsiasi altra cosa.

ROUND e CAST: non sono la stessa cosa

Molti usano CAST(x AS INTEGER) quando in realtà vogliono arrotondare, e ci cascano:

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 tronca verso lo zero: butta semplicemente via la parte frazionaria. ROUND arrotonda all'intero più vicino. Per 2.9 la differenza è di un'unità intera. Scegli quella che fa davvero ciò che ti serve.

ABS, SIGN e il segno di un numero

ABS(x) restituisce il valore assoluto. SIGN(x) restituisce -1, 0 o 1 a seconda del segno:

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 è il cavallo da tiro, comodo per le query del tipo "quanto distano questi due valori". SIGN è meno comune, ma utile quando vuoi raggruppare le righe per direzione (addebito o accredito, guadagno o perdita) senza un CASE esplicito.

CEIL, FLOOR e TRUNC

Queste ti danno valori interi senza arrotondare al più vicino. CEIL va sempre verso l'alto, FLOOR sempre verso il basso, TRUNC sempre verso lo zero:

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;

Attenzione ai casi negativi. FLOOR(-2.9) è -3 (più lontano dallo zero), ma TRUNC(-2.9) è -2 (verso lo zero). Con i numeri negativi FLOOR e TRUNC non concordano, e scegliere quella sbagliata è un classico errore di uno.

CEILING è un alias di CEIL. Usa la grafia che ti sembra più leggibile.

La divisione intera è la vera trappola

Non è una funzione, è l'operatore /, ma mette in difficoltà i principianti più di qualsiasi funzione matematica vera e propria:

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;

Quando entrambi i lati sono interi, SQLite esegue una divisione intera e tronca. Appena uno dei due lati è un REAL, l'intera espressione diventa reale. La soluzione è assicurarsi che almeno un operando sia in virgola mobile, scrivendo 2.0 invece di 2 oppure con un cast.

Il problema morde di più con i riferimenti alle colonne: total_cents / 100 restituisce un intero. total_cents / 100.0 restituisce l'importo in dollari che volevi davvero.

MOD e l'operatore %

MOD(x, y) restituisce il resto di x / y. L'operatore % fa la stessa cosa:

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) e 17 % 5 restituiscono entrambi 2. Il modulo per zero in SQLite restituisce NULL: non solleva un errore, il che è insolito rispetto alla maggior parte dei linguaggi. Se per te conta, controlla prima il divisore o racchiudi la chiamata in CASE WHEN y = 0 THEN ... END.

La forma funzione e la forma operatore sono intercambiabili. Quasi tutti usano % perché è più breve.

POWER, SQRT, EXP, LOG

Per esponenti e radici:

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;

Alcune note che colgono molti di sorpresa:

• POW è un alias di POWER.
• In SQLite LOG(x) è in base 10. LN(x) è il logaritmo naturale. LOG(b, x) con due argomenti è il logaritmo in base b. (È diverso da molti linguaggi in cui log è il logaritmo naturale: qui ha vinto la convenzione SQL.)
• SQRT di un numero negativo restituisce NULL, non un errore.
• POWER(0, 0) restituisce 1 per convenzione.

Sono utili per l'interesse composto, per normalizzare in decibel, per calcolare distanze: ovunque compaia matematica geometrica o esponenziale.

RANDOM e RANDOMBLOB

RANDOM() restituisce un intero con segno a 64 bit, in qualsiasi punto del suo intervallo completo:

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;

Per ottenere un numero in un intervallo, avvolgi con ABS (perché RANDOM() ha il segno) e usa %. Per ottenere un numero reale tra 0 e 1, dividi per il massimo intero a 64 bit. SQLite non ha una funzione RAND() integrata che restituisca un valore tra 0 e 1: te la costruisci da solo.

RANDOMBLOB(n) restituisce n byte di dati casuali, utili per generare token di sessione o fixture di test. Combinala con HEX() per ottenere una stringa stampabile:

SELECT HEX(RANDOMBLOB(8)) AS token;

Ogni chiamata produce un valore nuovo. Non aspettarti che RANDOM() restituisca due volte lo stesso numero nella stessa riga: anche dentro un'unica espressione, ogni invocazione è indipendente.

Mettere tutto insieme

Un piccolo esempio completo: calcolare distanze e arrotondare i prezzi in una tabella di prodotti.

CREATE TABLE products (id INTEGER, name TEXT, price_cents INTEGER, weight_g REAL);
INSERT INTO products VALUES
    (1, 'Tazza',    1299, 350.5),
    (2, 'Quaderno',  850, 180.2),
    (3, 'Penna',     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;

Il punto chiave è price_cents / 100.0: quel .0 rende reale la divisione, poi ROUND la formatta a due decimali. Senza, 1299 / 100 ti darebbe 12, non 12.99.

Prossimo passo: date e orari

Le funzioni numeriche si occupano della matematica. Date e orari hanno bisogno di strumenti propri: SQLite li memorizza come testo, reale o intero, e ti offre un insieme piccolo ma capace di funzioni per analizzarli, formattarli e fare calcoli su di essi. È il prossimo argomento.