Quels analytics patterns maîtriser en data science ?

Les quatre patterns SQL essentiels sont joins+filters, window functions, aggregation/grouping (roll‑up) et pivoting, capables de résoudre la majorité des analyses décisionnelles (voir PostgreSQL documentation). Suivez les séquences pratiques, exemples PostgreSQL et cas métier concrets pour les appliquer immédiatement.

Comment isoler le bon sous‑ensemble avec JOIN et WHERE

Isoler le bon sous‑ensemble commence par choisir la table principale, puis joindre les tables de contexte et enfin filtrer avant d’agréger. J’insiste sur ce point parce que filtrer après agrégation ou joindre de travers conduit à des biais, des doublons et des coûts inutiles.

Séquence recommandée et explication :

La séquence suivante minimise les erreurs et optimise les performances :

• Choisir la table primaire — Celle qui représente l’entité analysée (ex : commandes, employés, vols).
• Joindre les contextes nécessaires — Utiliser INNER JOIN si l’existence est requise, LEFT JOIN si l’absence est informative.
• Appliquer des filtres sur colonnes non agrégées (WHERE) — Filtrer avant agrégation pour réduire les données traitées.
• Agréger si besoin et filtrer résultats agrégés (HAVING) — Utiliser HAVING uniquement pour conditions sur agrégats.

Read moreComment utiliser VLOOKUP pour fusionner des données ?

Exemples métier concrets :

• RH — Identifier les heures sup en joignant feuille de temps + contrats et filtrant par période avant d’agréger.
• Retail — Extraire les produits d’une commande en joignant commandes → lignes de commande → produits puis filtrant par statut.
• Streaming — Suggérer des films adaptés à la durée d’un vol en comparant duration film ≤ durée vol et en triant par popularité.

Exemple PostgreSQL : recommander films compatibles avec un vol

CREATE TABLE movies (id SERIAL PRIMARY KEY, title TEXT, duration INT, popularity NUMERIC);
CREATE TABLE flights (id SERIAL PRIMARY KEY, user_id INT, duration INT);

-- Retourne jusqu'à 10 films dont la durée est <= durée du vol, triés par popularité
SELECT m.id, m.title, m.duration, m.popularity
FROM flights f
JOIN movies m ON m.duration <= f.duration
WHERE f.id = 123 -- vol ciblé
ORDER BY m.popularity DESC
LIMIT 10;

Alternatives, astuces et pièges :

EXISTS évite la multiplication de lignes quand seule l'existence compte. J'encourage d'appliquer les filtres avant les JOIN pour réduire I/O. Indexez les colonnes de jointure et de filtre. Méfiez-vous des JOIN multiples qui multiplient les lignes; utilisez DISTINCT ou agrégations correctement pour corriger.

Quand utiliser les fonctions fenêtre pour classer

Read moreComment utiliser COUNT et COUNTIF dans Excel efficacement ?

Quand il faut classer et extraire un top N par groupe sans perdre les colonnes de détail, utilisez les fonctions fenêtre.

J'utilise systématiquement ROW_NUMBER, RANK et DENSE_RANK pour ces besoins, car elles permettent de partitionner, trier et attribuer un rang sans agrégation destructrice.

• Séquence essentielle : Partitionner (PARTITION BY), Ordonner (ORDER BY), Appliquer la fonction de fenêtre, Encapsuler le tout dans une CTE pour filtrer le top N.
• Cas métier typiques : Top vendeurs par région, Meilleurs étudiants par classe, Top articles par canal.

Différences pratiques :

• ROW_NUMBER donne un rang unique par ligne (ordinal). Utile quand on veut exactement N lignes, même en présence d'ex æquo.
• RANK donne le même rang aux ex æquo mais crée des trous (gaps) dans les rangs suivants. Utile pour dire « ex æquo au rang 1, suivant = rang 3 ».
• DENSE_RANK donne le même rang aux ex æquo sans trous. Utile pour classement où les rangs doivent rester consécutifs.

Exemple PostgreSQL complet (table posts(id, channel_id, likes, created_at)) :

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

Read moreComment la data analytics révolutionne-t-elle la sécurité en entrepôt ?

Exemple montrant RANK vs DENSE_RANK pour gérer les ex æquo :

SELECT id, channel_id, likes,
  RANK() OVER (PARTITION BY channel_id ORDER BY likes DESC)    AS r_rank,
  DENSE_RANK() OVER (PARTITION BY channel_id ORDER BY likes DESC) AS d_rank
FROM posts
ORDER BY channel_id, likes DESC;

Performance et alternatives :

• Penser à un index couvrant (channel_id, likes DESC, created_at) pour accélérer ORDER BY dans la fenêtre.
• Évaluer la taille des partitions : les fonctions fenêtre traitent généralement toutes les lignes de la partition avant filtrage, donc coût mémoire/CPU.
• Alternative pour top N global : FETCH FIRST ... WITH TIES (Postgres 13+). Pour top-1 par groupe, DISTINCT ON(channel_id) ou LATERAL JOIN + LIMIT peuvent être plus rapides selon le cas.

Comment agréger par dimensions avec le roll up

Le roll-up consiste à grouper par une ou plusieurs dimensions puis appliquer des agrégats (COUNT, SUM) pour résumer les données et détecter tendances ou outliers.

Choix de la dimension et séquence opérationnelle importante pour obtenir un résumé pertinent :

• Choisir la dimension analytique : Sélectionner la granularité utile (jour, semaine, client, produit).
• GROUP BY : Grouper par la ou les dimensions choisies.
• Appliquer fonctions d'agrégation : Utiliser COUNT, SUM, AVG, MAX selon le besoin.
• HAVING pour conditions sur agrégats : Filtrer les groupes (par exemple COUNT > 0, SUM > seuil).

Read moreQu'est-ce que le Dummy Variable Trap en Machine Learning

Cas métier courants : e‑commerce (commandes et CA par client/jour), SaaS (connexions par semaine), finance (transactions par trimestre).

Exemple PostgreSQL concret : identifier les utilisateurs ayant lancé une session et passé une commande le même jour, avec COUNT(orders) et SUM(amount).

WITH session_days AS (
  SELECT DISTINCT user_id, started_at::date AS day
  FROM sessions
)
SELECT sd.user_id,
       sd.day,
       COUNT(o.id) AS orders_count,
       SUM(o.amount) AS total_amount
FROM session_days sd
JOIN orders o
  ON o.user_id = sd.user_id
  AND o.ordered_at::date = sd.day
GROUP BY sd.user_id, sd.day
HAVING COUNT(o.id) > 0
ORDER BY sd.user_id, sd.day;

Variante avec ROLLUP/GROUPING SETS : ROLLUP(user_id, day) produit des sous-totaux hiérarchiques (par user, puis global), tandis que GROUPING SETS permet de définir explicitement les combinaisons d'agrégats.

SELECT user_id::text,
       day::text,
       COUNT(id) AS orders_count,
       SUM(amount) AS total_amount
FROM orders
GROUP BY ROLLUP(user_id, day);

Implications performance : Cardinalité élevée alourdit le GROUP BY, privilégier index sur (user_id, ordered_at) ou index fonctionnel (ordered_at::date), utiliser agrégations partielles (map-reduce) ou materialized views pour pré-agréger si les requêtes sont fréquentes et le délai d'actualisation acceptable.

Read moreQuels sont les 7 meilleurs outils Python ETL pour Data Engineering ?

Tableau synthèse (étapes, SQL snippet, cas métier) :

Comment transformer des lignes en colonnes avec pivot

Je détaille comment transformer des lignes en colonnes (pivot) en PostgreSQL, quand utiliser les agrégats conditionnels et quand recourir à tablefunc.crosstab.

• Agrégats conditionnels : Utilisez SUM(CASE WHEN ...) ou SUM(...) FILTER(WHERE ...) pour produire des colonnes par catégorie sans extension.
• Extension tablefunc.crosstab : Utilisez crosstab pour des tableaux croisés plus performants quand les colonnes sont connues à l'avance.

• Cas métier concret : Tableau croisé des paiements par mois et méthode (pivot mois x méthode).
• Cas métier concret : Comparatif de chiffre d'affaires (CA) par canal marketing.
• Cas métier concret : Rapport de conversion par étape (funnel) pivoté par étape ou par segment.

Exemple A — Agrégats conditionnels (valeur maximale par méthode sur une période) :

SELECT
  date_trunc('month', paid_at) AS month,
  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') AS bank_max
FROM payments
WHERE paid_at BETWEEN '2025-01-01' AND '2025-03-31'
GROUP BY 1
ORDER BY 1;

Exemple B — crosstab (nécessite l'extension tablefunc, colonnes fixes) :

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT to_char(paid_at, ''YYYY-MM'') AS mon, payment_method, SUM(amount)
   FROM payments
   GROUP BY 1,2
   ORDER BY 1,2'
) AS ct(mon text, card numeric, cash numeric, bank numeric);

Avantages/Inconvénients :

• Agrégats conditionnels : Lisibilité élevée, portable, supporte colonnes dynamiques via SQL dynamique, mais les requêtes peuvent devenir verbeuses pour beaucoup de colonnes.
• Crosstab : Très efficace pour des pivots larges et lecture optimisée, mais nécessite de connaître les colonnes en avance et attention à la gestion des NULLs et des performances si données volumineuses.

Format final HTML requis : Utiliser les balises strong, p, ul, li, table, tbody, tr, td et inclure les requêtes SQL PostgreSQL lisibles dans des balises p ou table (ou dans <pre class="wp-block-code"><code> pour le code).

Prêt à appliquer ces patterns à vos données ?

Ces quatre patterns — joins+filters, window functions, aggregation/grouping (roll‑up) et pivoting — couvrent la plupart des besoins analytiques en SQL. Maîtrisez la séquence de chaque pattern, testez les exemples PostgreSQL fournis et adaptez‑les à vos schémas. Vous gagnerez en rapidité d'analyse et en robustesse des rapports, et réduirez temps d'investigation et erreurs. Bénéfice clair : produire des insights actionnables plus vite et à moindre coût.

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 d'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

⭐ Expert et formateur en Tracking avancé, Analytics Engineering et Automatisation IA (n8n, Make) ⭐

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

Mon terrain de jeu :

• Data & Analytics engineering : tracking propre RGPD, entrepôt de données (GTM server, BigQuery…), modèles (dbt/Dataform), dashboards décisionnels (Looker, SQL, Python).
• Automatisation IA des taches Data, Marketing, RH, compta etc : conception de workflows intelligents robustes (n8n, Make, 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.

Résumer ce contenu avec :