Quels patterns analytiques un data scientist doit maîtriser ?

Les quatre patterns essentiels — jointures, fonctions fenêtre, agrégations/grouping et pivot — résolvent la majorité des cas analytiques; exemples et SQL fournis en PostgreSQL, inspirés d’exercices StrataScratch.

Comment utiliser les jointures et filtres efficacement

Les jointures combinées à des filtres ciblés permettent d’identifier rapidement des enregistrements pertinents en partant d’une table principale, puis en enrichissant et restreignant les données.

1) Identifier la table principale : Choisir la table qui contient la clé de départ ou le contexte métier (par exemple flight_schedule pour un vol précis).

• Critères : Donner la priorité à la table filtrée par une condition unique (ex. flight_id = X), réduire le cardinalité tôt pour limiter le travail de jointure.

2) Choisir le type de jointure : INNER pour ne garder que les correspondances exactes, LEFT pour conserver tous les enregistrements de la table principale même sans correspondance, RIGHT rarement utilisé en pratique — remplacer par LEFT en inversant l’ordre.

• Usage métier : INNER pour listes actives (clients avec commandes), LEFT pour enrichissement sans perte (employés et salaire manquant), RIGHT pour cas sporadiques où la logique impose l’inversion.

Découvrez égalementComment réussir le prétraitement des données en Python ?

3) Appliquer les filtres au bon niveau : WHERE avant agrégation pour réduire les lignes traitées, HAVING après agrégation pour filtrer sur des résultats agrégés (ex. SUM>1000).

• Impact perf : Filtrer tôt réduit I/O et CPU. Garder les filtres sélectifs côté table la plus réduite évite des joins coûteux.

Exemple PostgreSQL (pattern « Movie Duration Match ») :

-- Variante JOIN
SELECT e.movie_id, e.duration_min
FROM flight_schedule f
JOIN entertainment_catalog e ON e.duration_min <= f.duration_min
WHERE f.flight_id = 42
ORDER BY e.duration_min;

-- Variante sous-requête
SELECT e.movie_id, e.duration_min
FROM entertainment_catalog e
WHERE e.duration_min <= (
  SELECT f.duration_min
  FROM flight_schedule f
  WHERE f.flight_id = 42
)
ORDER BY e.duration_min;

Recommandation d'index : Indexer flight_schedule(flight_id) comme clé primaire, indexer entertainment_catalog(duration_min) pour les recherches par durée, indexer movie_id si jointures directes.

Plan d'exécution : Utiliser EXPLAIN ANALYSE pour comparer. Si le plan montre Seq Scan sur entertainment_catalog, ajouter un index sur duration_min. Si le join est Nested Loop avec beaucoup d'itérations, inverser l'ordre ou utiliser la sous-requête peut être plus efficace.

Découvrez égalementQuels Docker containers pour une petite entreprise ?

Cas métiers : RH — Assigner formations à créneaux disponible en joignant employés (créneaux) et catalogues formation (durée). Retail — Générer promotions liées à stock en joignant magasins (capacité) et produits (volume). Streaming — Proposer contenus adaptés à la durée de session en joignant sessions utilisateur et catalogue médias.

Quand utiliser les fonctions fenêtre pour classer

Les fonctions fenêtre (ROW_NUMBER, RANK, DENSE_RANK) servent à classer et segmenter les enregistrements à l'intérieur de partitions sans réduire l'ensemble des lignes.

PARTITION BY crée des groupes logiques (par ex. channel_id), ORDER BY définit le critère de classement (par ex. likes DESC) et les frame clauses contrôlent la portée temporelle ou par rang (par défaut pour le classement, on considère toute la partition).

ROW_NUMBER attribue un numéro unique à chaque ligne dans la partition, RANK laisse des trous quand il y a des ex aequo, DENSE_RANK ne laisse pas de trous (les rangs sont contigus).

Découvrez égalementComment créer un analyste financier privé en local ?

Exemple PostgreSQL « Top Posts Per Channel » avec CTE :

WITH ranked AS (
  SELECT post_id, channel_id, likes,
         ROW_NUMBER() OVER (PARTITION BY channel_id ORDER BY likes DESC) AS row_num
  FROM posts
)
SELECT post_id, channel_id, likes
FROM ranked
WHERE row_num <= 3;

Le CTE améliore la lisibilité en séparant l'étape de classement de la filtration, ce qui facilite la maintenance et la réutilisation.

Variante pour extraire les ties (ex. conserver tous les ex aequo au rang 3) :

WITH ranked AS (
  SELECT post_id, channel_id, likes,
         RANK() OVER (PARTITION BY channel_id ORDER BY likes DESC) AS rnk
  FROM posts
)
SELECT post_id, channel_id, likes
FROM ranked
WHERE rnk <= 3;

Voici quelques cas d'usage concrets :

• Top commerciaux par région : SELECT sales_rep, region, revenue, ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn ... WHERE rn <= 10.
• Top étudiants par promotion : SELECT student_id, cohort, score, DENSE_RANK() OVER (PARTITION BY cohort ORDER BY score DESC) AS dr ... WHERE dr <= 5.
• Top livreurs par secteur : SELECT driver_id, area, deliveries, RANK() OVER (PARTITION BY area ORDER BY deliveries DESC) AS r ... WHERE r <= 3.

Découvrez égalementQuels cours SQL gratuits offrent un certificat reconnu ?

Optimisation : limiter la taille des partitions quand possible, veiller à la mémoire (opération peut être coûteuse en RAM), et ordonner sur des colonnes indexées pour accélérer l'ORDER BY. Exemple d'EXPLAIN simplifié : « WindowAgg » indique l'utilisation d'une fenêtre, vérifier si un Index Scan est utilisé avant l'agrégation.

Comment agréger et grouper pour analyser des séries

L'agrégation + grouping permet de résumer des événements selon des dimensions temporelles ou utilisateur et d'obtenir des métriques clés (COUNT, SUM, AVG).

Pour obtenir des agrégats fiables suivez cette séquence recommandée.
1) Définir la dimension de regroupement (ex. user_id, session_date).
2) Dédupliquer si nécessaire (sessions distinctes).
3) Joindre aux tables de faits (orders).
4) Appliquer GROUP BY et agrégats.
5) Filtrer via HAVING si besoin (ex. users avec >=1 commande).

Exemple PostgreSQL "Same-Day Orders".
Tables : sessions(session_id, user_id, session_start::date) et order_summary(order_id, user_id, order_date::date, amount).
Requête simple par user_id/session_date :

SELECT
  s.user_id,
  s.session_start::date AS session_date,
  COUNT(o.order_id) AS orders_count,
  SUM(o.amount) AS total_amount
FROM sessions s
JOIN order_summary o
  ON s.user_id = o.user_id
  AND s.session_start::date = o.order_date
GROUP BY s.user_id, session_date;

Version avec DISTINCT ON pour dédupliquer une session par utilisateur/jour :

WITH dedup_sessions AS (
  SELECT DISTINCT ON (user_id, session_start::date)
    session_id, user_id, session_start::date AS session_date
  FROM sessions
  ORDER BY user_id, session_start::date, session_start DESC
)
SELECT d.user_id, d.session_date, COUNT(o.order_id), SUM(o.amount)
FROM dedup_sessions d
JOIN order_summary o
  ON d.user_id = o.user_id
  AND d.session_date = o.order_date
GROUP BY d.user_id, d.session_date;

Découvrez égalementComment automatiser le feature engineering en Python efficacement ?

Version avec window functions pour choisir la session "principale" :

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id, session_start::date ORDER BY session_start DESC) AS rn
  FROM sessions
)
SELECT r.user_id, r.session_start::date AS session_date, COUNT(o.*), SUM(o.amount)
FROM ranked r
JOIN order_summary o
  ON r.user_id = o.user_id AND r.session_start::date = o.order_date
WHERE r.rn = 1
GROUP BY r.user_id, session_date;

Cas d'usage et granularités.
E‑commerce : taux de conversion par session/jour/semaine.
SaaS : activation utilisateur par cohortes hebdo/mensuelle.
Finance : réconciliation journalière et agrégats trimestriels pour reporting.
Adapter la granularité en utilisant date_trunc('week'/'month'/'quarter', timestamp) pour regrouper.

Pièges courants et optimisations.
Gérer les NULLs et les timestamps hors fuseau (utiliser timestamptz et AT TIME ZONE).
Eviter agrégats sur colonnes non indexées sans filtrage préalable.
Index recommandés : index composite (user_id, session_start), (user_id, order_date).
Utiliser materialized views pour tables volumineuses avec rafraîchissement planifié.

Comment transformer des lignes en colonnes avec un pivot

Le pivot transforme des lignes en colonnes pour comparer des mesures par dimension et peut s'implémenter via crosstab (tablefunc) ou agrégats conditionnels CASE WHEN en PostgreSQL.

Le pivot sert à confronter des mesures (somme, max, count) par dimension (type, quartier, mois).

Explication des deux approches, avantages et limites.

Approche 1 — Agrégats filtrés / CASE WHEN.

Requête simple pour « Highest Payment from the City of San Francisco » pivotée par type de paiement (exemple par quartier) :

SELECT neighborhood,
       MAX(amount) FILTER (WHERE payment_type = 'Rent')   AS max_rent,
       MAX(amount) FILTER (WHERE payment_type = 'Utilities') AS max_utilities,
       MAX(amount) FILTER (WHERE payment_type = 'Other')  AS max_other
FROM payments
WHERE city = 'San Francisco'
GROUP BY neighborhood;

Alternative équivalente avec CASE WHEN :

SELECT neighborhood,
       MAX(CASE WHEN payment_type='Rent' THEN amount END) AS max_rent,
       MAX(CASE WHEN payment_type='Utilities' THEN amount END) AS max_utilities
FROM payments
WHERE city='San Francisco'
GROUP BY neighborhood;

Approche 2 — Extension tablefunc crosstab.

Création de l'extension puis utilisation :

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  $$
  SELECT neighborhood, payment_type, MAX(amount)
  FROM payments
  WHERE city = 'San Francisco'
  GROUP BY neighborhood, payment_type
  ORDER BY neighborhood, payment_type
  $$,
  $$VALUES ('Rent'),('Utilities'),('Other')$$
) AS ct(neighborhood text, rent numeric, utilities numeric, other numeric);

SQL dynamique pour colonnes inconnues (exécution PL/pgSQL avec string_agg + EXECUTE) :

DO $$
DECLARE cols text;
BEGIN
  SELECT string_agg(quote_ident(payment_type) || ' numeric', ', ') INTO cols
  FROM (SELECT DISTINCT payment_type FROM payments WHERE city='San Francisco') t;

  EXECUTE format(
    'SELECT * FROM crosstab($$SELECT neighborhood, payment_type, MAX(amount) FROM payments WHERE city = %L GROUP BY neighborhood, payment_type ORDER BY 1,2$$) AS ct(neighborhood text, %s)',
    'San Francisco', cols
  );
END;
$$;

Cas d'usage (explication courte puis listes) :

• Tableaux de bord comparatifs : Comparer KPI par catégorie (ex. paiement max par type et quartier).
• Rapport multi-dimensions : Export CSV avec colonnes fixées pour analyse Excel.
• KPI mensuels pivotés : Mois en colonnes pour visualiser tendance.

Contraintes : Colonnes fixes vs dynamiques, NULLs générés quand combinaison absente, gestion de la performance sur gros jeux.

Prêt à appliquer ces patterns analytiques sur vos jeux de données ?

Ces quatre patterns — jointures/filtres, fonctions fenêtre, agrégations/grouping et pivot — constituent la boîte à outils qui couvre la majorité des problématiques analytiques décrites. En maîtrisant les séquences et variantes SQL (PostgreSQL), vous standardisez vos analyses, gagnez en vitesse de production et améliorez la fiabilité des résultats. Appliquez-les pour rendre vos tableaux de bord et vos décisions business plus robustes, rapidement.

FAQ

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.