Quels patterns analytiques un data scientist doit maîtriser?

Maîtriser les patterns analytiques (joins, window functions, agrégations, pivot) permet d’extraire des insights fiables et réutilisables ; PostgreSQL illustre ces techniques via ses fonctions fenêtrées et ses agrégats (PostgreSQL docs). Je détaille motifs, SQL types et cas d’usage pour les appliquer tout de suite.

Comment trouver le bon sous-ensemble avec joins et filtres

On identifie d’abord la table primaire, puis on joint les tables secondaires et on applique des filtres pour isoler le sous-ensemble pertinent.

La logique du motif «Joins + Filters» repose sur quatre étapes claires.

• Identification de la table primaire : Choisir l’entité centrale qui détermine le contexte (ici flight_schedule pour un vol donné).
• Choix du type de jointure : Préférer INNER JOIN quand on ne veut que les correspondances, LEFT JOIN quand on doit conserver la ligne primaire même sans correspondance.
• Conditions de jointure : Écrire des conditions précises dans ON pour éviter les produits cartésiens et pour limiter le volume dès la jointure.
• Filtres WHERE/HAVING : Appliquer des filtres après la jointure pour restreindre le résultat final, ou utiliser HAVING pour les agrégations.

Bonnes pratiques importantes : Sélectionner uniquement les colonnes nécessaires pour réduire le trafic réseau et la mémoire, indexer les colonnes utilisées en WHERE et ON, et filtrer tôt (pré-filtrage via CTE ou sous-requêtes) pour diminuer les données jointes.

Découvrez égalementQuels sont les meilleurs cours SQL gratuits avec certificat ?

Pièges courants : Un ON manquant ou incorrect peut créer un produit cartésien massif, et placer un filtre sur la mauvaise table (par exemple filtrer après un LEFT JOIN comme si c’était INNER) change le résultat.

WITH f AS (
  SELECT flight_id, duration
  FROM flight_schedule
  WHERE flight_id = 101
)
SELECT e.movie_id, e.title, e.duration
FROM f
JOIN entertainment_catalog e
  ON e.duration 

WITH f AS (...) Préfiltre le vol ciblé pour réduire la table primaire à une ligne.

SELECT e.movie_id, e.title, e.duration Choisit uniquement les colonnes utiles pour l'usage analytique.

FROM f JOIN entertainment_catalog e ON e.duration

Découvrez égalementComment la data analytics révolutionne-t-elle la sécurité en entrepôt ?

ORDER BY e.duration DESC Classe les films du plus long au plus court pour la sélection.

Pour de gros volumes, indexer flight_schedule(flight_id) et entertainment_catalog(duration) aide, limiter les colonnes renvoyées réduit I/O, utiliser JOIN LATERAL pour des critères dépendant ligne à ligne, et préfiltrer les deux tables via CTE si possible.

• RH : Associer employés et formations pour n'afficher que les formations éligibles selon ancienneté.
• Retail : Joindre transactions et catalogue pour ne garder que les promos applicables au panier.
• Streaming : Filtrer catalogue selon la durée et le profil utilisateur pour proposer only contenus adaptés.

Quand utiliser les fonctions fenêtrées pour classer

Les fonctions fenêtrées servent à classer ou ordonner les enregistrements tout en conservant le contexte des partitions.

Principe : Une window function calcule des valeurs sur un ensemble de lignes appelées « partition » tout en conservant chaque ligne d'origine.

ORDER BY dans OVER() détermine l'ordre interne à la partition et influence le résultat de RANK(), DENSE_RANK() et ROW_NUMBER().

Découvrez égalementComment Docker simplifie-t-il la reproductibilité en data science ?

RANK() attribue le même rang aux ties puis saute des positions, DENSE_RANK() attribue le même rang mais sans sauts, ROW_NUMBER() donne un numéro unique (utile pour déduplication).

SELECT channel_id, post_id, likes, RANK() OVER (PARTITION BY channel_id ORDER BY likes DESC) AS rnk FROM posts;

Pour ne garder que les 3 publications les plus likées par canal utiliser une sous-requête ou CTE :

SELECT channel_id, post_id, likes
FROM (
  SELECT channel_id, post_id, likes,
         RANK() OVER (PARTITION BY channel_id ORDER BY likes DESC) AS rnk
  FROM posts
) t
WHERE rnk 

En cas d'égalité, RANK() retournera plusieurs lignes avec le même rang et sautera des positions (ex. 1,1,3), DENSE_RANK() donnera 1,1,2 et ROW_NUMBER() forcera un ordre arbitraire (1,2,3).

Performances : Les window functions lisent toutes les lignes concernées par la partition après les filtres. Utiliser des index couvrants sur (channel_id, likes DESC) pour accélérer le tri. Pré-filtrer avec WHERE ou CTE pour réduire le volume, et envisager le partitionnement physique si la table est massive.

• Vente : Top vendeurs par région pour fixer des quotas mensuels.
• Éducation : Top étudiants par promotion pour bourses et distinctions.
• Logistique : Top livreurs par zone pour optimiser incentives.

Découvrez égalementQuels sont les meilleurs outils Python ETL pour data engineering ?

Exemple de déduplication avec ROW_NUMBER :

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY external_id ORDER BY updated_at DESC) AS rn
  FROM items
)
DELETE FROM items
USING ranked
WHERE items.id = ranked.id AND ranked.rn > 1;

Comment agréger et faire du roll-up pour résumés

L'agrégation et le grouping (ROLLUP/GROUPING SETS) permettent de résumer les données par dimensions analytiques et d'obtenir des totaux hiérarchiques.

Le motif Aggregation + Grouping consiste à choisir des dimensions pertinentes, appliquer un GROUP BY, calculer des métriques avec COUNT/SUM/AVG et filtrer les groupes avec HAVING si nécessaire.

Choisir les dimensions : Sélectionner les axes analytiques (par ex. user_id, session_date).

• Appliquer GROUP BY : Regrouper les lignes pour résumer.
• Utiliser COUNT/SUM/AVG : Calculer nombre d'événements, montants, moyennes.
• Filtrer avec HAVING : Exclure groupes non pertinents (ex. SUM(total)=0).

Exemple pratique : Trouver les utilisateurs ayant commencé une session et passé une commande le même jour en joignant sessions (distinct user_id, session_date) et orders, puis GROUP BY user_id, session_date pour COUNT et SUM.

WITH sessions_day AS (
  -- Une session par utilisateur et par jour
  SELECT DISTINCT user_id, session_date::date AS session_date
  FROM sessions
),
orders_day AS (
  -- Total des commandes par utilisateur et par jour
  SELECT user_id, order_date::date AS session_date, SUM(total) AS total_order
  FROM orders
  GROUP BY user_id, session_date
)
SELECT
  s.user_id,
  s.session_date,
  COUNT(o.*) AS orders_count,            -- nombre de lignes de orders liées
  COALESCE(o.total_order, 0) AS total_order  -- montant total des commandes ce jour
FROM sessions_day s
LEFT JOIN orders_day o
  ON s.user_id = o.user_id
  AND s.session_date = o.session_date
GROUP BY s.user_id, s.session_date;

Découvrez égalementQuels changements en 2026 pour les régulations de la vie privée et l’analytics ?

Le pattern Roll-Up permet d'obtenir plusieurs niveaux d'agrégation en une requête : par jour, par utilisateur, et un total général.

Exemple :

SELECT
  user_id,
  session_date,
  COUNT(*) AS sessions_count,
  SUM(total_order) AS total_order
FROM combined_table
GROUP BY ROLLUP (user_id, session_date);

On peut aussi expliciter avec GROUPING SETS pour contrôler les combinaisons (par ex. (user_id, session_date), (user_id), ()).

Cas d'usage :

• E-commerce : Résumer ventes par produit, par jour, par catégorie pour détecter tendances.
• SaaS : Mesurer activation par utilisateur et par période pour suivre churn.
• Finance : Consolider positions par compte, par jour et totaux pour reporting réglementaire.

Limites : Cardinalité élevée des dimensions peut exploser le nombre de groupes et impacter les performances ; Attention aux scans complets et besoin possible d'index, de pré-agrégations ou de data-warehouse.

Comment transformer des lignes en colonnes avec pivot

Le pivot transforme des lignes agrégées en colonnes pour des rapports lisibles ; on peut utiliser FILTER avec des agrégats ou la fonction crosstab de PostgreSQL.

Objectif du motif «Pivoting». Rendre lisible un tableau où une dimension catégorielle (type de paiement, canal, mois) doit devenir des colonnes pour comparaisons rapides et export BI.

Méthodes courantes et explications succinctes avant la liste des avantages/inconvénients.

• SUM(...) FILTER (WHERE ...): Agrége directement par condition, syntaxe claire et performante, idéale quand les catégories sont connues.
• SUM(CASE WHEN ... THEN ... ELSE 0 END): Compatible avec presque tous les SGBD, utile si FILTER n'est pas disponible.
• tablefunc.crosstab: Produit un pivot «véritable» moins verbeux en SQL mais nécessite l'extension et des colonnes fixes dans la signature de sortie.

Exemple lié aux paiements les plus élevés par entité (ville San Francisco). Exemple avec FILTER pour total par type de paiement par ville :

SELECT city,
  SUM(amount) FILTER (WHERE payment_type = 'card') AS card_total,
  SUM(amount) FILTER (WHERE payment_type = 'cash') AS cash_total
FROM payments
GROUP BY city;

Implémentation crosstab (activer l'extension tablefunc) :

CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT *
FROM crosstab(
  $$SELECT city, payment_type, SUM(amount)
    FROM payments
    GROUP BY city, payment_type
    ORDER BY city, payment_type$$
) AS ct(city text, card_total numeric, cash_total numeric, check_total numeric);

Génération dynamique des colonnes. Requérir une étape supplémentaire pour interroger DISTINCT payment_type et construire la requête SQL dynamiquement via PL/pgSQL ou l'outil applicatif, ce qui augmente la complexité mais permet d'éviter les changements manuels.

Impacts sur tableaux de bord et maintenance. Les pivots SQL centralisent la logique et diminuent la charge côté BI, mais rendent les modifications de schéma plus coûteuses à maintenir. Les outils BI peuvent pivoter dynamiquement et gérer les colonnes variables sans déploiement SQL.

Cas d'usage rapides :

• Reporting périodique pour export CSV/Excel.
• Dashboards opérationnels nécessitant colonnes stables et performances rapides.
• Export pour ETL/BI quand la source doit être normalisée avant ingestion.

Prêt à appliquer ces patterns pour produire des analyses exploitables ?

Les patterns analytiques — joins+filters, window functions, aggregation/roll-up et pivot — forment une boîte à outils réutilisable pour tout data scientist travaillant en SQL. Maîtriser chaque motif vous fait gagner en rapidité, robustesse et scalabilité : requêtes plus performantes, résultats fiables et rapports exploitables. En appliquant ces templates SQL et bonnes pratiques, vous transformez des données brutes en décisions actionnables, immédiates et mesurables.

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 Formations Analytics. Références : 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.