LEÇON 3 : Fonctions et Syntaxe SQL Spécifiques - Explications Simplifiées

3.1 Fonctions de Chaînes

Le Problème avec les Chaînes de Caractères

Dans une base de données, vous manipulez souvent du texte : noms, emails, adresses. Vous devez parfois assembler des morceaux de texte, les découper, les nettoyer, ou chercher dedans. Les fonctions de chaînes sont vos outils pour cela.

Concaténation : Assembler du Texte

Concaténer signifie “coller ensemble”. Vous avez un prénom “Marie” et un nom “Dupont”, et vous voulez obtenir “Marie Dupont”.

Opérateur || (PostgreSQL) :

SELECT first_name || ' ' || last_name AS full_name
FROM customers;

L’opérateur || colle les morceaux. Si first_name = “Marie” et last_name = “Dupont”, le résultat est “Marie Dupont”. Le ' ' au milieu ajoute un espace entre les deux.

Fonction CONCAT :

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

C’est la même chose, mais avec une fonction. C’est plus lisible pour certains.

CONCAT_WS (Concat With Separator) :

SELECT CONCAT_WS(' ', title, first_name, middle_name, last_name) AS full_name
FROM customers;

Le “WS” signifie “With Separator” (avec séparateur). Le premier argument (' ') est le séparateur qui sera mis entre tous les autres éléments. Si certains champs sont NULL, ils sont ignorés automatiquement.

Exemple : Si title = “Dr”, first_name = “Marie”, middle_name = NULL, last_name = “Dupont”, le résultat sera “Dr Marie Dupont” (pas d’espace double à cause du NULL).

Extraction de Sous-Chaînes

Une sous-chaîne est un morceau d’une chaîne. Par exemple, dans “0612345678”, vous voulez extraire “06” (l’indicatif).

SUBSTRING :

SELECT SUBSTRING(phone FROM 1 FOR 3) AS area_code
FROM users;

FROM 1 signifie “commence à la position 1” (le premier caractère). FOR 3 signifie “prend 3 caractères”. Si phone = “0612345678”, le résultat est “061”.

Attention : En SQL, les positions commencent à 1, pas à 0 comme en programmation.

POSITION : Trouver un caractère :

SELECT POSITION('@' IN email) AS at_position
FROM users;

Cette fonction trouve la position du caractère ‘@’ dans l’email. Si email = “marie@example.com”, le résultat est 6 (le @ est le 6ème caractère).

SPLIT_PART : Découper avec un délimiteur :

SELECT
    SPLIT_PART(email, '@', 1) AS username,
    SPLIT_PART(email, '@', 2) AS domain
FROM users;

Cette fonction découpe la chaîne en utilisant ‘@’ comme séparateur. Le troisième argument indique quelle partie vous voulez :

• Partie 1 : ce qui est avant le ‘@’
• Partie 2 : ce qui est après le ‘@’

Si email = “marie@example.com”, vous obtenez “marie” et “example.com”.

Exemple Pratique : Nettoyage de Données

Imaginez que vous importez des données d’un fichier Excel. Les utilisateurs ont tapé n’importe comment : espaces en trop, majuscules/minuscules mélangées, etc.

CREATE TABLE cleaned_customers AS
SELECT
    TRIM(REGEXP_REPLACE(raw_name, '\s+', ' ', 'g')) AS clean_name,
    LOWER(TRIM(raw_email)) AS clean_email,
    SPLIT_PART(raw_name, ' ', 1) AS first_name,
    SPLIT_PART(raw_name, ' ', 2) AS last_name,
    REGEXP_REPLACE(raw_phone, '[^0-9]', '', 'g') AS clean_phone
FROM raw_customer_import
WHERE raw_email LIKE '%@%.%';

Décomposons ligne par ligne :

TRIM(REGEXP_REPLACE(raw_name, ‘\s+’, ’ ', ‘g’)) :

• REGEXP_REPLACE est une expression régulière qui remplace du texte
• '\s+' signifie “un ou plusieurs espaces” (y compris tabulations)
• ' ' est le remplacement : un seul espace
• 'g' signifie “global” : remplacer partout, pas juste la première occurrence
• TRIM enlève les espaces au début et à la fin

Résultat : " Marie Dupont " devient “Marie Dupont”.

LOWER(TRIM(raw_email)) :

• TRIM enlève les espaces
• LOWER met tout en minuscules

Résultat : " MARIE@EXAMPLE.COM " devient “marie@example.com”.

SPLIT_PART pour prénom/nom : Si raw_name = “Marie Dupont”, cela extrait “Marie” puis “Dupont”.

REGEXP_REPLACE(raw_phone, ‘[^0-9]’, ‘’, ‘g’) :

• '[^0-9]' signifie “tout ce qui n’est PAS un chiffre”
• '' est le remplacement : rien (suppression)

Résultat : “06 12 34 56 78” ou “06-12-34-56-78” deviennent “0612345678”.

Recherche Insensible à la Casse

Le problème : Vous cherchez “john” mais l’utilisateur s’appelle “John” avec une majuscule. La recherche normale échoue.

Solution MySQL : LIKE ignore automatiquement la casse (selon la configuration).

Solution PostgreSQL - ILIKE :

SELECT * FROM customers WHERE name ILIKE '%john%';

ILIKE est comme LIKE mais insensible à la casse. Il trouve “John”, “JOHN”, “john”, “JoHn”, etc.

Le % signifie “n’importe quels caractères”. '%john%' trouve “john” n’importe où dans le nom : “Johnson”, “Johnny”, “john smith”, etc.

Recherche Floue avec Trigrammes :

Un trigramme décompose les mots en groupes de 3 lettres. “john” devient “joh”, “ohn”. Cela permet de trouver des correspondances approximatives.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_customers_name_trgm ON customers USING GIN (name gin_trgm_ops);

Maintenant vous pouvez utiliser l’opérateur % :

SELECT * FROM customers WHERE name % 'jon doo';

Cela trouve “John Doe” même avec les fautes de frappe. C’est comme la correction automatique de Google.

---

3.2 Fonctions de Date et Heure

Obtenir la Date et l’Heure Actuelles

SELECT
    CURRENT_TIMESTAMP AS now_with_tz,
    CURRENT_DATE AS today,
    CURRENT_TIME AS time_now,
    NOW() AS now_function;

• CURRENT_TIMESTAMP et NOW() : Date et heure complètes avec fuseau horaire (ex : 2025-12-10 14:30:00+01)
• CURRENT_DATE : Juste la date (ex : 2025-12-10)
• CURRENT_TIME : Juste l’heure (ex : 14:30:00+01)

Arithmétique de Dates : Ajouter et Soustraire

Ajouter des jours, mois, années :

SELECT created_at + INTERVAL '7 days' AS in_7_days
FROM orders;

Si une commande a été créée le 10 décembre, in_7_days sera le 17 décembre. L’intervalle est une durée qu’on ajoute à une date.

Vous pouvez combiner :

SELECT created_at + INTERVAL '1 year 2 months 5 days' AS future_date
FROM orders;

Cela ajoute 1 an, puis 2 mois, puis 5 jours à la date de commande.

Soustraire :

SELECT created_at - INTERVAL '3 months' AS three_months_ago
FROM orders;

Si on est le 10 décembre, cela donne le 10 septembre.

Calculer des Différences Entre Dates

Simple soustraction :

SELECT shipped_date - order_date AS days_to_ship
FROM orders;

Si la commande est le 10 décembre et l’expédition le 15 décembre, le résultat est 5 (jours).

EXTRACT : Extraire une partie :

SELECT
    EXTRACT(DAY FROM shipped_date - order_date) AS days,
    EXTRACT(HOUR FROM shipped_date - order_date) AS hours
FROM orders;

EXTRACT permet de récupérer une partie spécifique d’une durée : jour, heure, minute, etc.

EXTRACT(EPOCH) : Convertir en secondes

SELECT EXTRACT(EPOCH FROM shipped_date - order_date) AS seconds
FROM orders;

EPOCH convertit une durée en secondes depuis l’origine Unix (1er janvier 1970). C’est utile pour des calculs précis.

AGE : Différence formatée :

SELECT AGE(shipped_date, order_date) AS age_interval
FROM orders;

Au lieu d’un simple nombre, AGE retourne un intervalle lisible comme “5 days 3 hours”.

Exemple Pratique : Analytics E-commerce

SELECT
    order_id,
    EXTRACT(EPOCH FROM shipped_date - order_date)/3600 AS processing_hours,
    EXTRACT(DAY FROM delivered_date - shipped_date) AS delivery_days,
    EXTRACT(WEEK FROM order_date) AS week_number,
    EXTRACT(QUARTER FROM order_date) AS quarter,
    DATE_TRUNC('month', order_date) AS month_start
FROM order_analytics;

Décomposons :

processing_hours : Temps entre commande et expédition, en heures. On divise par 3600 car EPOCH donne des secondes (3600 secondes = 1 heure).

delivery_days : Temps entre expédition et livraison, en jours.

week_number : Numéro de la semaine dans l’année (1 à 52).

quarter : Trimestre (1, 2, 3 ou 4). Utile pour les rapports trimestriels.

DATE_TRUNC(‘month’, …) : Tronque la date au début du mois. Si order_date = “2025-12-15”, le résultat est “2025-12-01”. Cela permet de regrouper toutes les commandes d’un même mois.

Analyse de Cohorte : Rétention Utilisateurs

Une cohorte est un groupe d’utilisateurs qui se sont inscrits le même mois. L’analyse de rétention mesure combien reviennent chaque mois suivant.

WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', created_at) AS cohort_month
    FROM users
)
SELECT
    cohort_month,
    COUNT(DISTINCT user_id) AS cohort_size,
    COUNT(DISTINCT CASE
        WHEN activity_month = cohort_month
        THEN user_id
    END) AS month_0,
    COUNT(DISTINCT CASE
        WHEN activity_month = cohort_month + INTERVAL '1 month'
        THEN user_id
    END) AS month_1
FROM user_cohorts
GROUP BY cohort_month;

user_cohorts : On identifie le mois d’inscription de chaque utilisateur avec DATE_TRUNC.

cohort_size : Nombre d’utilisateurs inscrits ce mois-là.

month_0 : Combien d’utilisateurs de cette cohorte étaient actifs le mois de leur inscription.

month_1 : Combien étaient encore actifs le mois suivant.

Si 1000 utilisateurs se sont inscrits en janvier, et que 800 étaient actifs en janvier et 600 en février, votre taux de rétention mois 1 est 60%.

---

3.3 LIMIT et OFFSET : Pagination

Pagination de Base

Imaginez une liste de 1000 produits. Vous ne voulez pas tous les afficher d’un coup. Vous créez des pages de 20 produits.

SELECT * FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

• LIMIT 20 : Affiche 20 résultats maximum
• OFFSET 40 : Saute les 40 premiers résultats

Cela vous donne les résultats 41 à 60, soit la page 3 (page 1 = 0-19, page 2 = 20-39, page 3 = 40-59).

Calcul de l’offset pour une page :

• Page 1 : OFFSET 0
• Page 2 : OFFSET 20
• Page 3 : OFFSET 40
• Formule : OFFSET = (page_number - 1) * page_size

Problème de Performance avec OFFSET

Le problème : OFFSET 1000000 force PostgreSQL à lire et ignorer 1 million de lignes avant de retourner les résultats. C’est très lent.

Solution : Pagination par Cursor

-- Première page
SELECT product_id, name, price
FROM products
WHERE is_active = TRUE
ORDER BY product_id DESC
LIMIT 20;

Vous obtenez 20 produits. Le dernier a product_id = 12345.

-- Page suivante
SELECT product_id, name, price
FROM products
WHERE is_active = TRUE AND product_id < 12345
ORDER BY product_id DESC
LIMIT 20;

Au lieu de sauter des lignes, on filtre directement avec product_id < 12345. Cela utilise l’index sur product_id et reste rapide même avec des millions de produits.

Le “cursor” (curseur) est simplement le dernier ID de la page précédente. Vous le passez entre les pages.

---

3.4 UPSERT : INSERT … ON CONFLICT

Le Problème des Doublons

Imaginez que vous importez des données de stock chaque jour. Si le produit existe déjà, vous voulez mettre à jour la quantité. S’il n’existe pas, vous voulez l’ajouter. Faire deux requêtes (vérifier puis insérer ou modifier) est lent et risqué (race conditions).

L’UPSERT fait les deux en une seule opération : “Insère, mais si ça existe déjà, mets à jour.”

PostgreSQL : ON CONFLICT

INSERT INTO product_inventory (product_id, quantity, last_updated)
VALUES (1234, 100, NOW())
ON CONFLICT (product_id)
DO UPDATE SET
    quantity = EXCLUDED.quantity,
    last_updated = EXCLUDED.last_updated;

ON CONFLICT (product_id) : Si un produit avec cet ID existe déjà…

DO UPDATE SET : …mets à jour ces colonnes.

EXCLUDED : Fait référence aux valeurs que vous essayiez d’insérer. C’est comme dire “utilise les nouvelles valeurs”.

Si le produit 1234 existe avec quantité 50, après cette requête il aura quantité 100. Si le produit 1234 n’existe pas, il est créé avec quantité 100.

Variante : Ne rien faire si existe :

INSERT INTO product_inventory (product_id, quantity, last_updated)
VALUES (1234, 100, NOW())
ON CONFLICT (product_id) DO NOTHING;

Si le produit existe, on ignore silencieusement. S’il n’existe pas, on l’ajoute. Utile pour éviter les erreurs lors d’imports.

Exemple Pratique : Synchronisation Multi-Entrepôts

CREATE TABLE warehouse_inventory (
    warehouse_id INT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    reserved_quantity INT NOT NULL DEFAULT 0,
    last_sync TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    PRIMARY KEY (warehouse_id, product_id)
);

Chaque entrepôt a son propre stock pour chaque produit. La clé primaire composite (warehouse_id, product_id) garantit qu’un produit n’apparaît qu’une fois par entrepôt.

INSERT INTO warehouse_inventory (warehouse_id, product_id, quantity, last_sync)
VALUES
    (1, 1001, 500, NOW()),
    (2, 1001, 1000, NOW())
ON CONFLICT (warehouse_id, product_id)
DO UPDATE SET
    quantity = EXCLUDED.quantity,
    last_sync = EXCLUDED.last_sync,
    reserved_quantity = warehouse_inventory.reserved_quantity;

reserved_quantity = warehouse_inventory.reserved_quantity : On ne modifie PAS les réservations lors de la mise à jour. On garde l’ancienne valeur.

Pourquoi ? Les réservations sont gérées en interne par l’application. L’import externe ne doit pas les écraser.

UPSERT Conditionnel :

INSERT INTO warehouse_inventory (warehouse_id, product_id, quantity, last_sync)
VALUES (1, 1001, 500, NOW())
ON CONFLICT (warehouse_id, product_id)
DO UPDATE SET
    quantity = EXCLUDED.quantity,
    last_sync = EXCLUDED.last_sync
WHERE warehouse_inventory.quantity != EXCLUDED.quantity;

La clause WHERE ajoute une condition : on ne met à jour que si la quantité a vraiment changé. Cela évite des écritures inutiles et améliore les performances.

UPSERT avec Contraintes Multiples

CREATE TABLE app_users (
    user_id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    display_name VARCHAR(100),
    last_login TIMESTAMP WITH TIME ZONE
);

Cette table a deux contraintes UNIQUE : email et username. Vous pouvez faire un UPSERT sur l’une ou l’autre :

INSERT INTO app_users (email, username, display_name, last_login)
VALUES ('john@example.com', 'johndoe', 'John Doe', NOW())
ON CONFLICT (email)
DO UPDATE SET
    display_name = EXCLUDED.display_name,
    last_login = EXCLUDED.last_login;

Si l’email existe déjà, on met à jour le nom d’affichage et la date de connexion. Le username n’est pas modifié.

UPSERT sur Clé Composite :

CREATE TABLE user_preferences (
    user_id BIGINT NOT NULL,
    preference_key VARCHAR(50) NOT NULL,
    preference_value TEXT,
    PRIMARY KEY (user_id, preference_key)
);

INSERT INTO user_preferences (user_id, preference_key, preference_value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, preference_key)
DO UPDATE SET preference_value = EXCLUDED.preference_value;

La clé primaire est sur deux colonnes. L’UPSERT fonctionne sur la combinaison des deux : si cet utilisateur a déjà cette préférence, on la met à jour ; sinon on la crée.

C’est parfait pour les systèmes de configuration où chaque utilisateur a plusieurs préférences (thème, langue, notifications, etc.).