SQL analytique : ordre d'exécution et fondamentaux
SQL (Structured Query Language) est le langage standard pour interroger les bases de données relationnelles. Mais dans un contexte d'analyse de données, les requetes vont bien au-dela du simple SELECT * FROM table : elles combinent agrégations, jointures multiples, sous-requetes et fonctions analytiques pour répondre a des questions métier precises sur des millions de lignes.
Les fonctions d'agrégation de base — COUNT, SUM, AVG, MIN, MAX — combinées avec GROUP BY et HAVING forment la colonne vertebrale de l'analyse. Mais avant de passer aux fonctionnalites avancées, comprendre l'ordre d'exécution logique des clauses SQL est indispensable pour ecrire des requetes correctes et les debugger efficacement.
L'ordre d'exécution logique SQL
L'ordre dans lequel vous ecrivez les clauses SQL (SELECT, FROM, WHERE...) est different de l'ordre dans lequel le moteur SQL les exécute logiquement. Comprendre cet ordre d'exécution explique pourquoi certaines erreurs surviennent et comment les éviter. Ordre logique d'exécution : (1) FROM et JOIN — on détermine l'ensemble de lignes de travail. (2) WHERE — on filtre les lignes avant toute agrégation. (3) GROUP BY — on regroupe les lignes. (4) HAVING — on filtre les groupes après agrégation. (5) SELECT — on projette les colonnes et applique les expressions. (6) DISTINCT — on deduplique. (7) ORDER BY — on trie. (8) LIMIT/OFFSET — on paginates.
Cette séquence explique pourquoi vous ne pouvez pas utiliser un alias de SELECT dans le WHERE (WHERE est evalue avant SELECT), pourquoi HAVING peut utiliser des fonctions d'agrégation mais pas WHERE, et pourquoi les window functions doivent être dans SELECT ou ORDER BY (elles s'exécutent après GROUP BY et HAVING). Une erreur classique : WHERE alias_colonne_calculee = ... — le moteur ne connait pas encore cet alias au moment du WHERE.
Types de JOIN : INNER, LEFT, SELF, LATERAL et CROSS
INNER JOIN retourne uniquement les lignes qui ont une correspondance dans les deux tables — les lignes sans correspondance sont eliminies des deux cotes. LEFT JOIN (ou LEFT OUTER JOIN) retourne toutes les lignes de la table gauche, avec NULL pour les colonnes de droite si pas de correspondance — indispensable pour identifier les enregistrements 'orphelins' (clients sans commandes, produits sans ventes).
Le SELF JOIN joint une table avec elle-même — utile pour comparer des lignes de la même table (trouver des doublons, comparer des employes avec leur manager dans la même table). Le LATERAL JOIN (ou CROSS JOIN LATERAL, appele CROSS APPLY dans SQL Server) permet a chaque ligne de la table gauche de referencer une sous-requete ou une fonction qui s'exécute séparément pour cette ligne — utile pour les TOP-N par groupe sans window function ou pour exploser des arrays. Le CROSS JOIN produit le produit cartesien de deux tables (chaque ligne de A x chaque ligne de B) — utile pour générer des combinaisons ou une grille date/client complète.
SQL dans les entrepots analytiques modernes
Les entrepots de données analytiques modernes — BigQuery, Snowflake, Amazon Redshift, Databricks SQL, ClickHouse et DuckDB — utilisent tous SQL comme interface principale, même s'ils stockent les données en format columnar (Parquet, ORC) plutot qu'en lignes. La connaissance SQL est donc largement transferable d'un outil a l'autre. Les différences concernent les extensions spécifiques : QUALIFY chez BigQuery et Snowflake, ARRAY_AGG et STRUCT chez BigQuery, PIVOT natif chez Snowflake, syntaxe des window frames.
DuckDB est un moteur SQL OLAP in-process (comme SQLite pour l'OLAP) qui permet d'interroger directement des fichiers Parquet, CSV et JSON en SQL sans serveur. C'est l'outil idéal pour l'exploration locale de datasets moyens (quelques dizaines de GB) et pour les scripts de transformation légère. Il supporte toutes les window functions, les CTE recursives et PIVOT natif.
Fonctions de fenêtre (Window Functions) : le guide complet
Les fonctions de fenêtre sont la fonctionnalite SQL la plus puissante pour l'analyse de données. Elles calculent une valeur pour chaque ligne en fonction d'un ensemble de lignes liées (la 'fenêtre'), sans réduire le nombre de lignes du résultat — contrairement a GROUP BY qui ecrase les lignes individuelles dans une agrégation.
Syntaxe complète : FONCTION() OVER (PARTITION BY col1 ORDER BY col2 ROWS BETWEEN n PRECEDING AND m FOLLOWING). La clause OVER définit la fenêtre de calcul : PARTITION BY partitionne les données en groupes indépendants (comme GROUP BY mais sans aggreger), ORDER BY ordonne les lignes dans chaque partition, et la frame clause (ROWS/RANGE BETWEEN) délimite précisément les lignes incluses dans le calcul de chaque résultat.
Fonctions de classement : ROW_NUMBER, RANK, DENSE_RANK, NTILE
ROW_NUMBER() attribue un numéro unique et séquentiel a chaque ligne dans la partition, sans tenir compte des égaux. RANK() attribue le même rang aux égaux mais créé des 'trous' numeriques (1, 1, 3 — le rang 2 n'existe pas). DENSE_RANK() attribue le même rang aux égaux sans trous (1, 1, 2). Cas d'usage typique : 'Pour chaque client, retourner uniquement sa dernière commande' — solution : ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date_commande DESC), puis filtrer WHERE rn = 1.
NTILE(n) divise les lignes d'une partition en n groupes de taille approximativement égale et attribue a chaque ligne son numéro de groupe. NTILE(4) OVER (ORDER BY revenue DESC) segmente les clients en quartiles de revenu (Q1 = top 25 %). NTILE(100) donne des percentiles. Utile pour la segmentation et les analyses de distribution sans avoir a calculer les quantiles manuellement.
LAG, LEAD, FIRST_VALUE et LAST_VALUE
LAG(colonne, n, default) renvoie la valeur de la ligne n positions avant dans la fenêtre (default si absent). LEAD(colonne, n, default) renvoie la valeur n positions après. Cas d'usage analytique classique : calculer la croissance month-over-month sans auto-jointure. SELECT mois, revenue, LAG(revenue, 1) OVER (ORDER BY mois) AS revenue_m1, (revenue - LAG(revenue, 1) OVER (ORDER BY mois)) / LAG(revenue, 1) OVER (ORDER BY mois) * 100 AS growth_pct.
FIRST_VALUE(colonne) OVER (...) retourne la valeur de la première ligne de la fenêtre selon l'ORDER BY — utile pour comparer chaque ligne avec la première valeur de la série. LAST_VALUE(colonne) OVER (...) retourne la valeur de la dernière ligne — mais attention : par defaut, la frame clause est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, ce qui fait que LAST_VALUE retourne la valeur courante et non la dernière de la partition. Pour corriger : LAST_VALUE(colonne) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING).
Agregats glissants et frame clause
Les fonctions d'agrégation deviennent des fonctions de fenêtre quand on leur ajoute OVER. SUM(revenue) OVER (PARTITION BY année ORDER BY mois) calcule un cumul mensuel par année (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW est la frame par defaut avec ORDER BY). AVG(score) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) calcule une moyenne mobile sur 7 jours glissants. COUNT(*) OVER () retourne le total de lignes de toute la table — utile pour calculer des proportions.
La distinction entre ROWS et RANGE dans la frame clause est subtile mais importante. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING inclut littéralement les 1 ligne avant et après par position. RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING inclut les lignes dont la valeur d'ORDER BY est dans [valeur-1, valeur+1] — ce qui peut inclure plus de lignes si des valeurs sont égales. Pour les calculs glissants par date ou par valeur continue, ROWS est généralement plus predictible.
Les window functions sont définies dans le standard SQL:2003 et supportées par PostgreSQL, MySQL 8+, MariaDB 10.2+, SQLite 3.25+, BigQuery, Snowflake, Redshift, DuckDB, ClickHouse et presque tous les entrepots analytiques modernes. Une syntaxe apprise dans PostgreSQL fonctionne dans BigQuery avec des variantes mineures.
SQL:2003 Standard — ISO/IEC 9075CTE : Common Table Expressions pour des requetes lisibles
Une CTE (WITH nom AS (...)) est une sous-requete nommée qui peut être referenciée plusieurs fois dans la requete principale ou dans les CTE suivantes. Elle améliore drastiquement la lisibilite et la maintenabilite des requetes analytiques complexes — c'est l'équivalent des fonctions nommées en programmation applique au SQL.
L'avantage clé sur les sous-requetes imbriquées : la CTE est définie une fois en haut, referenciée par nom, et le lecteur comprend la logique étape par étape. Une requete analytique complexe peut être décomposée en 5-6 CTE successives, chacune faisant une opération précise et clairement nommée. Les CTE peuvent se referencer les unes les autres en ordre (CTE_B peut utiliser CTE_A si CTE_A est définie avant), mais pas de référence circulaire sauf avec WITH RECURSIVE.
CTE materialisées vs inlinees
Par defaut, la plupart des moteurs SQL (PostgreSQL, BigQuery, Snowflake) considerent les CTE comme des optimisation barriers ou les materialisent — c'est-a-dire qu'ils exécutent la sous-requete une fois et stockent le résultat temporairement avant de l'utiliser. Dans PostgreSQL jusqu'a la version 11, les CTE etaient toujours materialisées (ce qui pouvait degrader les performances si le planificateur aurait voulu pousher des filtres a l'interieur). Depuis PostgreSQL 12, on peut forcer avec WITH cte AS MATERIALIZED (...) ou WITH cte AS NOT MATERIALIZED (...).
Dans les entrepots analytiques comme BigQuery ou Snowflake, les CTE sont généralement inlinees (le moteur les traite comme des sous-requetes et optimise le plan global). L'implication pratique : ne pas compter sur les CTE pour forcer une exécution séparée dans les entrepots analytiques — utiliser une table temporaire ou une vue materialisée si nécessaire.
CTE recursives : hiérarchies, graphes et séquences
Une CTE recursive (WITH RECURSIVE en PostgreSQL, WITH RECURSIVE en DuckDB, recursive nativement supporte dans BigQuery et Snowflake sans mot-clé) fait référence a elle-même pour parcourir des structures hiérarchiques : organigrammes (tous les subordonnes d'un manager a n niveaux), catégories imbriquées (arbre de catégories produits), graphes de dépendances (all downstream datasets impactes par un changement).
Structure : une partie ancre (cas de base — les racines de la hiérarchie) et une partie recursive (WITH RECURSIVE cte AS (SELECT id, parent_id, 1 AS level FROM table WHERE parent_id IS NULL UNION ALL SELECT t.id, t.parent_id, c.level + 1 FROM table t JOIN cte c ON t.parent_id = c.id)). La recursion s'arrete quand la partie recursive ne produit plus de lignes. Attention aux cycles (graphes non-acycliques) qui creeraient une recursion infinie : utiliser un ARRAY de visited ou un MAX DEPTH.
CTE = temporaire dans la requete, non stockée, scope limite a la requete courante. Sous-requete = intégrée dans le WHERE, FROM ou HAVING — moins lisible si complexe, mais parfois optimisée différemment par le planificateur. Vue = stockée en base, accessible depuis plusieurs requetes, pas de paramètre. Vue materialisée = stockée et pre-calculée, a rafraichir périodiquement. Règle : CTE pour la lisibilite d'une requete complexe ponctuelle, vue materialisée pour une agrégation couteuse souvent réutilisée.
QUALIFY, PIVOT, fonctions JSON et analytique avancée
Au-dela des fonctions de fenêtre standards, les entrepots analytiques modernes proposent des extensions SQL puissantes qui simplifient des requetes classiquement lourdes a ecrire. QUALIFY, PIVOT/UNPIVOT, les fonctions JSON et les agrégations conditionnelles font partie du repertoire avance de tout Data Analyst senior.
QUALIFY : filtrer sur les window functions sans sous-requete
QUALIFY est une clause disponible dans BigQuery et Snowflake (et DuckDB) qui filtre les lignes après évaluation des window functions — l'équivalent de HAVING pour les fonctions de fenêtre. Sans QUALIFY, filtrer sur un résultat de window function nécessite une sous-requete ou une CTE. Avec QUALIFY : SELECT *, ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date DESC) AS rn FROM commandes QUALIFY rn = 1 — retourne la dernière commande par client en une seule requete sans CTE.
QUALIFY s'exécute après SELECT et les window functions, mais avant ORDER BY — ce qui en fait l'outil le plus elegant pour les patterns 'dernier enregistrement par groupe', 'TOP-N par catégorie' ou 'filtrer sur un rang de percentile'. C'est une des questions les plus fréquentes sur les différences BigQuery/Snowflake vs PostgreSQL en entretien Data Engineering.
PIVOT, UNPIVOT et fonctions JSON
PIVOT (natif dans Snowflake, SQLServer et Oracle ; via CASE WHEN dans PostgreSQL et BigQuery) transforme des valeurs de lignes en colonnes — utile pour transformer un tableau long (une ligne par métrique par date) en tableau large (une colonne par métrique). Snowflake supporte aussi UNPIVOT pour l'opération inverse (colonnes → lignes).
Les fonctions JSON sont indispensables pour analyser des colonnes semi-structurées. Dans BigQuery : JSON_EXTRACT_SCALAR, JSON_EXTRACT_ARRAY, TO_JSON_STRING. Dans Snowflake : la colonne VARIANT avec la notation :: (SELECT col:field::string AS champ FROM table). Dans PostgreSQL : ->> pour extraire un champ JSON sous forme de texte, -> pour extraire comme JSON. ARRAY_AGG() agregre plusieurs lignes en un seul array — utile pour construire des histoires par utilisateur (tous ses événements en ordre).
Agrégations conditionnelles et FILTER
SUM(CASE WHEN statut = 'gagne' THEN montant ELSE 0 END) calcule la somme conditionnelle — utile pour pivoter plusieurs métriques en une seule requete. La syntaxe FILTER (plus lisible, disponible dans PostgreSQL et DuckDB) fait la même chose : SUM(montant) FILTER (WHERE statut = 'gagne'). COUNTIF(condition) dans BigQuery est le raccourci pour COUNT(CASE WHEN condition THEN 1 END). Ces patterns évitent de faire plusieurs sous-requetes pour calculer des métriques sur des sous-ensembles differents d'une même table.
Les window functions avec UNBOUNDED PRECEDING (cumul depuis le debut) sont généralement efficaces car le moteur les optimise en un seul passage. En revanche, plusieurs window functions avec des PARTITION BY ou ORDER BY differents dans la même requete peuvent nécessiter plusieurs sorts de la table — couteuse sur des tables de milliards de lignes. Dans ce cas, calculer les window functions dans des CTE séparées ou des tables intermédiaires peut améliorer les performances.
Optimisation SQL : EXPLAIN ANALYZE et entrepots columnar
Une requete SQL peut renvoyer le bon résultat en 50 ms ou en 5 minutes selon qu'elle utilise les index et le schema de stockage correctement. Comprendre le plan d'exécution est la competence clé pour optimiser une requete lente, qu'on soit sur PostgreSQL, BigQuery ou Snowflake.
EXPLAIN ANALYZE : lire le plan d'exécution
EXPLAIN affiche le plan d'exécution prévu sans exécuter la requete. EXPLAIN ANALYZE exécute la requete et compare les coûts estimes aux coûts réels. Les éléments critiques a surveiller : Seq Scan (lecture séquentielle de toute la table — souvent a remplacer par un Index Scan), Hash Join vs Nested Loop (le Hash Join est généralement plus efficace pour les grandes tables, le Nested Loop pour les petites), et le nombre de lignes estimées vs réelles (un écart important signale des statistiques de table perimées — a mettre a jour avec ANALYZE).
Dans BigQuery, l'équivalent est le Job Information → Exécution details qui montre les stages du plan d'exécution, le volume de données scanne par stage et les temps. Dans Snowflake, la Query Profile montre un graphe du plan avec les temps et les bytes traites par chaque operateur. La métrique clé dans BigQuery est les bytes scanned — car la facturation est a la quantité de données scannées (non en temps de compute).
Optimisation dans les entrepots columnar : partitioning et clustering
Dans les entrepots analytiques columnar (BigQuery, Snowflake, Redshift), l'index B-tree traditionnel n'existe pas. L'optimisation repose sur le partitionnement et le clustering. Dans BigQuery : partitionner par DATE ou TIMESTAMP élimine les partitions irrelevantes avant le scan (partition pruning). Le clustering (sur 1 a 4 colonnes) co-localise les données similaires dans les blocs de stockage — les requetes qui filtrent sur une colonne clusterisée scannent moins de blocs.
Dans Snowflake : le clustering key (CLUSTER BY) joue le même role. Les micro-partitions de 50-500 MB sont automatiquement triées selon le cluster key, ce qui permet au moteur d'éliminer (pruning) les micro-partitions irrelevantes. Les Search Optimization Service et les materialized views permettent d'optimiser des requetes fréquentes et couteuses sur des colonnes a faible cardinalite.
Anti-patterns SQL courants a éviter
SELECT * dans une table large en entrepot columnar est particulièrement couteux : le format columnar permet de ne lire que les colonnes nécessaires, SELECT * force la lecture de toutes — ce qui peut multiplier les coûts par 10x ou 100x. Les fonctions sur les colonnes partitionnées dans le WHERE (WHERE EXTRACT(YEAR FROM date) = 2026) empêche le partition pruning — preferer WHERE date BETWEEN '2026-01-01' AND '2026-12-31'.
Les sous-requetes correlées (exécutées pour chaque ligne de la requete externe) sont souvent remplaçables par un JOIN ou une CTE avec une agrégation groupée. Les CROSS JOIN accidentels (oubli de la condition ON dans un JOIN) produisent le produit cartesien et font exploser le volume traite. Dans BigQuery, CROSS JOIN sans filtre peut générer des coûts enormes sur de grandes tables — utiliser CROSS JOIN UNNEST pour les arrays ou LATERAL pour les sous-requetes per-row.
Patterns SQL analytiques incontournables
Certains patterns SQL reviennent systématiquement dans le travail quotidien d'un Data Analyst. Les connaitre par coeur accélère considérablement la productivite et évite de reinventer des solutions a chaque fois.
Analyse de cohorte et rétention
L'analyse de cohorte groupe les utilisateurs par période d'acquisition et suit leur comportement dans le temps — c'est le pattern de rétention le plus important en analytics produit. Structure SQL : (1) CTE 'first_activity' : SELECT user_id, DATE_TRUNC('month', MIN(event_date)) AS cohort_month FROM events GROUP BY user_id. (2) CTE 'activity' : SELECT e.user_id, fa.cohort_month, DATEDIFF(month, fa.cohort_month, DATE_TRUNC('month', e.event_date)) AS months_since_first FROM events e JOIN first_activity fa ON e.user_id = fa.user_id. (3) SELECT cohort_month, months_since_first, COUNT(DISTINCT user_id) AS retained_users.
Ce pattern mesure le taux de rétention, le churn et la valeur long-terme par cohorte d'acquisition. Diviser retained_users par le COUNT de la cohorte au mois 0 donne le taux de rétention en pourcentage. Le pivot (QUALIFY + CASE WHEN ou PIVOT natif) transforme ce tableau long en triangle de cohorte lisible.
Analyse de funnel (entonnoir de conversion)
Le funnel analysis mesure le taux de completion de chaque étape d'un parcours utilisateur (inscription → activation → premier achat → rétention). Pattern SQL : COUNT(DISTINCT CASE WHEN event = 'inscription' THEN user_id END) AS step1, COUNT(DISTINCT CASE WHEN event = 'activation' THEN user_id END) AS step2, COUNT(DISTINCT CASE WHEN event = 'premier_achat' THEN user_id END) AS step3. Le taux de conversion entre étapes = step2 / step1.
Pour un funnel ordonne (l'utilisateur doit avoir fait l'étape N avant l'étape N+1 dans un certain délai), on utilise window functions : LAG pour vérifier que l'étape précédente a eu lieu avant, et un filtre sur la date_delta. Snowflake et BigQuery proposent des MATCH_RECOGNIZE ou des fonctions natives pour les funnels ordonnées avec contraintes temporelles.
Deduplication et dernière version d'un enregistrement
La deduplication est l'un des besoins les plus fréquents en Data Engineering : extraire une seule ligne par entité (dernier état d'un client, dernière transaction par commande). Pattern standard : WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY id_entite ORDER BY updated_at DESC) AS rn FROM table) SELECT * FROM ranked WHERE rn = 1.
Avec QUALIFY (BigQuery, Snowflake, DuckDB) : SELECT * FROM table QUALIFY ROW_NUMBER() OVER (PARTITION BY id_entite ORDER BY updated_at DESC) = 1 — une ligne au lieu de 4. Pour la deduplication dans dbt, le materialization 'incremental' avec unique_key et la stratégie 'merge' gere la deduplication automatiquement a chaque run incremental.
dbt (data build tool) transforme le SQL en framework de transformation industrialise : les modèles sont des fichiers .sql versionnes dans Git, le DAG de dépendances est automatiquement détecte, les tests (unique, not_null, referential integrity, custom) s'exécutent en CI/CD, et la documentation (avec lineage) est générée automatiquement. dbt est devenu le standard de facto de la transformation SQL dans les data stacks modernes.
Ancrer le SQL analytique avec la répétition espacée
SQL est une competence pratique qui s'acquiert par la répétition des patterns. La lecture passive d'un guide SQL ne suffit pas : il faut ecrire les requetes, se confronter aux erreurs (mauvais ordre d'exécution, LAST_VALUE avec la mauvaise frame clause) et restituer les syntaxes de mémoire. Les flashcards accelerent cet ancrage en forçant le rappel actif des syntaxes et des distinctions.
Les cartes les plus utiles pour les entretiens Data Analyst et Data Engineer : la syntaxe complète d'une window function (OVER, PARTITION BY, ORDER BY, frame clause), RANK vs DENSE_RANK vs ROW_NUMBER (avec l'exemple des égaux), QUALIFY et son avantage vs CTE, ROWS vs RANGE dans la frame clause, et les 3 anti-patterns d'optimisation columnar.
Priorités pour les entretiens : (1) Syntaxe OVER (PARTITION BY ... ORDER BY ...) et ce que chaque clause fait. (2) Différence ROW_NUMBER / RANK / DENSE_RANK avec des égaux. (3) LAG(col, 1) vs LEAD(col, 1) avec valeur par defaut. (4) LAST_VALUE avec la frame UNBOUNDED FOLLOWING. (5) QUALIFY vs CTE pour filtrer sur une window function. (6) Partition pruning dans BigQuery/Snowflake et pourquoi EXTRACT(YEAR FROM date) l'empecche.
Approfondir le cluster Data & IA
Questions fréquentes sur SQL et l'analyse de données
Quelle est la différence entre GROUP BY et une window function ?
GROUP BY réduit le nombre de lignes du résultat en aggregant : 100 commandes → 12 lignes (une par mois). Une window function (OVER) calcule une valeur pour chaque ligne en tenant compte d'autres lignes de la fenêtre, sans réduire le résultat : 100 commandes → 100 lignes, chacune avec son cumul mensuel. GROUP BY efface les lignes individuelles ; les window functions les conservent.
Quelle est la différence entre RANK, DENSE_RANK et ROW_NUMBER ?
Les trois classent les lignes dans une fenêtre. ROW_NUMBER() attribue un numéro unique séquentiel même pour des valeurs égales (1, 2, 3, 4). RANK() attribue le même rang aux égaux avec des trous (1, 1, 3 — le rang 2 est sauté). DENSE_RANK() attribue le même rang aux égaux sans trous (1, 1, 2). Pour extraire une seule ligne par groupe (dernier achat, premier événement), ROW_NUMBER est le bon choix.
A quoi servent LAG et LEAD ?
LAG(col, n, default) renvoie la valeur de la ligne n positions precedentes dans la fenêtre. LEAD(col, n, default) renvoie la valeur n positions suivantes. Usage typique : calculer la croissance month-over-month sans auto-jointure — revenue - LAG(revenue, 1) OVER (ORDER BY mois). Le paramètre default évite les NULL pour la première ou dernière ligne de la série.
Qu'est-ce qu'une CTE et pourquoi l'utiliser ?
Une CTE (Common Table Expression) est une sous-requete nommée définie avec WITH nom AS (...) avant la requete principale. Elle améliore la lisibilite en decomposant une requete complexe en étapes nommées et logiques, peut être referenciée plusieurs fois, et permet les CTE recursives (WITH RECURSIVE) pour les hiérarchies. Alternative aux sous-requetes imbriquées illisibles.
Qu'est-ce que QUALIFY dans BigQuery et Snowflake ?
QUALIFY est une clause qui filtre les lignes après évaluation des window functions — l'équivalent de HAVING pour les window functions. Sans QUALIFY : on doit encapsuler dans une CTE pour filtrer sur le résultat d'une window function. Avec QUALIFY : SELECT *, ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date DESC) AS rn FROM commandes QUALIFY rn = 1. Disponible dans BigQuery, Snowflake et DuckDB.
Comment optimiser une requete SQL lente ?
Premier reflexe : EXPLAIN ANALYZE pour voir le plan d'exécution et identifier les Seq Scan couteux. Dans les entrepots columnar (BigQuery, Snowflake) : vérifier que les colonnes du WHERE correspondent aux colonnes de partitionnement/clustering — éviter les fonctions sur les colonnes partitionnées (EXTRACT, DATE_TRUNC) qui empêche le pruning. Remplacer SELECT * par les colonnes nécessaires. Utiliser des vues materialisées pour les agrégations fréquentes et couteuses.
Quelle est la différence entre WHERE et HAVING ?
WHERE filtre les lignes AVANT l'agrégation (GROUP BY) — c'est exécute tot dans l'ordre logique, donc plus performant. HAVING filtre les groupes APRÈS l'agrégation. WHERE revenue > 100 garde les lignes de commandes supérieures a 100. HAVING SUM(revenue) > 1000 garde uniquement les groupes dont le total depasse 1000. On ne peut pas utiliser une fonction d'agrégation dans WHERE, mais on peut dans HAVING.
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 et testable. Chaque modèle est un fichier .sql dans un DAG de dépendances. dbt ajoute : tests automatiques (unique, not_null, referential integrity, custom), documentation et lineage générés, materialisations configurables (vue, table, incremental, materialized view) et Snapshots pour le SCD Type 2. C'est l'outil standard de la transformation SQL dans les data stacks modernes.
Quel est l'ordre d'exécution logique des clauses SQL ?
FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. Cet ordre explique pourquoi on ne peut pas utiliser un alias de SELECT dans le WHERE (WHERE est evalue avant SELECT), pourquoi HAVING peut utiliser des agrégations mais pas WHERE, et pourquoi les window functions (évaluées dans SELECT) ne peuvent pas être filtrées avec HAVING — il faut une CTE ou QUALIFY.
Comment ecrire un pattern de deduplication en SQL ?
Pattern standard avec CTE : WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY id_entite ORDER BY updated_at DESC) AS rn FROM table) SELECT * FROM ranked WHERE rn = 1. Avec QUALIFY (BigQuery, Snowflake, DuckDB) en une seule requete : SELECT * FROM table QUALIFY ROW_NUMBER() OVER (PARTITION BY id_entite ORDER BY updated_at DESC) = 1. Dans dbt, le materialization incremental avec unique_key gere la deduplication automatiquement.