Quels patrons analytiques un data scientist doit connaitre ?

Les patrons analytiques clés — joins+filtres, window functions, agrégation/grouping et pivot — permettent de résoudre 80% des besoins analytiques en SQL (voir PostgreSQL docs et exercices StrataScratch). Lisez la suite pour appliquer ces motifs métier et gagner en efficacité.

Comment utiliser joins et filtres efficacement

Commencez par identifier la table primaire, joignez ensuite les tables d’appoint, puis filtrez pour restreindre le périmètre et réduire les erreurs et l’I/O superflu.

Étapes pas-à-pas :

• Identifier la table primaire — Celle qui représente l’entité principale de votre requête (par exemple vols, commandes, sessions).
• Choisir le type de jointure — INNER JOIN pour intersection, LEFT JOIN pour conserver la table primaire complète, RIGHT JOIN rarement utilisé sauf symétrie).
• Appliquer les filtres — Placer les filtres qui réduisent cardinalité le plus tôt possible; mettre les conditions liées à la relation dans ON si vous voulez préserver la cardinalité d’une LEFT JOIN.

Trois cas métiers avec schéma compact :

• RH heures/heures supp — employés(emp_id, nom), timesheets(emp_id, date, heures), approvals(timesheet_id, approved).
• Retail commandes×produits — orders(order_id, client_id, date), order_lines(order_id, sku, qty, price), products(sku, category).
• Streaming sessions×utilisateurs — sessions(session_id, user_id, start_ts, duration_min), users(user_id, plan, region), content_watched(session_id, title).

Requête PostgreSQL exemple (films dont la durée ≤ durée du vol) :

SELECT
  fs.flight_id,
  fs.departure,
  fs.arrival,
  fs.duration_minutes AS flight_duration,
  ec.movie_id,
  ec.title,
  ec.duration_minutes AS movie_duration
FROM flight_schedule fs
INNER JOIN entertainment_catalog ec
  ON ec.duration_minutes = '2026-01-01'::date;

Explication clause par clause :

• FROM flight_schedule fs — Table primaire, réduit d’abord par date dans WHERE pour limiter les lignes.
• INNER JOIN entertainment_catalog ec ON ec.duration_minutes
• WHERE fs.departure >= … — Filtre précoce sur la table primaire pour optimiser I/O.

Découvrez égalementQuel outil de prévision séries temporelles choisir ?

Variante LEFT JOIN pour préserver les vols sans contenu :

SELECT fs.flight_id, ec.title
FROM flight_schedule fs
LEFT JOIN entertainment_catalog ec
  ON ec.duration_minutes 

Bonnes pratiques de performance :

• Filtrer tôt — Appliquer WHERE ou conditions de base avant la jointure pour réduire cardinalité.
• Indexer — Créer index sur les clés de jointure et colonnes filtrées.
• Éviter SELECT * — Ne ramener que les colonnes nécessaires pour réduire réseau et mémoire.
• Matérialiser une sous-requête — Utiliser une table matérialisée ou CTE matérialisée si le sous-jeu est réutilisé ou coûteux.

Quand appliquer les fonctions de fenêtre

Quand appliquer les fonctions de fenêtre : Pour classer, ordonner ou calculer des mesures qui nécessitent un contexte de partition sans écraser les lignes originales (agrégation destructive).

Principaux opérateurs et leur logique :

• ROW_NUMBER(): Numérote chaque ligne dans sa partition de 1 à N, utile pour pagination ou dédoublonnage.
• RANK(): Donne la même position aux valeurs égales puis saute des rangs (ex. 1,2,2,4).
• DENSE_RANK(): Donne la même position aux valeurs égales sans sauter de rangs (ex. 1,2,2,3).
• NTILE(n): Répartit les lignes en n buckets proches de la même taille.
• SUM() OVER / AVG() OVER: Calculs agrégés avec contexte (fenêtre) sans réduire le nombre de lignes.

Découvrez égalementComment automatiser le data preprocessing en Python ?

Séquence logique à respecter : PARTITION BY → ORDER BY → fonction fenêtre. Chaque clause a un rôle clair : PARTITION BY définit le groupe, ORDER BY l’ordre intra-groupe, la fonction calcule sur ce cadre.

SELECT p.id, p.title, c.name AS channel_name, p.likes, p.rnk
FROM (
  SELECT id, channel_id, title, likes,
         RANK() OVER (PARTITION BY channel_id ORDER BY likes DESC) AS rnk
  FROM posts
) p
JOIN channels c ON p.channel_id = c.id
WHERE p.rnk 

Résultat attendu : Pour chaque channel, les trois meilleurs posts par nombre de likes. En cas d’égalité au rang 3, plusieurs posts peuvent apparaître (RANK crée des égalités qui peuvent élargir le résultat).

Deux variantes pratiques :

• ROW_NUMBER() pour la pagination ou garder une seule ligne par clé (ex. garder row_number=1 pour dédupliquer).
• SUM() OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) pour des cumuls temporels (running totals).

Usages métiers : Classement des commerciaux par région, classement d’élèves par promotion, suivi de performance des livreurs (par jour/semaine).

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

Impact performance : Fonctions fenêtre peuvent être coûteuses sur de très grandes partitions. Index couvrants sur (partition_col, order_col) réduisent les tris. Préférer filtrer/agréger avant d’appliquer la fenêtre ou utiliser des vues matérialisées pour charges répétées.

Comment agréger et grouper pour résumer les données

Pour résumer des données, identifiez d’abord la ou les dimensions de groupement, puis appliquez des fonctions d’agrégation et filtrez si nécessaire. Cette démarche réduit les volumes et révèle des KPIs clés comme les totaux, les moyennes et les comptes uniques.

Étapes essentielles avant la requête :

• Choisir les dimensions : Sélectionnez les colonnes qui définissent le groupement (ex. user_id, session_date).
• Sélectionner les mesures : Utilisez COUNT, SUM, AVG, MIN, MAX selon le KPI souhaité.
• Appliquer GROUP BY : Groupez sur les dimensions choisies.
• Filtrer avec HAVING : Filtrez les groupes après agrégation (ex. SUM(order_value) > 0).
• Trier avec ORDER BY : Classez les groupes selon la priorité métier.

Exemple PostgreSQL pratique : détecter les utilisateurs ayant démarré une session et passé une commande le même jour. Attention aux pièges : double comptage si jointure 1:N, fuseaux horaires et date_trunc à utiliser pour normaliser les dates.

-- Détecter sessions + commandes le même jour
SELECT
  s.user_id,
  date_trunc('day', s.started_at AT TIME ZONE 'UTC')::date AS session_date,
  COUNT(*) AS session_count,
  SUM(o.order_value) AS total_order_value
FROM sessions s
JOIN order_summary o
  ON s.user_id = o.user_id
  AND date_trunc('day', s.started_at AT TIME ZONE 'UTC') = date_trunc('day', o.order_date AT TIME ZONE 'UTC')
GROUP BY s.user_id, session_date
HAVING COUNT(*) > 0 AND SUM(o.order_value) > 0
ORDER BY total_order_value DESC;

Découvrez égalementComment BERTopic améliore-t-il le topic modeling ?

ROLLUP et GROUPING SETS permettent d’obtenir totaux intermédiaires et totaux globaux sans multiplier les requêtes. Exemple ROLLUP pour CA par jour, par client et total global :

SELECT
  COALESCE(client_id, 'TOTAL') AS client_id,
  date_trunc('day', order_date)::date AS day,
  SUM(order_value) AS ca
FROM orders
GROUP BY ROLLUP (client_id, date_trunc('day', order_date)::date)
ORDER BY day NULLS LAST, client_id;

Cas métiers : E‑commerce (CA par client/jour), SaaS (connexions par utilisateur/semaine), Finance (transactions par compte/trimestre).

Conseils pratiques : Utiliser des agrégations anonymes pour réduire les données transférées, filtrer en amont pour réduire la cardinalité, vérifier les NULLs et COALESCE pour éviter les résultats trompeurs.

Pourquoi et comment pivoter des données en colonnes

Le pivot transforme des lignes en colonnes pour rendre la lecture et la comparaison plus faciles, par exemple pour afficher le montant par ville et par type afin d'identifier le "Highest Payment from the City of San Francisco".

Découvrez égalementQuel cours SQL gratuit choisir selon votre profil ?

Motif du pivoting : faciliter le reporting (montants par année), comparer des KPI par produit ou construire des matrices de conversion par canal.

• Agrégation conditionnelle (portable et simple) : utiliser SUM(CASE WHEN … THEN value ELSE 0 END) GROUP BY pour générer des colonnes fixes.
• tablefunc.crosstab (spécifique PostgreSQL) : extension qui transforme une source en deux/trois colonnes en une table pivotée, plus concise mais nécessite une installation et une définition statique des colonnes de sortie.

SELECT city,
  SUM(CASE WHEN type = 'A' THEN amount ELSE 0 END) AS amount_a,
  SUM(CASE WHEN type = 'B' THEN amount ELSE 0 END) AS amount_b,
  SUM(CASE WHEN type = 'C' THEN amount ELSE 0 END) AS amount_c
FROM payments
GROUP BY city
ORDER BY city;

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  $$SELECT city, type, SUM(amount) FROM payments GROUP BY city, type ORDER BY 1,2$$
) AS ct(city text, amount_a numeric, amount_b numeric, amount_c numeric);

Points pratiques : le pivot nécessite généralement un ensemble de colonnes fixe pour être performant et simple à maintenir. Les colonnes dynamiques obligent à générer du SQL dynamique (ETL, scripts en PL/pgSQL ou côté applicatif). Les NULLs surviennent quand une combinaison n'existe pas ; remplacer par 0 via COALESCE quand nécessaire. En termes de performance, les deux approches scannent les données ; l'agrégation conditionnelle reste très portable, tandis que crosstab peut être plus lisible et compact pour des sorties larges mais impose une définition de type de retour.

• Cas d'usage : reporting financier multi-années.
• Cas d'usage : comparaison de KPI par produit.
• Cas d'usage : matrice de conversion par canal.

Prêt à appliquer ces patrons analytiques sur vos datasets ?

Les quatre motifs présentés — joins+filtres, fonctions fenêtre, agrégation/grouping et pivot — couvrent l’essentiel des besoins analytiques courants et permettent de standardiser vos pipelines SQL. En les maîtrisant vous réduisez le temps d’analyse, limitez les erreurs de logique et améliorez la performance des requêtes. Appliquez-les dès vos prochains cas d’usage pour obtenir des insights exploitables plus vite, et transformer des demandes ad hoc en patterns réutilisables au bénéfice de vos dashboards et décisions métiers.

FAQ

A propos de l'auteur

Franck Scandolera — expert & formateur en Tracking avancé server-side, Analytics Engineering et Automatisation No/Low Code (n8n). J’intègre l’IA dans les entreprises et optimise les flux de données pour des clients comme Logis Hôtel, Yelloh Village, BazarChic, Fédération Française de Football, Texdecor. Responsable de l’agence webAnalyste et de l’organisme de formation Formations Analytics. 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.