Le SQL basique (SELECT, FROM, WHERE, GROUP BY) est maîtrisé par la plupart des analystes. Ce qui différencie un data analyst junior d'un senior, c'est la maîtrise du SQL avancé : window functions, CTEs récursives et optimisation des requêtes.
Window Functions : l'outil le plus puissant
Les window functions (fonctions de fenêtre) permettent de réaliser des calculs sur un ensemble de lignes relatives à la ligne courante, sans réduire le nombre de lignes dans le résultat comme le ferait un GROUP BY. C'est la compétence SQL la plus demandée par les entreprises.
- ROW_NUMBER() : numéroter les lignes dans chaque partition (ex : top N par catégorie)
- RANK() / DENSE_RANK() : classement avec gestion des ex aequo
- LAG() / LEAD() : accéder à la valeur d'une ligne précédente ou suivante
- SUM() / AVG() OVER() : calculs cumulatifs ou glissants (moving average)
- NTILE(n) : diviser en n groupes de taille égale (quartiles, déciles)
CTEs : rendre vos requêtes lisibles
Les Common Table Expressions (WITH ... AS ...) permettent de nommer des sous-requêtes et de les réutiliser. Elles transforment des requêtes illisibles de 100 lignes en code documenté et maintenable. Privilégiez-les aux sous-requêtes imbriquées.
- CTE simple : nommer une sous-requête pour la clarté et la réutilisation
- CTEs multiples : enchaîner plusieurs transformations logiquement séparées
- CTEs récursives : traverser des hiérarchies (organigrammes, catégories produit)
- Performances : dans la plupart des moteurs modernes (PostgreSQL, BigQuery), équivalent aux sous-requêtes
Agrégations avancées
Au-delà de GROUP BY simple, SQL offre des fonctionnalités d'agrégation puissantes pour l'analyse multidimensionnelle et les rapports complexes.
- GROUPING SETS : agrégations sur plusieurs combinaisons de colonnes en une requête
- ROLLUP : sous-totaux hiérarchiques (pays → région → ville)
- CUBE : toutes les combinaisons d'agrégation possibles
- FILTER (WHERE ...) : agrégations conditionnelles (ex : COUNT(CASE WHEN ...)
Optimisation des requêtes
Une requête lente coûte de l'argent et du temps. L'optimisation commence par comprendre comment la base de données exécute votre requête avec EXPLAIN ANALYZE, puis par l'identification des goulots d'étranglement.
- EXPLAIN ANALYZE : afficher le plan d'exécution et les temps réels par étape
- Index : créer des index sur les colonnes de JOIN et de WHERE fréquents
- Partitioning : diviser les grandes tables par date ou catégorie pour accélérer les scans
- Statistiques : vérifier que les statistiques de la table sont à jour (ANALYZE en PostgreSQL)
Fonctions de date et de texte
Les données réelles contiennent souvent des dates et du texte mal formatés. Maîtriser les fonctions de manipulation de date et de texte est essentiel pour le nettoyage et la préparation des données.
- Dates : DATE_TRUNC, EXTRACT, DATEDIFF, DATE_ADD — standardisés sur BigQuery, PostgreSQL
- Texte : REGEXP_REPLACE, SPLIT_PART, POSITION, TRIM, UPPER/LOWER
- Conversions : CAST, TRY_CAST (BigQuery), SAFE_CAST pour éviter les erreurs de type
- JSON : JSON_EXTRACT_PATH, JSONB_ARRAY_ELEMENTS pour les colonnes semi-structurées
Pivot et unpivot
La transformation de données longues en données larges (pivot) et inversement (unpivot) est une opération fréquente dans la préparation des rapports.
- CASE WHEN : pivot manuel flexible, lisible et portable entre moteurs SQL
- PIVOT (SQL Server, BigQuery) : syntaxe native pour les pivots avec valeurs connues
- CROSSTAB (PostgreSQL) : extension tablefunc pour les pivots dynamiques
Connaître les window functions, c'est comme passer d'un couteau à une scie électrique : vous pouvez techniquement faire les mêmes choses, mais la vitesse et la précision n'ont rien à voir.
Pratique recommandée
Refaites tous vos anciens calculs GROUP BY + sous-requêtes avec des window functions. Vous découvrirez à la fois des simplifications majeures et de meilleures performances.