Fonctions numériques SQLite : ROUND, ABS, CEIL, FLOOR

SQLite cache plus de maths qu'on ne le pense

SQLite a la réputation d'être minimaliste, et pourtant il embarque une panoplie complète de fonctions numériques : arrondi, valeur absolue, plafond et plancher, puissances, racines, logarithmes, trigonométrie, nombres aléatoires. La plupart de ces fonctions mathématiques SQLite ont été ajoutées avec la version 3.35 (2021), donc toute installation un peu récente — celle livrée avec Python, Node, l'ancêtre WebSQL de votre navigateur, ou la CLI officielle — les a déjà sous le coude.

Voici un petit aperçu avant d'entrer dans le vif du sujet :

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;

Six fonctions, une seule ligne de résultats. Le reste de cette page détaille à quoi sert chaque famille et les pièges qui valent le coup d'être connus.

ROUND : celle que vous utiliserez le plus

ROUND(valeur, décimales) arrondit à un nombre donné de décimales. Le second argument est optionnel — sans lui, vous obtenez un arrondi à l'entier le plus proche (mais toujours sous forme de nombre à virgule flottante) :

SELECT
    ROUND(3.14159)       AS sans_decimales,
    ROUND(3.14159, 2)    AS deux_decimales,
    ROUND(3.14159, 4)    AS quatre_decimales,
    ROUND(2.5)           AS arrondi_sup,
    ROUND(-2.5)          AS arrondi_inf;

Quelques points à retenir :

• ROUND(3.14159) renvoie 3.0, pas 3. Pour obtenir un entier, utilisez CAST(ROUND(x) AS INTEGER), ou simplement CAST(x AS INTEGER) pour tronquer.
• SQLite applique la règle « arrondi au plus loin de zéro » : 2.5 devient 3, et -2.5 devient -3. Certaines bases utilisent l'arrondi bancaire (vers le pair le plus proche), mais pas SQLite.
• L'argument digits accepte des valeurs négatives : ROUND(1234.5, -2) arrondit à la centaine la plus proche et renvoie 1200.

En pratique, c'est surtout ROUND(price, 2) que vous écrirez, pour afficher des montants.

ROUND ou CAST : attention, ce n'est pas pareil

Beaucoup de gens dégainent CAST(x AS INTEGER) en pensant arrondir, et se font avoir :

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 tronque vers zéro — la partie fractionnaire est tout simplement jetée. ROUND, lui, arrondit à l'entier le plus proche. Pour 2.9, l'écart entre les deux est d'une unité entière. À vous de choisir celui qui correspond vraiment au comportement attendu.

ABS, SIGN et le signe d'un nombre

ABS(x) renvoie la valeur absolue. SIGN(x) renvoie -1, 0 ou 1 selon le signe du nombre :

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 est le couteau suisse — pratique pour les requêtes du genre « quel est l'écart entre ces deux valeurs ? ». SIGN est moins utilisée, mais bien utile quand on veut regrouper des lignes selon leur signe (débit vs crédit, gain vs perte) sans devoir écrire un CASE explicite.

SQLite CEIL, FLOOR et TRUNC

Ces fonctions renvoient des valeurs entières, mais sans arrondir au plus proche. CEIL arrondit toujours vers le haut, FLOOR toujours vers le bas, et TRUNC toujours vers zéro :

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;

Attention aux cas négatifs. FLOOR(-2.9) renvoie -3 (on s'éloigne de zéro), alors que TRUNC(-2.9) donne -2 (on se rapproche de zéro). Sur les nombres négatifs, FLOOR et TRUNC ne sont pas d'accord, et choisir le mauvais des deux est une source classique de bug d'écart d'une unité.

CEILING est un alias de CEIL. Utilisez l'orthographe que vous préférez.

Le vrai piège : la division entière en SQLite

Ce n'est pas une fonction à proprement parler — c'est l'opérateur / — mais c'est ce qui fait trébucher les débutants bien plus que toutes les fonctions mathématiques réunies :

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;

Quand les deux opérandes sont des entiers, SQLite effectue une division entière et tronque le résultat. Dès qu'un côté est un REAL, toute l'expression bascule en réel. La parade : s'assurer qu'au moins un opérande soit un flottant — soit en écrivant 2.0 au lieu de 2, soit en utilisant un cast.

Le piège est particulièrement vicieux avec les références de colonnes : total_cents / 100 renvoie un entier, alors que total_cents / 100.0 te donne bien le montant en euros que vous attendiez.

MOD et l'opérateur %

MOD(x, y) renvoie le reste de la division x / y. L'opérateur % fait exactement la même chose :

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) et 17 % 5 renvoient tous les deux 2. À noter : le modulo par zéro retourne NULL dans SQLite — pas d'erreur levée, ce qui détonne par rapport à la plupart des langages. Si ce comportement vous pose souci, vérifiez le diviseur en amont ou enveloppez l'appel dans un CASE WHEN y = 0 THEN ... END.

La fonction et l'opérateur sont parfaitement interchangeables. En pratique, la plupart des gens utilisent % parce que c'est plus court.

POWER, SQRT, EXP, LOG : puissances et racines en SQLite

Pour les puissances et les racines carrées :

SELECT
    POWER(2, 10)    AS deux_puissance_dix,
    POW(2, 0.5)     AS racine_via_puissance,
    SQRT(2)         AS racine_de_deux,
    EXP(1)          AS e,
    LOG(100)        AS log10_de_100,
    LN(EXP(1))      AS ln_de_e;

Quelques pièges classiques à connaître :

• POW est un alias de POWER.
• En SQLite, LOG(x) est en base 10, et LN(x) correspond au logarithme népérien. La forme à deux arguments LOG(b, x) calcule le logarithme en base b. (Attention : dans beaucoup de langages, log désigne le log népérien — ici c'est la convention SQL qui l'a emporté.)
• SQRT appliqué à un nombre négatif renvoie NULL, pas une erreur.
• POWER(0, 0) renvoie 1 par convention.

Ces fonctions sont précieuses dès qu'on touche à des calculs d'intérêts composés, à une normalisation en décibels, à des distances — bref, à toute opération géométrique ou exponentielle.

RANDOM et RANDOMBLOB

RANDOM() renvoie un entier signé sur 64 bits, sur toute la plage de valeurs possibles :

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;

Pour obtenir un nombre dans une plage donnée, on enveloppe l'appel avec ABS (puisque RANDOM() renvoie un entier signé), puis on applique %. Pour obtenir un réel entre 0 et 1, il suffit de diviser par le plus grand entier 64 bits. SQLite ne fournit pas de RAND() natif qui renvoie une valeur entre 0 et 1 — c'est à vous de le construire.

RANDOMBLOB(n) renvoie n octets aléatoires, ce qui est pratique pour générer des jetons de session ou des données de test. Combinez-le avec HEX() pour obtenir une chaîne affichable :

SELECT HEX(RANDOMBLOB(8)) AS jeton;

Chaque appel renvoie une nouvelle valeur. Ne comptez pas sur RANDOM() pour produire deux fois le même nombre dans une même ligne — même au sein d'une seule expression, chaque appel est indépendant.

Mise en pratique

Voici un petit exemple concret : calculer des distances et arrondir des prix dans une table de produits.

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

Le truc important ici, c'est price_cents / 100.0 — le .0 force une division en réel, puis ROUND met en forme avec deux décimales. Sans ça, 1299 / 100 te renverrait 12, et non 12.99.

La suite : les dates et heures

Les fonctions numériques s'occupent des calculs. Les dates et heures, elles, méritent leur propre boîte à outils. SQLite les stocke en TEXT, REAL ou INTEGER, et fournit un petit ensemble de fonctions, modeste mais efficace, pour les parser, les formater et faire de l'arithmétique dessus. C'est ce qu'on verra dans la suite.