Facile

Bonnes pratiques SQL analytique

Rappel des limites du SQL de base, et bonnes pratiques pour écrire du SQL analytique lisible et testable.

Rappel : ce que le SQL de base sait faire

SELECT, WHERE, GROUP BY, ORDER BY et les fonctions d'agrégation (SUM, COUNT, AVG) couvrent les besoins de base :

sql

SELECT region, SUM(montant) AS chiffre_affaires
FROM ventes
WHERE annee = 2024
GROUP BY region
ORDER BY chiffre_affaires DESC

Cette requête filtre, agrège, trie. C'est la base de tout reporting.

Ce que le SQL de base ne sait pas faire

Besoin	Limite du SQL de base	Solution
Numéroter le classement	`ORDER BY` trie mais ne numérote pas	RANK(), ROW_NUMBER()
Comparer au mois précédent	Pas d'accès à la ligne précédente	LAG()
Top 3 par groupe	`GROUP BY` = une ligne par groupe	CTE + RANK + WHERE
Requête lisible de 40 lignes	Sous-requêtes imbriquées illisibles	CTEs chaînées
Enrichir avec d'autres tables	`SELECT` limité à une table	JOIN

D'où l'intérêt des CTEs, window functions, LAG() et jointures.

Quelle limitation du SQL de base les window functions résolvent-elles ?

Indentation et lisibilité

Le SQL est un langage déclaratif — la lisibilité compte. Comparez :

sql

-- Illisible
SELECT region,SUM(montant) AS ca FROM ventes WHERE annee=2024 GROUP BY region HAVING SUM(montant)>10000 ORDER BY ca DESC

-- Lisible
SELECT
    region,
    SUM(montant) AS ca
FROM ventes
WHERE annee = 2024
GROUP BY region
HAVING SUM(montant) > 10000
ORDER BY ca DESC

Règles de base :

Un clause SQL par ligne : SELECT, FROM, WHERE, GROUP BY, etc.
Indentation des colonnes sous SELECT
Indentation du contenu CTE dans le bloc WITH ... AS (...)
Espaces autour des opérateurs : =, >, <, AND, OR

Toujours aliaser les colonnes calculées

sql

-- Sans alias : la colonne s'appelle "SUM(montant)" — illisible
SELECT region, SUM(montant)
FROM ventes
GROUP BY region

-- Avec alias : clair et réutilisable
SELECT region, SUM(montant) AS chiffre_affaires
FROM ventes
GROUP BY region

Un alias rend la colonne réutilisable dans ORDER BY, dans les CTEs suivantes, et dans le code Python qui récupère le résultat.

Convention de nommage

Utilisez le snake_case pour les alias : chiffre_affaires, ca_mois_precedent, evolution_pct. Évitez les abréviations cryptiques : ca est acceptable, c_a_r ne l'est pas.

Préférer les CTEs aux sous-requêtes imbriquées

Au-delà de deux niveaux d'imbrication, restructurez en CTEs :

sql

-- Difficile à suivre
SELECT * FROM (
    SELECT * FROM (
        SELECT region, SUM(montant) AS ca
        FROM ventes GROUP BY region
    ) WHERE ca > (
        SELECT AVG(ca) FROM (
            SELECT region, SUM(montant) AS ca
            FROM ventes GROUP BY region
        )
    )
) ORDER BY ca DESC

-- Clair
WITH ca_par_region AS (
    SELECT region, SUM(montant) AS ca
    FROM ventes
    GROUP BY region
)
SELECT region, ca
FROM ca_par_region
WHERE ca > (SELECT AVG(ca) FROM ca_par_region)
ORDER BY ca DESC

À partir de combien de niveaux d'imbrication est-il recommandé de restructurer en CTEs ?

Commenter les CTEs complexes

Pour les CTEs dont le rôle n'est pas évident à partir du nom seul :

sql

WITH
-- Étape 1 : CA mensuel pour l'année en cours
ca_mensuel AS (
    SELECT strftime('%Y-%m', date_vente) AS mois,
           SUM(montant) AS chiffre_affaires
    FROM ventes
    WHERE strftime('%Y', date_vente) = '2024'
    GROUP BY strftime('%Y-%m', date_vente)
),
-- Étape 2 : ajout du mois précédent pour calcul d'évolution
avec_precedent AS (
    SELECT mois, chiffre_affaires,
           LAG(chiffre_affaires, 1, 0) OVER (ORDER BY mois) AS ca_precedent
    FROM ca_mensuel
)
SELECT ...

Test incrémental avec pandas

Testez chaque CTE indépendamment plutôt qu'une requête de 40 lignes d'un coup :

python

import pandas as pd
import sqlite3

conn = sqlite3.connect("ventes.db")

# Test de la première CTE seule
query_cte1 = """
SELECT region, SUM(montant) AS chiffre_affaires
FROM ventes
GROUP BY region
"""
df1 = pd.read_sql_query(query_cte1, conn)
print(df1)
# Vérifier : les régions attendues sont-elles là ?
# Les montants sont-ils cohérents ?

# Test de la deuxième CTE
query_cte2 = """
WITH ca_par_region AS (
    SELECT region, SUM(montant) AS chiffre_affaires
    FROM ventes
    GROUP BY region
)
SELECT AVG(chiffre_affaires) AS moyenne
FROM ca_par_region
"""
df2 = pd.read_sql_query(query_cte2, conn)
print(df2)
# La moyenne est-elle plausible ?

Testez chaque étape

pd.read_sql_query(query, conn) est l'outil de débogage principal. Quand une requête retourne un résultat inattendu, isolez chaque CTE et vérifiez ses données.

Gestion des NULL

Les NULL en SQL se propagent dans les calculs arithmétiques :

sql

-- NULL + 5 = NULL
-- NULL * 100 = NULL
-- NULL > 0 = NULL (ni vrai ni faux)

Piège classique : la propagation des NULL

Une seule valeur NULL dans une chaîne de calculs rend le résultat entier NULL. Avec LAG(), le premier mois n'a pas de précédent, donc LAG() retourne NULL par défaut, et tout calcul basé dessus produit NULL.

COALESCE — remplacer NULL par une valeur neutre

COALESCE(valeur, remplacement) retourne la première valeur non-NULL :

sql

-- Si LAG retourne NULL, utiliser 0
COALESCE(LAG(chiffre_affaires) OVER (ORDER BY mois), 0) AS ca_precedent

Utilisez COALESCE quand l'absence de valeur doit être traitée comme un zéro (neutre pour l'addition et la soustraction).

CASE WHEN IS NULL — quand l'absence a un sens

sql

CASE
    WHEN ca_precedent IS NULL THEN 'Pas de données'
    WHEN ca_precedent = 0 THEN 'Pas de ventes'
    ELSE ROUND((ca - ca_precedent) * 100.0 / ca_precedent, 2)
END AS evolution_pct

Utilisez CASE WHEN IS NULL quand l'absence de données porte une information distincte de zéro.

Outil	Usage	Exemple
`COALESCE(val, 0)`	NULL → valeur neutre	LAG sans précédent → 0
`CASE WHEN IS NULL`	NULL → traitement spécifique	Afficher « N/A » ou exclure du calcul

Que retourne l'expression NULL + 5 en SQL ?

Référence : fig.add_hline()

Rappel des paramètres de fig.add_hline() pour les lignes de référence (moyenne, zéro, seuil) :

python

fig.add_hline(
    y=0,                           # Position sur l'axe Y
    line_dash="dash",              # "solid", "dash", "dot", "dashdot"
    line_color="gray",             # Couleur de la ligne
    annotation_text="Référence",   # Texte affiché
    annotation_position="top left" # Position du texte
)

Cas d'usage typiques :

Moyenne : ligne en pointillés avec annotation "Moyenne : X €"
Zéro : ligne solide pour les graphiques d'évolution (hausse/baisse)
Seuil : ligne colorée pour marquer un objectif

À retenir

Points clés

Le SQL de base (SELECT/WHERE/GROUP BY) est limité : pas de classement, pas d'accès au passé, pas de structuration
Indentez le SQL, aliasez les colonnes, commentez les CTEs complexes
Restructurez en CTEs dès que la requête dépasse deux niveaux d'imbrication
Testez chaque CTE indépendamment avec pd.read_sql_query()
NULL se propage : utilisez COALESCE pour les valeurs neutres, CASE WHEN IS NULL pour les traitements spécifiques
fig.add_hline() ajoute des lignes de référence visuelles dans Plotly