---
jupytext:
  text_representation:
    extension: .md
    format_name: myst
    format_version: 0.13
    jupytext_version: 1.16.0
kernelspec:
  name: python3
  display_name: Python 3
---

# Agrégation et groupement

Les requêtes vues jusqu'ici retournent des lignes individuelles. L'agrégation change de perspective : elle **résume** un ensemble de lignes en une seule valeur. C'est le fondement des rapports, tableaux de bord et analyses statistiques.

```{code-cell} python
:tags: [hide-input]

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
import seaborn as sns

sns.set_theme(style="whitegrid", palette="muted", font_scale=1.1)
```

## Fonctions d'agrégat

```{prf:definition}
:label: ch06-def-agregation
Une **fonction d'agrégat** prend en entrée un ensemble de valeurs (une colonne sur plusieurs lignes) et retourne une valeur scalaire unique : un compte, une somme, une moyenne, un extremum ou une concaténation.
```

Les fonctions d'agrégat standard SQL sont :

| Fonction | Rôle | Ignore NULL ? |
|---|---|---|
| `COUNT(*)` | Nombre de lignes | Non |
| `COUNT(col)` | Nombre de valeurs non nulles | Oui |
| `SUM(col)` | Somme | Oui |
| `AVG(col)` | Moyenne arithmétique | Oui |
| `MIN(col)` | Valeur minimale | Oui |
| `MAX(col)` | Valeur maximale | Oui |
| `GROUP_CONCAT(col)` | Concaténation (SQLite) | Oui |

```{prf:remark}
:label: ch06-rem-count-star
`COUNT(*)` compte **toutes** les lignes, y compris celles avec des NULL. `COUNT(colonne)` ne compte que les lignes où la colonne est non nulle. Ces deux formes donnent donc des résultats différents dès qu'il y a des valeurs manquantes.
```

Créons le jeu de données qui servira tout au long du chapitre.

```{code-cell} python
conn = sqlite3.connect(":memory:")

conn.executescript("""
CREATE TABLE departements (
    id      INTEGER PRIMARY KEY,
    nom     TEXT NOT NULL
);

CREATE TABLE employes (
    id            INTEGER PRIMARY KEY,
    nom           TEXT NOT NULL,
    departement   INTEGER REFERENCES departements(id),
    salaire       REAL,
    annee_entree  INTEGER
);

CREATE TABLE ventes (
    id          INTEGER PRIMARY KEY,
    employe_id  INTEGER REFERENCES employes(id),
    montant     REAL NOT NULL,
    trimestre   INTEGER NOT NULL,
    annee       INTEGER NOT NULL
);

INSERT INTO departements VALUES
    (1,'Informatique'),(2,'Marketing'),(3,'Finance'),(4,'RH');

INSERT INTO employes VALUES
    (1,'Alice',   1, 62000, 2018),
    (2,'Bob',     1, 58000, 2020),
    (3,'Carla',   2, 52000, 2019),
    (4,'David',   2, 49000, 2021),
    (5,'Eva',     3, 71000, 2017),
    (6,'Fabien',  3, 68000, 2019),
    (7,'Gina',    4, 45000, 2022),
    (8,'Hugo',    1, 55000, 2021),
    (9,'Iris',    NULL, NULL, 2023);   -- salaire inconnu, dept inconnu

INSERT INTO ventes VALUES
    (1,1,12400,1,2024),(2,1,15200,2,2024),(3,1,11000,3,2024),
    (4,2, 9800,1,2024),(5,2,10500,2,2024),
    (6,3,22000,1,2024),(7,3,19500,2,2024),(8,3,21000,3,2024),
    (9,4,17000,1,2024),(10,4,14000,2,2024),
    (11,5,31000,1,2024),(12,5,28500,2,2024),(13,5,33000,3,2024),
    (14,6,26000,1,2024),(15,6,29000,2,2024),
    (16,7, 5000,1,2024),(17,7, 6200,2,2024),
    (18,8,13000,1,2024),(19,8,11500,3,2024);
""")

# Vérification des tables
pd.read_sql("SELECT * FROM employes", conn)
```

```{code-cell} python
# Fonctions d'agrégat simples sur la table employes
pd.read_sql("""
SELECT
    COUNT(*)          AS nb_lignes,
    COUNT(salaire)    AS nb_salaires_renseignes,
    ROUND(AVG(salaire),2) AS salaire_moyen,
    MIN(salaire)      AS salaire_min,
    MAX(salaire)      AS salaire_max,
    ROUND(SUM(salaire),2) AS masse_salariale
FROM employes
""", conn)
```

```{prf:example}
:label: ch06-ex-group-concat
`GROUP_CONCAT` permet d'agréger des chaînes. On peut personnaliser le séparateur avec un second argument.
```

```{code-cell} python
pd.read_sql("""
SELECT
    d.nom                                         AS departement,
    GROUP_CONCAT(e.nom, ', ')                     AS membres
FROM employes e
JOIN departements d ON e.departement = d.id
GROUP BY d.id, d.nom
ORDER BY d.nom
""", conn)
```

## GROUP BY — regrouper les lignes

```{prf:definition}
:label: ch06-def-group-by
La clause **`GROUP BY`** divise les lignes d'une table en groupes partageant les mêmes valeurs pour les colonnes spécifiées. Chaque groupe est ensuite réduit à une seule ligne par les fonctions d'agrégat.
```

La règle fondamentale est la suivante :

```{prf:theorem}
:label: ch06-thm-group-by-regle
Dans une requête avec `GROUP BY`, toute colonne du `SELECT` doit être soit :
- une colonne listée dans le `GROUP BY`,
- soit le résultat d'une fonction d'agrégat.

Toute autre colonne référencée dans le `SELECT` est une erreur logique (résultat non déterministe).
```

```{code-cell} python
# Statistiques par département
pd.read_sql("""
SELECT
    d.nom                         AS departement,
    COUNT(e.id)                   AS nb_employes,
    ROUND(AVG(e.salaire), 2)      AS salaire_moyen,
    MIN(e.salaire)                AS salaire_min,
    MAX(e.salaire)                AS salaire_max,
    ROUND(SUM(e.salaire), 2)      AS masse_salariale
FROM departements d
LEFT JOIN employes e ON e.departement = d.id
GROUP BY d.id, d.nom
ORDER BY masse_salariale DESC
""", conn)
```

```{prf:remark}
:label: ch06-rem-multi-colonnes
On peut grouper sur **plusieurs colonnes** simultanément. Le groupe est alors défini par la combinaison unique de valeurs de toutes les colonnes listées dans `GROUP BY`.
```

```{code-cell} python
# Ventes totales par employé et par trimestre
pd.read_sql("""
SELECT
    e.nom           AS employe,
    v.trimestre,
    ROUND(SUM(v.montant), 2) AS total_ventes
FROM ventes v
JOIN employes e ON v.employe_id = e.id
GROUP BY e.id, e.nom, v.trimestre
ORDER BY e.nom, v.trimestre
""", conn)
```

## HAVING — filtrer les groupes

```{prf:definition}
:label: ch06-def-having
La clause **`HAVING`** filtre les groupes **après** que `GROUP BY` les a formés. Elle s'applique donc sur des valeurs agrégées, contrairement à `WHERE` qui filtre les lignes individuelles **avant** tout regroupement.
```

```{prf:theorem}
:label: ch06-thm-where-vs-having
Ordre d'exécution logique d'une requête SQL :

1. `FROM` / `JOIN` — constitution des données sources
2. `WHERE` — filtrage des lignes individuelles
3. `GROUP BY` — regroupement
4. Calcul des fonctions d'agrégat
5. `HAVING` — filtrage des groupes
6. `SELECT` — projection des colonnes
7. `ORDER BY` — tri
8. `LIMIT` / `OFFSET` — pagination

`WHERE` ne peut donc pas référencer une valeur agrégée ; c'est le rôle de `HAVING`.
```

```{code-cell} python
# Départements dont la masse salariale dépasse 120 000 €
pd.read_sql("""
SELECT
    d.nom                       AS departement,
    COUNT(e.id)                 AS nb_employes,
    ROUND(SUM(e.salaire), 2)    AS masse_salariale
FROM departements d
JOIN employes e ON e.departement = d.id
GROUP BY d.id, d.nom
HAVING SUM(e.salaire) > 120000
ORDER BY masse_salariale DESC
""", conn)
```

```{prf:example}
:label: ch06-ex-having-count
Pour ne conserver que les employés ayant réalisé au moins 3 ventes distinctes, on utilise `HAVING COUNT(...) >= 3`.
```

```{code-cell} python
pd.read_sql("""
SELECT
    e.nom               AS employe,
    COUNT(v.id)         AS nb_ventes,
    ROUND(SUM(v.montant), 2) AS total
FROM ventes v
JOIN employes e ON v.employe_id = e.id
GROUP BY e.id, e.nom
HAVING COUNT(v.id) >= 3
ORDER BY total DESC
""", conn)
```

## Différence WHERE vs HAVING

```{prf:remark}
:label: ch06-rem-where-having
Une confusion fréquente consiste à utiliser `HAVING` à la place de `WHERE` pour filtrer sur des colonnes non agrégées. Si le filtre ne porte pas sur une agrégation, préférez `WHERE` : le moteur peut alors éliminer des lignes **avant** le regroupement, ce qui est plus efficace.
```

```{code-cell} python
# WHERE filtre avant le GROUP BY (plus efficace)
pd.read_sql("""
SELECT
    d.nom                       AS departement,
    ROUND(AVG(e.salaire), 2)    AS salaire_moyen
FROM employes e
JOIN departements d ON e.departement = d.id
WHERE e.annee_entree >= 2019          -- filtre sur ligne individuelle
GROUP BY d.id, d.nom
HAVING AVG(e.salaire) > 50000         -- filtre sur le groupe
ORDER BY salaire_moyen DESC
""", conn)
```

```{prf:definition}
:label: ch06-def-where-having-combo
Il est courant de combiner `WHERE` et `HAVING` dans la même requête : `WHERE` réduit le volume de données en amont, puis `HAVING` affine les groupes résultants. Les deux clauses jouent des rôles complémentaires.
```

## NULL dans les agrégats

```{prf:remark}
:label: ch06-rem-null-agregat
Toutes les fonctions d'agrégat (sauf `COUNT(*)`) **ignorent automatiquement les NULL**. Cela signifie que `AVG(salaire)` calcule la moyenne sur les seules valeurs connues, et non en comptant les NULL comme zéro. Ce comportement est conforme au standard SQL.
```

```{code-cell} python
# Iris a salaire = NULL et departement = NULL
# COUNT(*) = 9, COUNT(salaire) = 8
pd.read_sql("""
SELECT
    COUNT(*)       AS nb_total,
    COUNT(salaire) AS nb_avec_salaire,
    COUNT(departement) AS nb_avec_dept,
    ROUND(AVG(salaire), 2) AS moyenne_salaire
FROM employes
""", conn)
```

```{prf:example}
:label: ch06-ex-null-coalesce
Pour traiter les NULL comme zéro dans une somme, utiliser `COALESCE(colonne, 0)` avant d'agréger. Mais attention : cela change la sémantique de la requête (valeur inconnue ≠ zéro).
```

```{code-cell} python
pd.read_sql("""
SELECT
    SUM(salaire)                    AS somme_sans_null,
    SUM(COALESCE(salaire, 0))       AS somme_avec_zero
FROM employes
""", conn)
```

## ROLLUP et totaux croisés

```{prf:remark}
:label: ch06-rem-rollup-sqlite
`GROUP BY ROLLUP(...)` est une extension SQL permettant de produire des sous-totaux automatiques à chaque niveau de regroupement. **SQLite ne supporte pas `ROLLUP`**. On peut en simuler l'effet avec `UNION ALL` en ajoutant une ligne de total global, ou utiliser PostgreSQL/MySQL/SQL Server qui l'implémentent nativement.
```

```{prf:example}
:label: ch06-ex-rollup-simulation
Simulation d'un ROLLUP sur (département, employé) avec `UNION ALL` en SQLite.
```

```{code-cell} python
# Simulation ROLLUP : total par département + total global
pd.read_sql("""
-- Sous-total par département
SELECT d.nom AS departement, ROUND(SUM(v.montant),2) AS total_ventes
FROM ventes v
JOIN employes e ON v.employe_id = e.id
JOIN departements d ON e.departement = d.id
GROUP BY d.id, d.nom

UNION ALL

-- Total global (simulant la ligne ROLLUP de niveau supérieur)
SELECT 'TOTAL' AS departement, ROUND(SUM(montant),2)
FROM ventes
ORDER BY departement
""", conn)
```

```{prf:remark}
:label: ch06-rem-rollup-postgresql
En PostgreSQL, la syntaxe native est :
`GROUP BY ROLLUP(departement, employe)` et génère automatiquement les lignes de sous-totaux intermédiaires et la ligne grand-total.
```

## Visualisation des agrégats

```{code-cell} python
:tags: [hide-input]

# --- Données pour les graphiques ---
df_dept = pd.read_sql("""
SELECT
    d.nom                       AS departement,
    COUNT(e.id)                 AS nb_employes,
    ROUND(AVG(e.salaire), 2)    AS salaire_moyen,
    ROUND(SUM(COALESCE(e.salaire,0)), 2) AS masse_salariale
FROM departements d
LEFT JOIN employes e ON e.departement = d.id
GROUP BY d.id, d.nom
ORDER BY masse_salariale DESC
""", conn)

df_ventes = pd.read_sql("""
SELECT
    d.nom AS departement,
    ROUND(SUM(v.montant), 2) AS total_ventes
FROM ventes v
JOIN employes e ON v.employe_id = e.id
JOIN departements d ON e.departement = d.id
GROUP BY d.id, d.nom
""", conn)

fig, axes = plt.subplots(1, 2, figsize=(13, 5))

# Bar chart : salaire moyen par département
bars = axes[0].bar(
    df_dept["departement"],
    df_dept["salaire_moyen"],
    color=sns.color_palette("muted", len(df_dept))
)
axes[0].set_title("Salaire moyen par département")
axes[0].set_xlabel("Département")
axes[0].set_ylabel("Salaire moyen (€)")
axes[0].yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"{x:,.0f} €"))
for bar, val in zip(bars, df_dept["salaire_moyen"]):
    axes[0].text(bar.get_x() + bar.get_width()/2, bar.get_height() + 400,
                 f"{val:,.0f}", ha="center", va="bottom", fontsize=9)

# Camembert : répartition des ventes par département
axes[1].pie(
    df_ventes["total_ventes"],
    labels=df_ventes["departement"],
    autopct="%1.1f%%",
    colors=sns.color_palette("muted", len(df_ventes)),
    startangle=90
)
axes[1].set_title("Répartition des ventes par département (2024)")

plt.suptitle("Agrégats par département", fontsize=13, fontweight="bold")
plt.show()
```

```{prf:remark}
:label: ch06-rem-viz-agregat
La visualisation confirme que le département Finance concentre la majorité du chiffre d'affaires, tandis que les RH contribuent très peu aux ventes — ce qui est cohérent avec leurs rôles respectifs.
```

## Résumé

```{prf:definition}
:label: ch06-def-synthese
**Récapitulatif des clauses d'agrégation :**

- `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `GROUP_CONCAT` résument un ensemble de lignes en une valeur.
- `GROUP BY` partitionne les lignes en groupes homogènes.
- `HAVING` filtre les groupes formés (équivalent de `WHERE` pour les agrégats).
- Les NULL sont ignorés par toutes les fonctions d'agrégat sauf `COUNT(*)`.
- `ROLLUP` (non supporté en SQLite) produit des sous-totaux automatiques.
- Préférer `WHERE` pour les filtres pré-agrégation ; réserver `HAVING` aux conditions sur valeurs agrégées.
```

| Clause | Moment d'application | Sur quoi ? |
|---|---|---|
| `WHERE` | Avant `GROUP BY` | Lignes individuelles |
| `GROUP BY` | Après `WHERE` | Colonnes de regroupement |
| `HAVING` | Après `GROUP BY` | Valeurs agrégées |
| `ORDER BY` | En dernier | Tout (colonnes ou agrégats) |