Quels patrons analytiques pour le data scientist ?

Les patrons analytiques clés sont joins, fonctions fenêtre, agrégations et pivots, implémentés en SQL PostgreSQL (voir documentation PostgreSQL). Lisez la suite pour séquences actionnables, requêtes-exemples et cas métier concrets pour réutiliser ces motifs immédiatement.

Comment isoler le bon sous-ensemble avec JOIN et FILTER ?

Commencez par identifier la table primaire puis enrichissez-la avec des JOINs, enfin restreignez avec WHERE (avant agrégation) ou HAVING (après agrégation).

Choisir la table primaire réduit le volume parce que l’on part du jeu de lignes que vous devez impérativement conserver, ce qui limite la cardinalité dès le départ. Choisir entre INNER, LEFT et SEMI dépend de l’intention métier et de l’effet sur la cardinalité : INNER élimine les lignes sans correspondance (réduit le volume), LEFT conserve toutes les lignes primaires (utile pour détection d’absents), SEMI (implémenté par EXISTS/IN) teste l’existence sans multiplier les lignes liées.

Bonnes pratiques d’indexation pour PostgreSQL :

• Indexez systématiquement les colonnes de jointure et de filtrage pour des égalités (btree).
• Assurez-vous que les types et collations des colonnes correspondent exactement entre tables pour éviter casts inutiles.
• Maintenez des statistiques à jour avec ANALYZE pour que le planner choisisse les bons plans.
• Évitez les fonctions sur colonnes dans WHERE/JOIN sans index supporté (ex : col::text casse l’index).
• Utilisez des index partiels pour filtres fréquents et BRIN pour colonnes temporelles très larges.

CREATE TABLE flight_schedule (flight_id bigint PRIMARY KEY, duration_minutes int);
CREATE TABLE entertainment_catalog (movie_id bigint PRIMARY KEY, title text, duration_minutes int);

INSERT INTO flight_schedule VALUES (123, 120);
INSERT INTO entertainment_catalog VALUES (1, 'Short Comedy', 90), (2, 'Epic Movie', 150);

SELECT f.flight_id, e.movie_id, e.title, e.duration_minutes
FROM flight_schedule f
JOIN entertainment_catalog e ON e.duration_minutes 

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

Exemple WHERE vs HAVING : filtrage avant agrégation restreint les lignes d'entrée, HAVING filtre les groupes. Exemple :

-- Filtrage avant agrégation (date range)
SELECT customer_id, COUNT(*) AS orders, SUM(total) AS total_spent
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY customer_id
HAVING COUNT(*) > 3 AND SUM(total) > 100;

Étapes recommandées :

• 1) Définir la table primaire et le périmètre temporel.
• 2) Ajouter JOINs nécessaires en choisissant INNER/LEFT/EXISTS selon l'intention.
• 3) Appliquer filtres (WHERE avant agrégation, HAVING après).
• 4) Vérifier cardinalités (EXPLAIN), ajouter indexes ciblés.
• 5) Valider le résultat sur un échantillon représentatif.

Cas d'usage précis : RH pour heures sup non facturées, Retail pour produits présents dans commandes, Streaming pour recommandations compatibles avec la durée de session.

Quand utiliser les fonctions fenêtre pour classer et ordonner ?

Les fonctions fenêtre servent à calculer des classements, des rangs et des mesures sur des partitions de données sans réduire le nombre de lignes retournées. Elles utilisent trois éléments clés : PARTITION BY pour définir les groupes, ORDER BY pour ordonner au sein du groupe, et la frame specification (par exemple ROWS BETWEEN ...) pour fixer la plage sur laquelle la fonction opère.

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

Exemple concret pour retrouver les 3 meilleurs posts par canal selon le nombre de likes :

WITH ranked AS (
  SELECT post_id, channel_id, likes,
         ROW_NUMBER() OVER (PARTITION BY channel_id ORDER BY likes DESC) AS rn
  FROM posts
)
SELECT post_id, channel_id, likes
FROM ranked
WHERE rn 

Choix entre ROW_NUMBER() et RANK()

ROW_NUMBER() attribue un numéro unique même en cas d'ex-æquo, ce qui garantit exactement N lignes par partition si vous filtrez par rn ≤ N. RANK() donne le même rang aux ex-æquo et saute des numéros ensuite (par exemple 1,1,3), utile quand vous voulez conserver l'équité. DENSE_RANK() ressemble à RANK() mais ne saute pas de numéro (1,1,2). NTILE(n) segmente en n quantiles.

Quelques cas métiers typiques :

• Top performers par région : Classer les commerciaux par CA par région pour listes de primes.
• Classement étudiants : Gérer les ex-æquo différemment selon la politique d'établissement.
• Logistique et priorités : Prioriser les livraisons par zone et SLA sans agrégation globale.

Découvrez égalementQuel cours SQL gratuit avec certificat choisir pour avancer ?

Variantes utiles : calculs cumulatifs comme SUM() OVER (PARTITION BY region ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) pour suivre l'évolution cumulative.

Conseils de performance et limites : Les grandes partitions peuvent consommer beaucoup de mémoire et générer des fichiers temporaires. Augmenter work_mem (Postgres) aide, mais mieux vaut pré-filtrer ou pré-agréger quand c'est possible. Indexer channel_id et likes (ou un index couvrant) réduit les scans. Pour scaler, découper la requête par bucket de partition, utiliser des tables matérialisées ou traiter en batch.

Comment résumer les données avec GROUP BY et agrégats ?

GROUP BY permet de réduire des lignes en dimensions analytiques pour produire des agrégats (COUNT, SUM, AVG, MIN, MAX) et transformer des événements bruts en métriques exploitables.

Exemple concret : identifier les utilisateurs ayant démarré une session et passé une commande le même jour, puis compter les commandes et sommer la valeur.

-- schémas réduits
CREATE TABLE sessions (session_id bigint PRIMARY KEY, user_id bigint, session_date date);
CREATE TABLE order_summary (order_id bigint PRIMARY KEY, user_id bigint, order_date date, amount numeric);

SELECT s.user_id, s.session_date,
       COUNT(o.order_id) AS orders_count,
       SUM(o.amount) AS total_amount
FROM sessions s
JOIN order_summary o ON o.user_id = s.user_id AND o.order_date = s.session_date
GROUP BY s.user_id, s.session_date
HAVING COUNT(o.order_id) > 0;

Découvrez égalementQuelles évolutions pour la confidentialité des analytics en 2026 ?

Choix des dimensions : sélectionner les colonnes qui définissent l’analyse (ici user_id et session_date).

Impact sur la cardinalité : chaque combinaison unique des dimensions devient une ligne agrégée, ce qui peut réduire fortement le volume mais aussi exploser si vous groupez sur des colonnes à haute cardinalité (identifiants uniques, timestamps fins). J'insiste sur le coût mémoire/CPU lors d'agrégations en mémoire et sur la nécessité d'indexes ou de pré-agrégation pour gros volumes.

Roll-up et subtotaux : GROUPING SETS, ROLLUP et CUBE permettent d’obtenir subtotaux et totaux généraux sans multiplier les requêtes. Exemple rapide : ROLLUP(user_id, session_date) produit (user_id,session_date), (user_id), () pour obtenir subtotaux et total global.

Agrégats conditionnels : PostgreSQL propose FILTER pour agréger selon une condition sans joindre ou filtrer en amont.

SELECT user_id,
       COUNT(*) FILTER (WHERE order_date = CURRENT_DATE) AS orders_today,
       SUM(amount) FILTER (WHERE order_date = CURRENT_DATE) AS revenue_today
FROM order_summary
GROUP BY user_id;

Découvrez égalementQuels outils d’analytics business pour booster votre performance ?

Cas métiers : e‑commerce (commandes/revenu par client/jour), SaaS (connexions par semaine), finance (transactions par trimestre).

Comment pivoter des lignes en colonnes dans PostgreSQL ?

Pour pivoter des lignes en colonnes dans PostgreSQL, deux approches principales existent : les agrégations conditionnelles (CASE ou FILTER) pour la simplicité, et la fonction crosstab (extension tablefunc) pour des pivots plus performants ou dynamiques.

Exemple SQL simple avec agrégation conditionnelle pour obtenir le paiement le plus élevé par mode et par utilisateur :

SELECT user_id,
       MAX(CASE WHEN payment_method = 'card' THEN amount END) AS max_card,
       MAX(CASE WHEN payment_method = 'paypal' THEN amount END) AS max_paypal,
       MAX(CASE WHEN payment_method = 'bank' THEN amount END) AS max_bank
FROM payments
GROUP BY user_id;

Variante utilisant FILTER (plus lisible quand disponible) :

SELECT user_id,
       MAX(amount) FILTER (WHERE payment_method = 'card') AS max_card,
       MAX(amount) FILTER (WHERE payment_method = 'paypal') AS max_paypal
FROM payments
GROUP BY user_id;

Pour des pivots dynamiques ou très volumineux, crosstab (tablefunc) peut être préférable. Activation de l'extension :

CREATE EXTENSION IF NOT EXISTS tablefunc;

Exemple minimal crosstab : la requête source doit retourner (rowid, category, value) et la sortie nécessite une définition explicite des colonnes.

SELECT *
FROM crosstab(
  'SELECT user_id, payment_method, amount FROM payments ORDER BY 1,2'
) AS ct(user_id int, card numeric, paypal numeric, bank numeric);

Conseils pratiques : privilégier CASE/FILTER pour la simplicité, l'absence d'extensions et des jeux de colonnes connus. Préférer crosstab pour de meilleures performances sur gros volumes ou quand on construit dynamiquement les colonnes (nécessite SQL dynamique et définition stricte des types).

Précautions : s'assurer que la colonne valeur a un type constant, gérer les NULLs avec COALESCE si nécessaire, et vérifier l'ordre dans la requête source de crosstab pour éviter des colonnes mal alignées.

Exemple métier complet :

CREATE TABLE payments(user_id int, payment_method text, amount numeric);
INSERT INTO payments VALUES (1,'card',100),(1,'paypal',80),(2,'card',50),(2,'bank',120);
-- Pivot avec CASE
SELECT user_id,
       MAX(CASE WHEN payment_method='card' THEN amount END) AS max_card,
       MAX(CASE WHEN payment_method='paypal' THEN amount END) AS max_paypal,
       MAX(CASE WHEN payment_method='bank' THEN amount END) AS max_bank
FROM payments
GROUP BY user_id;

Prêt à appliquer ces patrons analytiques dans vos projets ?

Les patrons analytiques—join + filter, fonctions fenêtre, agrégation/grouping et pivot—forment un petit jeu d'outils réutilisables qui couvrent la majorité des besoins d'analyse SQL. En suivant les séquences proposées, en testant les requêtes sur échantillons et en appliquant les bonnes pratiques d'indexation et de configuration PostgreSQL, vous gagnerez en fiabilité et en vitesse d'exécution. Le bénéfice immédiat pour vous : produire des insights exploitables plus vite, avec moins d'essais-erreurs, et répliquer ces motifs sur différents cas métier.

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 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.