Fonctions mathématiques essentielles en SQL

Leçon 3.3 · Temps de lecture : ~8 min · Exercices pratiques ↓

Dans cette leçon, vous allez apprendre les fonctions mathématiques SQL essentielles qui permettent d'effectuer des calculs et de transformer des données numériques directement dans les requêtes. Nous verrons l'arrondi, le modulo, les puissances, les racines et la comparaison de valeurs, avec des exemples pratiques sur Sakila. À la fin de la leçon, vous pourrez appliquer ces fonctions avec confiance dans des cas analytiques et métier.

Les fonctions mathématiques en SQL sont utilisées pour effectuer différents calculs sur des données numériques. Elles permettent d'arrondir des valeurs, de trouver des minimums et des maximums, de calculer des restes de division, et bien plus encore. Cette leçon présente les fonctions mathématiques les plus courantes, avec des exemples basés sur la base Sakila.

Important : les données numériques en SQL peuvent avoir des types différents (INTEGER, REAL/FLOAT, DECIMAL/NUMERIC). Une même formule peut produire des résultats différents selon le type de donnée (par exemple à cause de la division entière, de l'arrondi et de la précision de stockage). Si le type n'est pas pris en compte, le résultat peut être différent de celui attendu.

Fonctions mathématiques courantes

ABS() - Renvoie la valeur absolue d'un nombre.

Syntaxe :

ABS(number)

Exemple :

SELECT ABS(amount - 5) AS abs_difference
FROM payment
LIMIT 3;

Résultat : Renvoie la différence absolue entre amount et 5.

CEIL() / CEILING() - Arrondit un nombre vers le haut (à l'entier le plus proche).

Syntaxe :

CEIL(number)
CEILING(number)

Exemple :

SELECT CEIL(amount) AS rounded_up
FROM payment
LIMIT 3;

Résultat : Arrondit amount vers le haut à l'entier le plus proche.

FLOOR() - Arrondit un nombre vers le bas (à l'entier le plus proche).

Syntaxe :

FLOOR(number)

Exemple :

SELECT FLOOR(amount) AS rounded_down
FROM payment
LIMIT 3;

Résultat : Arrondit amount vers le bas à l'entier le plus proche.

ROUND() - Arrondit un nombre à un nombre défini de décimales.

Syntaxe :

ROUND(number, decimals)

Exemple :

SELECT ROUND(amount, 1) AS rounded_amount
FROM payment
LIMIT 3;

Résultat : Arrondit amount à une décimale.

POWER() / POW() - Élève un nombre à une puissance.

Syntaxe :

POWER(number, exponent)
POW(number, exponent)

Exemple :

SELECT POWER(amount, 2) AS squared_amount
FROM payment
LIMIT 3;

Résultat : Met amount au carré.

SQRT() - Renvoie la racine carrée d'un nombre.

Syntaxe :

SQRT(number)

Exemple :

SELECT SQRT(amount) AS sqrt_amount
FROM payment
LIMIT 3;

Résultat : Renvoie la racine carrée de amount.

PI() - Renvoie la constante mathématique pi.

Syntaxe :

PI()

Exemple :

SELECT PI() AS pi_value;

Résultat : Renvoie la valeur de pi (environ 3.141592653589793).

MOD() - Renvoie le reste d'une division.

Syntaxe :

MOD(dividend, divisor)

Exemple :

SELECT MOD(payment_id, 5) AS mod_result
FROM payment
LIMIT 3;

Résultat : Renvoie le reste de la division de payment_id par 5.

Exemple d'utilisation d'une fonction dans WHERE (trouver les valeurs paires) :

SELECT payment_id, amount
FROM payment
WHERE MOD(payment_id, 2) = 0
LIMIT 10;

Résultat : Renvoie uniquement les lignes avec un payment_id pair.

SIGN() - Renvoie le signe d'un nombre (-1, 0 ou 1).

Syntaxe :

SIGN(number)

Exemple :

SELECT SIGN(amount - 5) AS sign_value
FROM payment
LIMIT 3;

Résultat : Renvoie -1 si le résultat est négatif, 0 s'il est nul, et 1 s'il est positif.

GREATEST() - Renvoie la plus grande valeur parmi les valeurs fournies (MySQL, PostgreSQL).

Syntaxe :

GREATEST(value1, value2, ...)

Exemple :

SELECT GREATEST(amount, 5) AS max_value
FROM payment
LIMIT 3;

Résultat : Renvoie la plus grande des deux valeurs : amount ou 5.

Important (NULL) : le comportement de GREATEST() dépend du SGBD.

• En MySQL/MariaDB, si au moins un argument vaut NULL, le résultat est généralement NULL.
• En PostgreSQL, les arguments NULL sont ignorés, et NULL n'est renvoyé que si tous les arguments sont NULL.

LEAST() - Renvoie la plus petite valeur parmi les valeurs fournies (MySQL, PostgreSQL).

Syntaxe :

LEAST(value1, value2, ...)

Exemple :

SELECT LEAST(amount, 5) AS min_value
FROM payment
LIMIT 3;

Résultat : Renvoie la plus petite des deux valeurs : amount ou 5.

Important (NULL) : pour LEAST(), les mêmes différences entre SGBD s'appliquent que pour GREATEST().

Pour rendre le comportement prévisible dans des requêtes multi-SGBD, on utilise souvent COALESCE(), par exemple :

SELECT GREATEST(COALESCE(value1, 0), COALESCE(value2, 0));

RAND() - Renvoie un nombre aléatoire entre 0 et 1.

Syntaxe :

RAND()

Exemple :

SELECT RAND() AS random_value
FROM payment
LIMIT 3;

Résultat : Renvoie un nombre aléatoire entre 0 et 1.

Important : il ne faut pas supposer que RAND() sera systématiquement recalculé pour chaque ligne dans tous les contextes. Selon le SGBD, le plan d'exécution, l'utilisation de CTE/sous-requêtes et d'autres facteurs, une même valeur aléatoire peut être réutilisée pour plusieurs lignes.

Si vous avez absolument besoin de valeurs différentes par ligne, vérifiez le comportement sur votre SGBD et avec la forme exacte de la requête.

Cas d'utilisation pratiques

1. Arrondir les montants de paiement : Utilisez ROUND(amount, 0) pour arrondir les montants à l'entier.

2. Trouver des enregistrements via un reste de division : Utilisez MOD(payment_id, 2) dans WHERE (par exemple MOD(payment_id, 2) = 0) pour trouver les identifiants pairs.

3. Calculer une racine carrée : Utilisez SQRT(amount) pour analyser la distribution des paiements.

4. Comparer des valeurs : Utilisez GREATEST() et LEAST() pour choisir la valeur maximale ou minimale parmi plusieurs valeurs.

5. Contrôler le type de données : Si la précision est importante, convertissez explicitement vers le type souhaité (par exemple CAST(value AS DECIMAL(10,2))) afin d'éviter les surprises liées aux calculs entiers et à l'arrondi.

FAQ

Quelle est la différence entre ROUND(), CEIL() et FLOOR() ?

ROUND() arrondit à la valeur la plus proche (ou au nombre de décimales indiqué), CEIL() arrondit toujours vers le haut, et FLOOR() toujours vers le bas.

Pourquoi des formules proches peuvent-elles donner des résultats différents ?

La raison principale est le type de données. Les types entiers et décimaux gèrent différemment la division, l'arrondi et la précision.

Quand vaut-il mieux utiliser MOD() ?

MOD() est utile pour des contrôles périodiques, par exemple pour séparer des enregistrements en groupes ou filtrer des identifiants pairs/impairs.

Pourquoi faut-il tenir compte des différences SGBD pour GREATEST() et LEAST() ?

Parce que la gestion de NULL peut différer entre MySQL/MariaDB et PostgreSQL. Pour un comportement prévisible, on utilise souvent COALESCE().

Questions d'entretien

Comment choisir la bonne fonction d'arrondi pour une règle métier ?

On définit d'abord la règle : arrondi classique (ROUND), toujours vers le haut (CEIL) ou toujours vers le bas (FLOOR). Ensuite, on vérifie l'impact sur les indicateurs des rapports.

Quels sont les risques des calculs sans conversion explicite de type ?

On peut obtenir des résultats inattendus à cause de la division entière ou d'une perte de précision. Dans les calculs critiques, des conversions explicites comme CAST(... AS DECIMAL(...)) sont plus sûres.

Que fait SIGN() et dans quels cas est-ce utile ?

SIGN() renvoie -1, 0 ou 1 selon le signe de la valeur. C'est pratique pour classer rapidement des écarts en négatif, nul ou positif.

Pourquoi utiliser RAND() avec prudence dans des requêtes analytiques ?

Selon le SGBD et le plan d'exécution, la valeur aléatoire peut ne pas être recalculée ligne par ligne comme attendu. Il faut valider ce comportement sur votre moteur.

Points clés de cette leçon :

• Les fonctions mathématiques SQL permettent d'effectuer des calculs et des transformations directement dans la requête.
• ROUND, CEIL et FLOOR répondent à des besoins d'arrondi différents selon la règle métier.
• MOD, POWER et SQRT sont utiles pour l'analyse et les contrôles de données.
• Les types de données influencent fortement la précision et le résultat final des calculs.
• Pour les requêtes multi-SGBD, il faut prendre en compte les différences de comportement avec NULL.