Quels motifs analytiques un data scientist doit maîtriser ?

Les motifs analytiques clés — joins, fonctions fenêtrées, agrégations, pivot — résolvent la majorité des questions métiers via SQL/PostgreSQL (voir documentation PostgreSQL) et exercices type StrataScratch. Lisez la suite pour des exemples exploitables et des requêtes prêtes à adapter à vos jeux de données.

Comment trouver le bon sous-ensemble avec JOINs et filtres ?

Identifier d’abord la table principale, joindre ensuite les sources complémentaires, et filtrer le plus tôt possible pour réduire le volume : telle est la règle simple qui répond directement à la question.

Principe du pattern JOINs + Filters :

• Choix de la table principale : privilégiez la table qui contient la clé métier de votre analyse pour limiter le scope dès le départ.
• Joindre les sources complémentaires : utilisez des JOIN (INNER pour intersection, LEFT pour conserver la base) pour enrichir les lignes pertinentes.
• Filtrer tôt : appliquer WHERE sur la table principale avant la jointure réduit le nombre de lignes combinées et accélère l’opération.
• Limiter les colonnes : sélectionner uniquement les colonnes nécessaires évite le coût I/O et réseau.

Movie Duration Match (schéma simplifié) : flight_schedule(flight_id, duration_minutes) et entertainment_catalog(movie_id, title, duration_minutes).

WITH flight AS (
  SELECT flight_id, duration_minutes AS flight_duration
  FROM flight_schedule
  WHERE flight_id = 101
)
SELECT e.movie_id,
       e.title,
       e.duration_minutes
FROM entertainment_catalog e
JOIN flight f
  ON e.duration_minutes <= f.flight_duration
ORDER BY e.duration_minutes DESC
LIMIT 20;

Découvrez égalementComment nettoyer et prétraiter des données en Python ?

Conseils de performance :

• Filtrer avant JOIN : appliquer WHERE sur flight_schedule (ou une première CTE) pour réduire la cardinalité joinée.
• Indexer les colonnes de jointure et de filtre : créer index sur flight_schedule.flight_id et entertainment_catalog.duration_minutes si les filtres sont fréquents.
• Éviter SELECT * : sélectionner explicitement évite le transfert inutile de données et améliore l'utilisation du cache.

Cas d'usage métier :

• RH : Trouver les employés éligibles à une formation en joignant le planning et les compétences internes filtrées par disponibilité.
• Retail : Associer promotions et stocks en filtrant les stores actifs avant de joindre le catalogue produit.
• Streaming : Proposer des films compatibles avec la durée de session utilisateur en joignant le catalogue aux métadonnées de session.

Quand utiliser les fonctions fenêtrées pour classer ?

Réponse brève : pour classer des éléments au sein de partitions (par canal, région, etc.), privilégiez les fonctions fenêtrées (ROW_NUMBER, RANK, DENSE_RANK) avec PARTITION BY et ORDER BY.

Pattern pratique : créer d'abord une colonne de rang calculée via OVER(PARTITION BY ... ORDER BY ...), puis filtrer ce rang dans une requête enveloppante (CTE) ou une sous-requête pour limiter aux meilleurs par partition.

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

Exemple PostgreSQL — Top Posts Per Channel :

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

Explication de l'encapsulation : Le CTE permet de calculer les rangs une seule fois et de filtrer ensuite facilement sur rnk ; alternativement, utiliser une sous-requête SELECT ... FROM (SELECT ..., RANK() OVER (...) AS rnk FROM posts) t WHERE rnk <= 3 fonctionne pareillement.

Comparaison RANK vs DENSE_RANK vs ROW_NUMBER :

• RANK retourne des sauts si égalité des clés de tri (Exemple : likes 100,100,90 → RANK = 1,1,3).
• DENSE_RANK compresse les rangs sans sauts en cas d'égalité (Exemple : likes 100,100,90 → DENSE_RANK = 1,1,2).
• ROW_NUMBER donne un numéro unique par ligne, utile pour découper strictement N éléments (Exemple : likes 100,100,90 → ROW_NUMBER = 1,2,3).

Bonnes pratiques :

• Trier sur des colonnes stables et déterministes pour éviter des résultats non reproductibles.
• Gérer explicitement les égalités selon l'objectif métier (préférer DENSE_RANK si on veut N positions réelles, ROW_NUMBER si on veut exactement N lignes).
• Éviter ORDER BY non déterministe sans clé secondaire (ajouter created_at ou post_id pour stabilité).

Découvrez égalementQuel cours SQL gratuit offre un certificat utile pour votre carrière ?

Usages métier : Classement de meilleurs vendeurs par zone, meilleurs étudiants par promotion, top itinéraires par hub logistique.

Comment agréger les données avec le pattern roll-up ?

Le pattern Roll-Up consiste à choisir la bonne dimension de groupement, dédupliquer si nécessaire, puis appliquer COUNT/SUM et filtrer avec HAVING pour isoler les cas d'intérêt.

Principe appliqué au cas Same-Day Orders. Schémas simplifiés : sessions(session_id, user_id, session_date) et orders(order_id, user_id, order_date, amount).

Stratégie. Dédupliquer les sessions par couple user_id+session_date, agréger les commandes par user_id+order_date, joindre les deux jeux et regrouper par user_id, session_date pour obtenir le nombre de commandes et le chiffre d'affaires le même jour.

WITH dedup_sessions AS (
  SELECT user_id, session_date
  FROM sessions
  GROUP BY user_id, session_date
),
orders_day AS (
  SELECT user_id, order_date, COUNT(*) AS orders_cnt, SUM(amount) AS orders_amount
  FROM orders
  GROUP BY user_id, order_date
)
SELECT s.user_id,
       s.session_date,
       COUNT(o.orders_cnt) AS orders_count,
       COALESCE(SUM(o.orders_amount),0) AS total_amount
FROM dedup_sessions s
LEFT JOIN orders_day o
  ON s.user_id = o.user_id AND s.session_date = o.order_date
GROUP BY s.user_id, s.session_date
HAVING COUNT(o.orders_cnt) >= 1;

Découvrez égalementQu'est-ce que le Dummy Variable Trap en Machine Learning ?

Choix dimensionnels. Utiliser user_id + date granularity (jour/semaine/trimestre) en fonction de la question métier. Utiliser la date (sans horodatage) pour des mesures journalières afin d'éviter le fractionnement inutile.

Pièges courants. Double comptage si on agrège sans dédupliquer les sessions ou si on joint avant d'agréger. Décalages liés aux fuseaux horaires si session_date et order_date ne sont pas normalisés (préférer UTC ou une conversion explicite).

Optimisations. Créer des index sur (user_id, session_date) et (user_id, order_date). Pré-agréger les orders si le volume est élevé. Utiliser partitions par date sur les tables volumineuses.

• Trois usages métier pertinents : E‑commerce — chiffre d'affaires par client/jour.
• SaaS — connexions par utilisateur/semaine pour mesurer adoption.
• Finance — transactions par compte/trimestre pour conformité et reporting.

Comment transformer des lignes en colonnes pour les rapports ?

Réponse brève : on pivote les données soit via la fonction crosstab (tablefunc) de PostgreSQL, soit via agrégations conditionnelles (MAX(CASE ...)).

Découvrez égalementQuels sont les vrais atouts des business analytics tools aujourd’hui ?

La transformation de lignes en colonnes sert à présenter un indicateur par catégorie (département, année...) sur une seule ligne par clé (ici city).

Méthode 1 — Agrégations conditionnelles (portable et sans extension)

La technique consiste à créer une colonne par département en utilisant MAX(CASE WHEN ... THEN amount END).

SELECT city,
  MAX(CASE WHEN department = 'Police' THEN amount END) AS police_max,
  MAX(CASE WHEN department = 'Fire' THEN amount END) AS fire_max,
  MAX(CASE WHEN department = 'Public Works' THEN amount END) AS public_works_max
FROM payments
GROUP BY city;

Pour l'exemple Highest Payment from the City of San Francisco :

SELECT
  MAX(CASE WHEN department = 'Police' THEN amount END) AS police_max,
  MAX(CASE WHEN department = 'Fire' THEN amount END) AS fire_max,
  MAX(CASE WHEN department = 'Public Works' THEN amount END) AS public_works_max
FROM payments
WHERE city = 'San Francisco';

Méthode 2 — crosstab (tablefunc) (pivot natif, plus performant pour large volumétrie)

La fonction crosstab prend une source (rowid, category, value) et une liste de catégories. L'extension tablefunc doit être activée.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  $$SELECT city, department, amount FROM payments ORDER BY 1,2$$,
  $$SELECT DISTINCT department FROM payments ORDER BY 1$$
) AS ct(city text, police numeric, fire numeric, "Public Works" numeric);

• Avantages agrégations : Portable, pas besoin d'extension, facile à générer dynamiquement depuis l'application.
• Inconvénients agrégations : Requiert autant de colonnes statiques que de catégories, peut devenir verbeux pour 50+ catégories.
• Avantages crosstab : Plus rapide sur de grandes tables, conçu pour pivoter efficacement.
• Inconvénients crosstab : Nécessite tablefunc et une définition de sortie statique (ou SQL dynamique), moins portable.

Conseils pour rapports automatisés et BI

Automatiser la récupération de la liste des départements via une requête metadata et générer dynamiquement la SQL de pivot si les catégories évoluent. Préférer de laisser l'outil BI (Tableau, Power BI) pivoter si les colonnes sont très dynamiques. Materialiser le résultat (vue matérialisée) si les calculs sont lourds et les rapports fréquents.

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

Les quatre patterns — JOINs+filtres, fonctions fenêtrées, agrégations/roll-up et pivot — forment l'ossature des analyses SQL robustes. Maîtrisez-les en PostgreSQL pour résoudre la majorité des questions métier, optimiser les performances et produire des rapports exploitables. En appliquant ces motifs, vous réduisez le temps d'analyse et augmentez la fiabilité de vos insights : un gain direct pour vos décisions opérationnelles et stratégiques.

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.

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.