Quels analytics patterns maîtriser en data science ?

Les analytics patterns essentiels sont un petit nombre de motifs réutilisables (joins, fonctions fenêtre, agrégations, pivot) qui résolvent la majorité des requêtes analytiques (voir PostgreSQL docs). Lisez la suite pour les appliquer immédiatement à vos cas métier.

Quand utiliser join et filtre ?

Pour combiner des tables et restreindre le jeu de résultats, on identifie d’abord la table principale, on joint ensuite les données complémentaires, puis on applique des filtres pour obtenir le sous‑ensemble pertinent.

• Types de jointures et choix. INNER JOIN retourne uniquement les lignes présentes dans les deux tables (préférer pour croiser des entités liées). LEFT JOIN conserve toutes les lignes de la table de gauche et apporte les correspondances à droite (préférer pour détecter des absences ou préserver la table principale). RIGHT JOIN est l’inverse du LEFT JOIN mais est rarement nécessaire si vous pilotez l’ordre des tables.
• Ordre recommandé. Table principale → Jointures (ON pour conditions de jointure) → WHERE (pour filtrer le résultat global) → Projections (SELECT).
• Performance. Indexez systématiquement les colonnes de jointure (FK/PK). Prendre en compte la cardinalité : joindre une table très volumineuse sans filtre préalable coûte cher. Utiliser LIMIT pour itérer lors du debugging. Vérifier la présence d’index couvrants pour éviter des lectures supplémentaires.
• Cas métiers concrets. RH : joindre heures et heures sup pour ne conserver que la période ciblée. Retail : joindre commandes et lignes pour calculer metrics par produit. Streaming : joindre utilisateurs et sessions pour sessions actives par utilisateur.

Exemple SQL

CREATE TABLE films (id INT PRIMARY KEY, title TEXT, duration_min INT);
CREATE TABLE flights (id INT PRIMARY KEY, flight_number TEXT, duration_min INT);

-- Requête : sélectionner films dont la durée ≤ durée d'un vol
SELECT f.id, f.title, f.duration_min
FROM films f
JOIN flights fl ON fl.id = /* flight_id */ 1
WHERE f.duration_min <= fl.duration_min;

• Filtrer dans ON vs WHERE. Mettre un filtre de correspondance dans ON contrôle la relation apportée à la jointure ; mettre le filtre dans WHERE transforme souvent un LEFT JOIN en comportement équivalent à INNER (en filtrant les NULLs).
• Exemple non‑associés. LEFT JOIN + WHERE right.id IS NULL identifie des clients sans commande.

Conseils opérationnels. Toujours inspecter le plan d’exécution avec EXPLAIN ANALYZE. Éviter SELECT *. Limiter les colonnes sélectionnées. Dénormaliser quand les jointures répétées coûtent trop cher en lecture pour des requêtes critiques.

À quoi servent les fonctions fenêtre ?

Les fonctions fenêtre permettent de classer et d’ordonner des lignes au sein de partitions sans réduire la cardinalité, ce qui rend possibles des analyses Top‑N, des classements et des calculs cumulés tout en conservant toutes les lignes d’origine.

• Principe des fenêtres : OVER définit la fenêtre sur laquelle la fonction opère, PARTITION BY segmente les données en groupes, ORDER BY impose l’ordre au sein de chaque partition, et les frame definitions (ROWS/RANGE BETWEEN …) contrôlent la plage de lignes prises en compte pour les fonctions cumulées.
• Différences et usages : ROW_NUMBER() donne un rang unique séquentiel (utile pour top‑N strict), RANK() laisse des sauts en cas d’ex aequo, DENSE_RANK() ne laisse pas de trou entre rangs. Les fonctions analytiques comme SUM() OVER() ou AVG() OVER() calculent des totaux ou moyennes cumulés sans réduire les lignes.
• Séquence recommandée : Partitionner → Ordonner → Appliquer la fonction fenêtre → Filtrer via une CTE ou une sous‑requête pour obtenir le top‑N par partition.
• Exemples métier : Top vendeurs par région, classement d’étudiants, performance de livreurs ordonnée par délai, détection d’anomalies séquentielles (pics, ruptures de tendance).

-- Exemple 1 : Top 3 posts par channel (PostgreSQL)
WITH ranked_posts AS (
  SELECT
    channel_id,
    post_id,
    likes,
    ROW_NUMBER() OVER (PARTITION BY channel_id ORDER BY likes DESC, post_id) AS rn
  FROM posts
)
SELECT channel_id, post_id, likes
FROM ranked_posts
WHERE rn <= 3
ORDER BY channel_id, rn;

-- Exemple 2 : Montrer différence RANK vs DENSE_RANK
SELECT
  region,
  seller_id,
  amount,
  RANK() OVER (PARTITION BY region ORDER BY amount DESC)    AS rnk,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dense_rnk
FROM sales
ORDER BY region, amount DESC;

• Bonnes pratiques : Limiter la taille des partitions pour la performance, indexer les colonnes utilisées en PARTITION BY/ORDER BY, éviter un ORDER BY non déterministe (ajouter une colonne tie‑breaker), et filtrer via CTE pour que les plans restent optimisables.

Comment résumer des données avec GROUP BY ?

GROUP BY agrège les lignes selon des dimensions pour produire des résumés chiffrés (COUNT, SUM, AVG…), indispensable pour rapports et KPI. Pattern central : choisir la dimension → grouper → agréger → filtrer les groupes si besoin.

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

Étapes concrètes avant la requête :

• Choisir la dimension pertinente, par exemple user_id, date ou produit pour donner du sens au résumé.
• Grouper sur cette dimension avec GROUP BY (ou GROUPING SETS/ROLLUP/CUBE pour totaux multiples).
• Agréger avec COUNT, SUM, AVG, MIN, MAX ; utiliser DISTINCT à l’intérieur d’un agrégat pour dédupliquer si nécessaire.
• Filtrer les groupes avec HAVING (par exemple conserver uniquement les groupes avec COUNT >= 1).

Fonctions d’agrégation courantes :

• COUNT(col) compte les lignes non-null ; COUNT(*) compte toutes les lignes.
• SUM(col) additionne les valeurs numériques.
• AVG(col) calcule la moyenne arithmétique.
• MIN(col) et MAX(col) donnent les extrêmes.
• Usage de DISTINCT : SUM(DISTINCT amount) ou COUNT(DISTINCT user_id) pour exclure les doublons.

Totaux hiérarchiques en PostgreSQL :

• ROLLUP produit des sous-totaux puis un total global.
• CUBE génère toutes les combinaisons de totaux pour plusieurs dimensions.
• GROUPING SETS permet de déclarer explicitement les agrégats souhaités.

Exemple ROLLUP — somme par jour puis total :

SELECT date_trunc('day', session_start) AS day,
       SUM(amount) AS total_amount
FROM orders
GROUP BY ROLLUP(date_trunc('day', session_start))
ORDER BY day NULLS LAST;

Découvrez égalementComment automatiser le prétraitement des données en Python ?

Exemples métier rapides :

• E‑commerce : commandes et revenus par client/jour pour détecter top clients.
• SaaS : connexions par utilisateur/semaine pour mesurer engagement.
• Finance : transactions par compte/trimestre pour contrôles et reporting.

Requête complète (schéma minimal puis requête) :

-- Schéma minimal : users(id), sessions(id,user_id,session_start), orders(id,session_id,amount)
SELECT s.user_id,
       date_trunc('day', s.session_start) AS day,
       COUNT(o.id) AS orders_count,
       SUM(o.amount) AS total_amount
FROM sessions s
LEFT JOIN orders o ON o.session_id = s.id
GROUP BY s.user_id, date_trunc('day', s.session_start)
HAVING COUNT(o.id) >= 1;

Bonnes pratiques :

• Pré-agréger dans l’ETL si volumétrie élevée pour réduire coût de calcul en temps réel.
• Traiter les NULLs volontairement (COALESCE si nécessaire) car ils influent sur COUNT et GROUP BY.
• Partitionner les tables temporelles pour accélérer scans et agrégations.

Comment pivotiser des résultats en colonnes ?

Le pivot transforme des lignes en colonnes soit via des agrégations conditionnelles (CASE/FILTER) soit via l’extension tablefunc crosstab pour un résultat compact et lisible en tableau.

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

Deux approches en PostgreSQL sont usuelles : agrégats conditionnels avec FILTER (ou MAX(CASE WHEN … THEN … END)) et l’extension tablefunc avec crosstab().

Je prends un jeu simplifié : payments(city, category, amount).

-- Pivot via FILTER (max par catégorie en colonnes)
SELECT city,
       MAX(amount) FILTER (WHERE category = 'utilities') AS utilities,
       MAX(amount) FILTER (WHERE category = 'rent')      AS rent,
       MAX(amount) FILTER (WHERE category = 'office')    AS office
FROM payments
GROUP BY city;

Pour crosstab, activer l’extension, préparer la source et la liste de catégories, puis définir la signature des colonnes retournées.

CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Source : rowid, category, value (ordre stable requis)
SELECT * FROM crosstab(
  $$ SELECT city, category, MAX(amount) FROM payments GROUP BY city, category ORDER BY 1,2 $$,
  $$ SELECT DISTINCT category FROM payments ORDER BY 1 $$
) AS ct (city text, office numeric, rent numeric, utilities numeric);

Les limitations sont claires : crosstab nécessite une liste fixe de colonnes et un ordre stable, ce qui rend les colonnes dynamiques difficiles. Les agrégats conditionnels sont plus flexibles et acceptent facilement des catégories dynamiques mais deviennent verbeux lorsque les catégories sont nombreuses.

Découvrez égalementQuel est le meilleur cours SQL gratuit avec certificat ?

Pour la performance et la maintenance, privilégier une vue matérialisée quand les calculs sont lourds et les données peu volatiles. Créer des index sur les colonnes de regroupement ou sur la vue matérialisée améliore les temps de réponse, mais nécessite une stratégie d’actualisation (REFRESH MATERIALIZED VIEW).

Gérer des catégories dynamiques passe par du SQL dynamique (construire la requête au runtime) ou par un ETL qui maintient une table de colonnes fixes pour crosstab.

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

Ces quatre patterns — joins + filtres, fonctions fenêtre, agrégations/grouping (rollup) et pivoting — constituent l’essentiel des requêtes analytiques réutilisables. Maîtriser leur séquence d’application, leurs variantes PostgreSQL (FILTER, window frames, ROLLUP, crosstab) et leurs implications de performance vous permet de transformer des besoins métier en requêtes robustes et maintenables. Bénéfice immédiat : gain de temps, requêtes plus fiables et décisions métier mieux informées.

FAQ

Découvrez égalementComment parser efficacement les dates et heures en Python ?

A propos de l’auteur

Franck Scandolera — expert & formateur en tracking server-side, Analytics Engineering, automatisation No/Low Code (n8n) et intégration IA en entreprise. Responsable de l’agence webAnalyste et de l’organisme Formations Analytics. Références clients : Logis Hôtel, Yelloh Village, BazarChic, Fédération Française de Football, Texdecor. Disponible pour aider votre équipe : 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.