Quels patterns analytiques maîtriser en data science ?

Les quatre patterns essentiels à maîtriser sont : jointures + filtres, fonctions de fenêtre, agrégation/groupement (roll‑up) et pivot. Ces patterns, implémentés en SQL (PostgreSQL), couvrent 80% des besoins analytiques quotidiens selon la documentation PostgreSQL et retours pratiques d’équipes data.

Comment choisir les bons joins et filtres

Les joins et filtres servent à définir précisément le sous‑ensemble de données utile avant toute agrégation.

Identifier la table principale (fact table) puis joindre explicitement les tables complémentaires (dimensions) avec JOIN … ON permet de contrôler le cardinalité et d’influencer le plan d’exécution.

Privilégier les filtres (WHERE ou conditions d’ON) appliqués avant GROUP BY/agrégation réduit le volume traité et limite les lectures disque et les scans d’index.

L’ordonnancement des filtres impacte la sélection d’indices : un filtre s’appuyant sur une colonne indexée dans la clause ON/WHERE aide le moteur à choisir un index seek plutôt qu’un full scan.

SELECT m.movie_id, m.title, f.flight_id, f.duration_min FROM movies m JOIN flights f ON m.duration_min <= f.duration_min WHERE f.flight_id = 123;

— Variante : utiliser une sous‑requête pour préfiltrer les vols longs

SELECT m.movie_id, m.title, f.flight_id, f.duration_min FROM movies m JOIN (SELECT flight_id, duration_min FROM flights WHERE flight_id = 123) f ON m.duration_min <= f.duration_min;

— Variante LEFT JOIN avec filtrage sur la table jointe via ON pour conserver les films sans vol associé

SELECT m.movie_id, m.title, f.flight_id, f.duration_min FROM movies m LEFT JOIN flights f ON m.duration_min <= f.duration_min AND f.flight_id = 123;

Bonnes pratiques opérationnelles :

Ordre des clauses : Placer les JOIN puis les filtres WHERE/GROUP BY pour la lisibilité et pour guider l’optimiseur.
Filtrer tôt : Préfiltrer les grandes tables (sous‑requêtes ou CTE) pour diminuer I/O et mémoire.
Limiter les colonnes : Éviter SELECT * pour réduire le trafic réseau et le temps de sérialisation.
JOIN vs EXISTS/IN : Préférer EXISTS pour tester existence (performant sur 1:many), utiliser JOIN quand on a besoin de colonnes de la table jointe.
Indexation : Créer des index sur les colonnes utilisées pour les JOIN et les filtres hautement sélectifs.

Cas métier :

HR : Matching candidatures ↔ offres pour réduire le délai de recrutement en ciblant les candidats pertinents.
Retail : Transactions ↔ catalogue pour analyser marge produit et corriger les fiches produit erronées.
Streaming : Catalogue ↔ sessions pour recommander contenus adaptés à la durée de session et augmenter le taux de complétion.

Pattern	Objectif	Point d’attention	Exemple SQL
INNER JOIN	Récupérer uniquement les lignes ayant correspondance	Filtrage dans WHERE rend la jointure stricte	SELECT * FROM a JOIN b ON a.id=b.a_id WHERE b.flag=1;
LEFT JOIN	Conserver les lignes principales même sans correspondance	Filtrer la table jointe dans WHERE annule le LEFT	SELECT * FROM a LEFT JOIN b ON a.id=b.a_id AND b.flag=1;
EXISTS / IN	Tester existence sans ramener colonnes	EXISTS souvent meilleur pour EXISTS corrélés	SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE b.a_id=a.id);

Ce travail de sélection prépare directement les agrégations et fonctions de fenêtre à venir, car réduire le dataset avant GROUP BY optimise nettement performance et clarté des résultats.

Quand utiliser les fonctions de fenêtre

Les fonctions de fenêtre s’utilisent pour classer et ordonner des lignes à l’intérieur de partitions sans sacrifier le niveau de détail des enregistrements.

Les fonctions de fenêtre calculent des valeurs ligne à ligne tout en conservant chaque enregistrement.

RANK() attribue un rang numérique en laissant des trous quand il y a des ex æquo.

DENSE_RANK() attribue un rang numérique sans laisser de trous pour les ex æquo.

ROW_NUMBER() donne un numéro de ligne unique, même en cas d’égalité.

PARTITION BY segmente les données (ex. par canal), ORDER BY définit l’ordre à l’intérieur de chaque partition.

Exemple PostgreSQL concret : calculer le top 3 posts par canal selon le nombre de likes.

WITH ranked AS (

SELECT channel_id, post_id, likes, ROW_NUMBER() OVER (PARTITION BY channel_id ORDER BY likes DESC) AS rn

FROM posts

)

SELECT channel_id, post_id, likes FROM ranked WHERE rn <= 3;

Pratique : ROW_NUMBER convient pour un top-N strict où il faut exactement N lignes par partition.

Pratique : RANK garde la même valeur pour les ex æquo mais crée des sauts, utile si on veut conserver l’écart ordinal (ex. 1,1,3).

Pratique : DENSE_RANK compacte les rangs après ex æquo (ex. 1,1,2), utile pour attribuer places sans sauts.

Cas d’usage métier et exemples SQL courts :

Ventes — Top produits par région :

SELECT region, product_id, sales FROM (SELECT region, product_id, sales, ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales DESC) rn FROM sales) t WHERE rn <= 5;

Éducation — Top élèves par classe (tenir compte des ex æquo) :

SELECT class_id, student_id, grade FROM (SELECT class_id, student_id, grade, DENSE_RANK() OVER (PARTITION BY class_id ORDER BY grade DESC) rnk FROM grades) t WHERE rnk <= 3;

Logistique — Priorité livraisons (ordre strict) :

SELECT truck_id, delivery_id FROM (SELECT truck_id, delivery_id, ROW_NUMBER() OVER (PARTITION BY truck_id ORDER BY priority DESC, eta ASC) rn FROM deliveries) t WHERE rn = 1;

Fonction	Comportement ex æquo	Coût relatif	Exemple d’usage
ROW_NUMBER	Numéros uniques, pas d’égalité	Faible à moyen	Top-N strict
RANK	Même rang pour ex æquo, crée des sauts	Moyen	Classement ordinal avec égalités
DENSE_RANK	Même rang pour ex æquo, pas de sauts	Moyen	Classement sans trous (quotas)

Remarque finale : Les fonctions de fenêtre s’articulent naturellement après des joins/filtres pour produire le set à classer et avant des agrégations quand on veut conserver le détail; elles améliorent la lisibilité du SQL mais peuvent impacter la performance si elles sont mal partitionnées ou appliquées sur de gros volumes sans index.

Comment agréger et grouper efficacement

L’aggregation + grouping (roll‑up) consiste à choisir la bonne dimension de groupement puis agréger (COUNT, SUM, AVG) pour résumer des volumes, souvent avec HAVING pour filtrer les groupes.

Le pattern roll‑up impose de choisir la dimension de groupement (par exemple user_id, date) en fonction du niveau d’analyse souhaité. L’Ordre de granularité détermine la cardinalité : grouper par jour puis par utilisateur produit beaucoup plus de groupes que par jour seul. Je recommande de commencer par la dimension la plus business‑significative et de monter en granularité uniquement si nécessaire.

Exemple SQL PostgreSQL : utilisateurs qui ont démarré une session et passé une commande le même jour, avec comptages et montants.

-- Approche JOIN + GROUP BY
SELECT e.user_id,
       date_trunc('day', e.ts) AS day,
       COUNT(DISTINCT s.session_id) AS sessions,
       COUNT(o.order_id) AS orders,
       SUM(o.total_amount) AS revenue
FROM events e
LEFT JOIN sessions s ON s.user_id = e.user_id AND date_trunc('day',s.ts)=date_trunc('day',e.ts)
LEFT JOIN orders o ON o.user_id = e.user_id AND date_trunc('day',o.ts)=date_trunc('day',e.ts)
WHERE e.event_type = 'session_start'
GROUP BY e.user_id, day
HAVING COUNT(o.order_id) > 0;

-- Agrégation conditionnelle (Postgres FILTER)
SELECT user_id,
       date_trunc('day', ts) AS day,
       COUNT(DISTINCT session_id) FILTER (WHERE event_type='session_start') AS sessions,
       COUNT(order_id) FILTER (WHERE event_type='order') AS orders,
       SUM(total_amount) FILTER (WHERE event_type='order') AS revenue
FROM events
WHERE event_type IN ('session_start','order')
GROUP BY user_id, day
HAVING COUNT(order_id) FILTER (WHERE event_type='order') > 0;

HAVING s’applique après l’agrégation et filtre des groupes, tandis que WHERE filtre les lignes avant agrégation. Les agrégations filtrées (SUM(x) FILTER (WHERE cond)) évitent le double counting et rendent la requête plus lisible.

Cas métiers et KPI : E‑commerce → taux de conversion jour/utilisateur (KPI : users with orders / active users). SaaS → activation→abonnement (KPI : activation rate, MRR par cohort). Finance → transactions par compte (KPI : volume et montant moyen par compte).

Pattern	Exemple SQL	KPI produit	Pièges à éviter
Roll‑up user × day	GROUP BY user_id, date_trunc(‘day’,ts)	Conversion utilisateur/jour	Double counting, cardinalité explosive
Agrégation conditionnelle	SUM(amount) FILTER (WHERE status=’paid’)	Revenu segmenté	Filtres mal placés, NULL non gérés
JOIN puis agrégation	JOIN tables puis GROUP BY	Sessions corrélées aux achats	Multiplication des lignes par JOIN → overcount

Une table agrégée propre sert de base pour un pivot en transformant lignes agrégées en colonnes (par exemple jour → colonnes), ce qui facilite les dashboards et les analyses temporelles.

Comment pivoter des lignes en colonnes

Le pivot transforme des lignes en colonnes pour comparer facilement des dimensions (ex. montants par ville), utile en reporting et tableau de bord.

Le pivot s’appuie souvent sur une agrégation préalable (SUM, MAX, COUNT) pour réduire la granularité et faciliter l’affichage dans un tableau de bord, ce qui impacte les extractions vers les outils BI (données pré-agrégées, moins de post-traitement).

Méthodes en PostgreSQL

Agrégation conditionnelle avec CASE WHEN : Méthode portable, pas d’extension requise, compatible avec toutes les versions modernes de PostgreSQL.
Extension tablefunc.crosstab : Méthode optimisée pour jeux larges et colonnes dynamiques, nécessite l’installation de l’extension (CREATE EXTENSION tablefunc) et peut demander des droits superuser selon l’environnement (RDS, Cloud). Voir la documentation PostgreSQL pour compatibilité précise.

Exemple SQL simple (CASE)

SELECT city,

MAX(CASE WHEN payment_rank = 1 THEN amount END) AS top1,

MAX(CASE WHEN payment_rank = 2 THEN amount END) AS top2

FROM (

SELECT city, amount, ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS payment_rank FROM payments

) sub

GROUP BY city;

Avantages / Inconvénients

Lisibilité : Le CASE est explicite et compréhensible pour les équipes SQL, mais devient verbeux si beaucoup de colonnes ciblées.
Nombre de colonnes : Le CASE convient pour un nombre limité de pivots fixes ; crosstab gère mieux des colonnes dynamiques.
Maintenance : Le CASE est simple à versionner ; crosstab demande souvent une étape de métadonnées (liste des colonnes).
Performance : Le crosstab peut être plus rapide pour très larges jeux de données ; le CASE reste performant pour des agrégations classiques.

Cas d’usage métier

Rapports territoriaux : Présenter les trois plus gros paiements par ville pour détecter anomalies et concentrations.
Comparaison de KPI entre segments : Exporter par segment de clientèle les top N metrics pour alimenter un outil BI sans jointures complexes.

Option	Points forts	Meilleure option selon volume / fréquence
CASE WHEN	Portable, facile à debugger	Volume faible à moyen, rafraîchissement ad hoc ou quotidien
crosstab (tablefunc)	Efficace pour colonnes dynamiques et grands volumes	Volume élevé, rafraîchissement fréquent, besoin de colonnes dynamiques
Outils ETL	Meilleure pour orchestrer transformations complexes et historisation	Très grands volumes, pipelines réguliers et besoins de traçabilité

Prêt à maîtriser ces patterns analytiques ?

Je résume : maîtriser joins + filtres, fonctions de fenêtre, agrégations/groupement et pivot couvre l’essentiel des analyses SQL en entreprise. Chaque pattern répond à des besoins précis — filtrage, classement, synthèse ou mise en forme — et s’enchaîne naturellement dans un workflow analytique. En appliquant ces patterns vous gagnerez en rapidité d’exécution, en robustesse des résultats et en clarté pour vos stakeholders. Bénéfice concret : vous produirez des insights-actionnables plus rapidement et avec moins d’erreurs.

FAQ

Quels sont les patterns analytiques essentiels à connaître en SQL ?

Les quatre essentiels sont : joins + filtres, fonctions de fenêtre (ranking), agrégation/grouping (roll‑up) et pivot (rows→columns). Ensemble, ils couvrent la majorité des besoins analytiques et reporting.

Pourquoi privilégier PostgreSQL pour ces patterns ?

PostgreSQL offre une implémentation robuste des window functions, des agrégations filtrées (FILTER) et des extensions comme tablefunc pour les crosstabs. Sa conformité SQL et son optimisation en font un bon choix pour l’analyse.

Comment m’exercer efficacement à ces patterns ?

Pratiquez sur jeux de données réels ou plateformes d’exercices SQL, recréez cas métiers (ventes, sessions, paiements), comparez performances des approches et documentez vos requêtes pour réutilisabilité.

Quelle différence entre RANK, DENSE_RANK et ROW_NUMBER ?

ROW_NUMBER donne un numéro unique à chaque ligne; RANK laisse des trous en cas d’ex æquo; DENSE_RANK n’a pas de trous (les ex æquo partagent le même rang et le suivant l’incrémente sans saut). Choix selon gestion des égalités.

Quand utiliser CASE pivot vs crosstab ?

Utilisez CASE WHEN pour portabilité, petits pivots et simplicité. Préférez crosstab (tablefunc) pour grands pivots larges et performance, mais attention à la maintenance et aux colonnes fixes.

A propos de l’auteur

Franck Scandolera — expert & formateur en Tracking avancé server-side, Analytics Engineering, automatisation No/Low Code (n8n) et intégration de l’IA en entreprise. Responsable de l’agence webAnalyste et de l’organisme de formation Formations Analytics. Références clients : Logis Hôtel, Yelloh Village, BazarChic, Fédération Française de Football, Texdecor. Dispo pour aider les entreprises => contactez-moi.

Franck Scandolera

⭐ Analytics engineer, Data Analyst et Automatisation IA indépendant ⭐

Ref clients : Logis Hôtel, Yelloh Village, BazarChic, Fédération Football Français, Texdecor…

Mon terrain de jeu :

Data Analyst & Analytics engineering : tracking avancé (GTM server, e-commerce, CAPI, RGPD), entrepôt de données (BigQuery, Snowflake, PostgreSQL, ClickHouse), modèles (Airflow, dbt, Dataform), dashboards décisionnels (Looker, Power BI, Metabase, SQL, Python).
Automatisation IA des taches Data, Marketing, RH, compta etc : conception de workflows intelligents robustes (n8n, App Script, scraping) connectés aux API de vos outils et LLM (OpenAI, Mistral, Claude…).
Engineering IA pour créer des applications et agent IA sur mesure : intégration de LLM (OpenAI, Mistral…), RAG, assistants métier, génération de documents complexes, APIs, backends Node.js/Python.