Quels analytics patterns un data scientist doit maîtriser ?

Les patterns-clés sont joins + filters, fonctions fenêtre, agrégations/grouping et pivoting, appliqués via SQL réutilisable. Ces schémas, démontrés par exercices d’entretien (StrataScratch) et la doc PostgreSQL, structurent l’analyse et facilitent la réutilisabilité des pipelines analytiques.

Comment joindre et filtrer pour isoler un sous-ensemble ?

On identifie d’abord la table primaire, on joint les données complémentaires, puis on applique des filtres pour isoler le sous-ensemble pertinent.

Étapes logiques :

• Choisir la table primaire qui représente l’entité d’analyse (par exemple flight_schedule pour des vols).
• Joindre les tables complémentaires en définissant clairement la condition de jointure (INNER JOIN si la correspondance est obligatoire, LEFT JOIN si vous voulez conserver les lignes sans correspondance).
• Appliquer les filtres métiers après ou dans la jointure selon l’effet voulu (filtre dans ON limite les lignes jointes, filtre dans WHERE peut exclure les lignes LEFT JOIN nullifiées).
• Contrôler la cardinalité et dédupliquer si nécessaire (agrégation, DISTINCT ON, row_number()).

SELECT
  fs.flight_id,
  fs.origin,
  fs.destination,
  fs.duration_minutes AS flight_minutes,
  ec.movie_id,
  ec.title,
  ec.duration_minutes AS movie_minutes
FROM flight_schedule fs
INNER JOIN entertainment_catalog ec
  ON ec.duration_minutes <= fs.duration_minutes
WHERE fs.flight_date = CURRENT_DATE
ORDER BY fs.flight_id, ec.duration_minutes DESC;

Quand INNER JOIN vs LEFT JOIN : INNER JOIN quand la correspondance est nécessaire et que les lignes sans correspondance n'ont pas de valeur métier. LEFT JOIN quand vous voulez conserver la table primaire même si la table secondaire manque.

Pourquoi l'ordre compte : Placer la table primaire à gauche améliore la lisibilité et, pour certains moteurs, facilite l'optimiseur et la planification d'accès aux index.

Découvrez égalementComment préparer un entretien Data Engineer pour 2026 ?

Cas d'usage métier :

• RH : L'entrée est employees, joint sur trainings pour filtrer les employés ayant suivi une formation spécifique (WHERE training_date > ...).
• Retail : L'entrée est sales_orders, joint sur product_catalog pour sélectionner les commandes contenant des produits en promotion (WHERE discount IS NOT NULL).
• Streaming : L'entrée est user_sessions, joint sur content_meta pour recommander les titres dont la durée ≤ durée restante de la session.

Pièges fréquents et recommandations :

• Cardinalité élevée → Peut exploser le volume, recommandez d'agréger ou filtrer avant la jointure.
• Duplication de lignes → Utiliser DISTINCT ON (Postgres) ou row_number() pour dédupliquer.
• Filtres dans JOIN vs WHERE → Mettre les conditions d'association dans ON, les conditions de post-filtre dans WHERE pour éviter d'éliminer des LEFT JOIN.
• Nulls → Traiter explicitement les valeurs NULL avec COALESCE si nécessaire.
• Index → Créer index sur les clés de jointure; pour inégalités, pré-filtrer la table la plus volumineuse.

Quand utiliser les fonctions fenêtre pour classer ?

On utilise les fonctions fenêtre pour classer ou ordonner des lignes à l’intérieur de partitions sans réduire le niveau de granularité.

RANK(), DENSE_RANK() et ROW_NUMBER() calculent des positions dans une partition.

• RANK() attribue le même rang aux ex-æquo et laisse des "trous" dans la numérotation (ex. deux éléments à rang 1, suivant rang 3).
• DENSE_RANK() attribue le même rang aux ex-æquo mais sans trous (ex. deux éléments à rang 1, suivant rang 2).
• ROW_NUMBER() donne un numéro unique à chaque ligne, même si les valeurs sont identiques, utile pour désambiguïser.

Exemple simple illustrant un ex-æquo : si trois posts ont 100, 100 et 90 likes alors RANK → 1,1,3 ; DENSE_RANK → 1,1,2 ; ROW_NUMBER → 1,2,3 (ordre déterminé par ORDER BY additionnel si nécessaire).

Découvrez égalementQuels cours gratuits Data Analytics délivrent un certificat ?

Exemple PostgreSQL pour "top 3 posts par channel" avec gestion des égalités via CTE :

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

Conseils pratiques :

• Choisir la clé de partition en fonction du périmètre métier (par ex. region, channel, course).
• Définir l'ordre (ORDER BY) avec les colonnes pertinentes et des tie-breakers si besoin (date, id) pour reproductibilité.
• Anticiper l'impact performance : index sur (partition_key, order_key DESC) peut accélérer les scans ; envisager index couvrant si la table est large.
• Utiliser ORDER BY dans la requête finale pour présenter les résultats ; les fonctions fenêtre ne trient pas implicitement l'ensemble final.

Trois cas d'usage métier :

• Ventes : top produits par région — implémentation : PARTITION BY region ORDER BY revenue DESC.
• Éducation : top étudiants par cours — implémentation : PARTITION BY course_id ORDER BY grade DESC, submission_date ASC.
• Logistique : top hubs par volume — implémentation : PARTITION BY hub_id ORDER BY shipments DESC.

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

On identifie la dimension de regroupement, on applique GROUP BY et les agrégats (COUNT, SUM, AVG) puis éventuellement HAVING pour filtrer les groupes.

Identifier la clé de regroupement signifie choisir la colonne temporelle, la catégorie ou l'identifiant qui répond au besoin métier. Appliquer GROUP BY calcule des résumés par groupe, les fonctions d'agrégat synthétisent les valeurs, et HAVING filtre après agrégation.

WITH sessions AS (
  SELECT DISTINCT user_id, date_trunc('day', started_at) AS day
  FROM sessions
),
orders AS (
  SELECT id, user_id, date_trunc('day', created_at) AS day, total_amount
  FROM orders
)
SELECT
  s.day,
  COUNT(o.id) AS total_orders,
  SUM(o.total_amount) AS total_value
FROM sessions s
JOIN orders o ON s.user_id = o.user_id AND s.day = o.day
GROUP BY s.day
HAVING COUNT(o.id) >= 1
ORDER BY s.day;

Découvrez égalementComment maîtriser probabilité et statistiques en data science ?

La requête précédente identifie les jours où un utilisateur a à la fois démarré une session et passé commande le même jour, puis calcule le nombre total de commandes et la valeur totale par jour.

Pour obtenir sous-totaux et total global, utiliser ROLLUP ou GROUPING SETS.

Syntaxe PostgreSQL standard : GROUP BY ROLLUP(col1, col2) ou GROUP BY GROUPING SETS ((col1, col2), (col1), ());

SELECT date_trunc('month', created_at) AS month, product_category,
       SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (date_trunc('month', created_at), product_category);

• Attention aux NULL dans GROUP BY : Les NULL forment un groupe unique, penser à COALESCE si nécessaire.
• Agrégats filtrés : Utiliser SUM(CASE WHEN condition THEN value ELSE 0 END) pour filtrer dans l'agrégat.
• Ordre des filtres : WHERE réduit les lignes avant l'agrégation, HAVING filtre les groupes après agrégation.
• Optimisation : Pré-agréger, réduire la cardinalité (par ex. bucketiser les dates) et indexer les colonnes de jointure.

Cas d'usage :

• E-commerce : Agréger par jour et catégorie pour suivre ventes et panier moyen par segment.
• SaaS : Grouper par plan et mois pour calculer churn, MRR et tendances par cohortes.
• Finance : Agréger par instrument et jour pour mesurer volume, VaR et exposition par classe d'actifs.

Comment pivotiser des lignes en colonnes efficacement ?

Le pivot transforme des valeurs de lignes en colonnes pour faciliter la lecture ou alimenter des dashboards, via des agrégations conditionnelles ou l’extension crosstab de PostgreSQL.

Approche 1 — Agrégations conditionnelles avec FILTER. Cette méthode utilise des agrégations standard avec un filtre conditionnel par colonne cible. Exemple pour le paiement le plus élevé par méthode et par client :

SELECT
  customer_id,
  MAX(amount) FILTER (WHERE payment_method = 'card')   AS card_max,
  MAX(amount) FILTER (WHERE payment_method = 'cash')   AS cash_max,
  MAX(amount) FILTER (WHERE payment_method = 'bank_transfer') AS bank_transfer_max
FROM payments
GROUP BY customer_id;

Approche 2 — tablefunc.crosstab. Cette extension pivote en une seule opération, mais nécessite des colonnes fixes (les catégories). Exemple par mois :

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  $$
  SELECT date_trunc('month', paid_at)::date AS month, payment_method, MAX(amount) AS max_amount
  FROM payments
  GROUP BY 1,2
  ORDER BY 1,2
  $$,
  $$ VALUES ('card'), ('cash'), ('bank_transfer') $$
) AS ct(month date, card numeric, cash numeric, bank_transfer numeric);

Cette même logique s'applique pour pivoter par client en remplaçant date_trunc par customer_id dans la première requête.

• Comparaison rapide : FILTER offre une grande flexibilité et s'adapte aux catégories dynamiques via SQL généré; crosstab offre de bonnes performances pour des rapports fixes mais exige des colonnes prédéfinies.
• Points pratiques : Vérifier l'ordre des colonnes avec crosstab; gérer les valeurs manquantes avec COALESCE(...,0) si besoin; générer dynamiquement la liste des catégories pour FILTER si elles sont nombreuses.
• Recommandation : Utiliser FILTER pour dashboards dynamiques et crosstab pour exports ou rapports réguliers et contrôlés.

Prêt à appliquer ces patterns pour fiabiliser vos analyses ?

Ces quatre patterns — joins + filters, fonctions fenêtre, aggregation/grouping et pivoting — forment le socle de l’analyse SQL réutilisable. Maîtrisés, ils permettent de résoudre la majorité des besoins analytiques métier, d'améliorer la reproductibilité et de réduire le temps d'investigation. En appliquant ces schémas vous gagnerez en robustesse, vitesse d'exécution et clarté des livrables analytiques. Passez de l'exploration ponctuelle à des pipelines analytiques fiables pour vos décisions.

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 : Logis Hôtel, Yelloh Village, BazarChic, Fédération Française de Football, Texdecor. Dispo pour aider les entreprises => contactez moi.