SQL analytique : rappels essentiels
SQL (Structured Query Language) est le langage standard pour interroger les bases de donnees relationnelles. Mais dans un contexte d'analyse de donnees, les requetes vont bien au-dela du simple SELECT * FROM table : elles combinent agregations, jointures multiples, sous-requetes et fonctions analytiques pour repondre a des questions metier precises.
Les fonctions d'agregation de base — COUNT, SUM, AVG, MIN, MAX — combinees avec GROUP BY et HAVING forment la colonne vertebrale de l'analyse. Le JOIN (INNER, LEFT, RIGHT, FULL OUTER) permet de combiner plusieurs tables. La clause WHERE filtre les lignes avant l'agregation, HAVING filtre apres.
SQL vs NoSQL pour l'analyse
Les entrepots de donnees analytiques modernes (BigQuery, Snowflake, Redshift, DuckDB) utilisent tous SQL comme interface principale, meme s'ils stockent les donnees en colonnar format plutot qu'en lignes. La connaissance SQL est donc transferable d'un outil a l'autre. Les bases NoSQL (MongoDB, Cassandra) ont leurs propres langages de requete, mais SQL reste le standard analytique.
Le SQL transactionnel (OLTP) est optimise pour des lectures et ecritures rapides sur peu de lignes. Le SQL analytique (OLAP) travaille sur des millions de lignes, privilegie les lectures et les agregations. Les requetes analytiques utilisent des patterns differents : pas d'UPDATE/INSERT frequents, mais des SELECT complexes avec fenetre de calcul.
Fonctions de fenetre (Window Functions)
Les fonctions de fenetre sont la fonctionnalite SQL la plus puissante pour l'analyse de donnees. Elles calculent une valeur pour chaque ligne en fonction d'un ensemble de lignes liees (la "fenetre"), sans reduire le nombre de lignes du resultat — contrairement a GROUP BY.
Syntaxe de base : FONCTION() OVER (PARTITION BY colonne ORDER BY colonne ROWS/RANGE BETWEEN ... AND ...). La clause OVER definit la fenetre : PARTITION BY regroupe les lignes (comme GROUP BY mais sans les aggreger), ORDER BY ordonne dans la partition, et la frame clause delimite les lignes incluses dans le calcul.
Fonctions de classement : ROW_NUMBER, RANK, DENSE_RANK
ROW_NUMBER() attribue un numero unique a chaque ligne dans la partition. RANK() attribue le meme rang aux egaux, mais cree des "trous" (1, 1, 3). DENSE_RANK() attribue le meme rang aux egaux sans trous (1, 1, 2). Cas d'usage typique : "pour chaque client, donner le TOP 1 achat par montant" — solution : ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY montant DESC) et filtrer WHERE rn = 1.
LAG et LEAD : comparer avec la periode precedente
LAG(colonne, n) renvoie la valeur de la ligne n positions avant dans la fenetre. LEAD(colonne, n) renvoie la valeur n positions apres. Cas d'usage analytique classique : calculer la croissance mensuelle. SELECT mois, revenue, LAG(revenue, 1) OVER (ORDER BY mois) AS revenue_precedent, revenue - LAG(revenue, 1) OVER (ORDER BY mois) AS variation. Plus besoin de jointure sur la meme table avec un decalage de date.
Agregats glissants : SUM, AVG, COUNT avec OVER
Les fonctions d'agregation deviennent des fonctions de fenetre quand on leur ajoute OVER. SUM(revenue) OVER (PARTITION BY annee ORDER BY mois) calcule un cumul mensuel par annee. AVG(score) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) calcule une moyenne mobile sur 7 jours. Ces calculs etaient autrefois impossibles sans self-join ou code applicatif.
Les window functions sont definies dans le standard SQL:2003 et supportees par PostgreSQL, MySQL 8+, MariaDB 10.2+, SQLite 3.25+, BigQuery, Snowflake, Redshift, DuckDB et presque tous les entrepots analytiques modernes. Elles sont disponibles partout ou vous travaillez.
SQL:2003 Standard — ISO/IEC 9075CTE : Common Table Expressions pour des requetes lisibles
Une CTE (WITH ... AS (...)) est une sous-requete nommee qui peut etre referenciee plusieurs fois dans la requete principale. Elle ameliore drastiquement la lisibilite et la maintenabilite des requetes analytiques complexes.
Avantage cle sur les sous-requetes imbriquees : la CTE est definie une fois en haut et referenciee par nom, ce qui permet de chainer les etapes de transformation logiquement. Une requete analytique complexe peut etre decomposee en 5-6 CTE successives, chacune faisant une operation precise.
CTE recursives : hierarchies et graphes
Une CTE recursive (WITH RECURSIVE) fait reference a elle-meme pour parcourir des structures hierarchiques : organigrammes, categories imbriquees, graphes de dependances. Structure : une partie "ancre" (cas de base) et une partie recursive (WITH RECURSIVE cte AS (SELECT ... UNION ALL SELECT ... FROM cte JOIN ...)). Cas d'usage : retrouver tous les subordones d'un manager a n niveaux.
CTE = temporaire dans la requete, non stockee. Sous-requete = integree dans le WHERE ou FROM, moins lisible si complexe. Vue = stockee en base, accessible depuis plusieurs requetes. Pour la lisibilite d'une requete complexe ponctuelle : CTE. Pour reutiliser la meme logique depuis plusieurs endroits : vue.
Optimisation des requetes SQL analytiques
Une requete SQL peut renvoyer le bon resultat en 50ms ou en 5 minutes selon qu'elle utilise les index correctement. Comprendre le plan d'execution est la competence cle pour optimiser.
EXPLAIN et EXPLAIN ANALYZE
EXPLAIN affiche le plan d'execution sans executer la requete. EXPLAIN ANALYZE execute la requete et affiche les temps reels vs estimates. Les elements a surveiller : Seq Scan (lecture sequentielle de toute la table, couteux), Index Scan (lecture via index, rapide), Hash Join vs Nested Loop (strategies de jointure), et le nombre de lignes traitees a chaque etape.
Strategie d'indexation
Un index B-tree accelere les requetes sur les colonnes filtrees (WHERE) et jointes (JOIN ... ON). Regles pratiques : indexer les colonnes de filtrage frequentes, les cles etrangeres, les colonnes d'ORDER BY. Eviter les index sur des colonnes a faible cardinalite (colonnes booleennes, statuts avec peu de valeurs). Les index partiels (WHERE actif = true) sont utiles pour les tables avec un sous-ensemble souvent consulte.
Anti-patterns SQL courants
SELECT * dans une table large ramene des colonnes inutiles et surcharge le reseau. Les fonctions sur les colonnes indexees dans le WHERE (WHERE YEAR(date) = 2026) empeche l'utilisation de l'index — preferer WHERE date BETWEEN '2026-01-01' AND '2026-12-31'. Les sous-requetes correlees (re-executees pour chaque ligne de la requete principale) sont souvent remplacables par un JOIN ou une CTE.
Patterns SQL analytiques courants
Certains patterns SQL reviennent systematiquement dans le travail quotidien d'un Data Analyst. Les connaitre par coeur accelere considerablement la productivite.
Analyse de cohorte
L'analyse de cohorte groupe les utilisateurs par periode d'acquisition et suit leur comportement dans le temps. Pattern SQL : (1) CTE 'first_activity' avec MIN(date) par user. (2) CTE 'activity' qui joint chaque evenement a sa cohorte. (3) SELECT cohorte, mois_depuis_acquisition, COUNT(DISTINCT user_id). Ce pattern mesure la retention, le churn et la valeur long-terme par cohorte.
Cumuls et parts de marche
SUM(revenue) OVER (ORDER BY date) pour un cumul. SUM(revenue) / SUM(SUM(revenue)) OVER () * 100 pour la part de chaque ligne dans le total (diviser par le grand total via une fenetre sans PARTITION BY). NTILE(4) OVER (ORDER BY revenue) pour segmenter en quartiles.
dbt transforme le SQL en framework de transformation : les modeles sont des fichiers .sql, versionnes dans Git, avec tests automatiques (unique, not_null, referential integrity) et documentation generee. dbt permet d'industrialiser la logique SQL analytique avec les pratiques du genie logiciel.
Ancrer le SQL analytique avec la repetition espacee
SQL est une competence pratique : la maitrise vient de la repetition des patterns, pas de la lecture passive. Les flashcards sont particulierement efficaces pour memoriser les syntaxes (window functions, CTE recursives, EXPLAIN ANALYZE) et les distinctions subtiles (RANK vs DENSE_RANK, LAG vs LEAD, CTE vs sous-requete).
Syntaxe OVER (PARTITION BY ... ORDER BY ...), difference ROW_NUMBER / RANK / DENSE_RANK, LAG et LEAD avec decalage n, structure CTE WITH ... AS, lecture d'un EXPLAIN ANALYZE, et les 3 anti-patterns courants. Ce sont les bases de tout entretien Data Analyst ou Data Engineer.
Questions frequentes sur SQL et l'analyse de donnees
Quelle est la difference entre GROUP BY et une window function ?
GROUP BY reduit le nombre de lignes du resultat en aggregant : une ligne par groupe. Une window function (OVER) calcule une valeur pour chaque ligne en tenant compte d'autres lignes de la fenetre, sans reduire le nombre de lignes. GROUP BY = 100 commandes → 12 lignes (une par mois). Window function = 100 commandes → 100 lignes, chacune avec son cumul mensuel.
Quelle est la difference entre RANK et DENSE_RANK ?
RANK() et DENSE_RANK() attribuent toutes deux le meme rang aux egaux. La difference : avec RANK, apres deux egaux au rang 1, la prochaine valeur est au rang 3 ("trou"). Avec DENSE_RANK, la prochaine valeur est au rang 2 ("dense", sans trou). ROW_NUMBER() attribue un numero unique a chaque ligne meme si des valeurs sont identiques.
A quoi servent LAG et LEAD ?
LAG(colonne, n) renvoie la valeur de la ligne n positions precedentes dans la fenetre. LEAD(colonne, n) renvoie la valeur n positions suivantes. Usage typique : calculer la variation par rapport a la periode precedente — revenue - LAG(revenue, 1) OVER (ORDER BY mois) — sans avoir a faire une auto-jointure sur la meme table.
Qu'est-ce qu'une CTE et pourquoi l'utiliser ?
Une CTE (Common Table Expression) est une sous-requete nommee definie avec WITH nom AS (...) avant la requete principale. Elle ameliore la lisibilite en decomposant une requete complexe en etapes nommees, peut etre referenciee plusieurs fois, et permet de creer des CTE recursives pour les hierarchies. Alternative aux sous-requetes imbriquees illisibles.
Comment optimiser une requete SQL lente ?
Premier reflexe : EXPLAIN ANALYZE pour voir le plan d'execution et identifier les Seq Scan couteux. Verifier que les colonnes du WHERE et du JOIN sont indexees. Eviter les fonctions sur les colonnes indexees dans le WHERE. Remplacer SELECT * par les colonnes necessaires. Pour les entrepots analytiques (BigQuery, Snowflake), verifier le partitionnement et le clustering des tables.
Quelle est la difference entre WHERE et HAVING ?
WHERE filtre les lignes AVANT l'agregation (GROUP BY). HAVING filtre les groupes APRES l'agregation. Exemple : WHERE revenue > 100 garde les lignes de commandes sup. a 100. HAVING SUM(revenue) > 1000 garde uniquement les groupes dont le total est sup. a 1000. WHERE est plus performant car il reduit les donnees avant l'agregation.
Qu'est-ce que dbt et pourquoi l'utiliser avec SQL ?
dbt (data build tool) est un framework qui transforme le SQL analytique en code versionneable. Chaque modele est un fichier .sql, organise dans un DAG de dependances. dbt ajoute : tests automatiques (uniqueness, not_null, referential integrity), documentation generee, materialisation configurable (vue, table, incremental), et lineage visuel. C'est l'outil standard de la transformation de donnees dans les data stacks modernes.