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

# Analyse de données avec DuckDB

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

import matplotlib.pyplot as plt
import matplotlib.patches as patches
import numpy as np
import pandas as pd
import seaborn as sns
import time

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

SQLite est excellent pour les applications transactionnelles légères. Pandas est puissant pour la manipulation de données en Python. Mais aucun des deux n'est optimisé pour les requêtes analytiques — ces agrégations, jointures et fenêtrages sur des millions de lignes qui caractérisent la business intelligence. **DuckDB** comble ce vide : c'est une base de données analytique **embarquée**, qui s'installe comme un package Python et s'utilise directement sur des DataFrames, des fichiers CSV ou Parquet, sans serveur.

DuckDB est orienté OLAP (*Online Analytical Processing*) : il lit les données colonne par colonne, applique des opérations vectorisées sur des blocs, et tire parti du parallélisme multi-cœurs — là où SQLite lit ligne par ligne et reste mono-thread pour les requêtes.

## OLAP vs OLTP

```{prf:definition}
:label: ch19-def-olap-oltp

**OLTP** (*Online Transaction Processing*) optimise les opérations transactionnelles courtes : insertion, mise à jour, lecture de quelques lignes par identifiant. Les bases OLTP (PostgreSQL, MySQL, SQLite) utilisent un stockage en lignes (*row-oriented*) — toutes les colonnes d'une ligne sont stockées ensemble, ce qui est optimal pour `SELECT * FROM commandes WHERE id = 42`.

**OLAP** (*Online Analytical Processing*) optimise les requêtes analytiques qui balayent des millions de lignes mais n'accèdent qu'à quelques colonnes : `SELECT region, SUM(montant) FROM ventes GROUP BY region`. Les bases OLAP (DuckDB, ClickHouse, BigQuery) utilisent un stockage en colonnes (*column-oriented*) — chaque colonne est stockée séparément, ce qui réduit les lectures inutiles et améliore la compression.
```

```{prf:remark}
:label: ch19-rem-duckdb-embedded

DuckDB est **embarqué** comme SQLite : aucun serveur, aucune configuration. Il s'installe avec `pip install duckdb` et s'importe en une ligne. Contrairement à SQLite, il supporte le **parallélisme multi-thread** et les **opérations vectorisées** sur des blocs de données. En pratique, DuckDB sur un laptop peut analyser plusieurs centaines de millions de lignes en quelques secondes.
```

## Lire directement des fichiers

```{prf:definition}
:label: ch19-def-lecture-fichiers

DuckDB peut interroger des fichiers **directement dans le SQL**, sans les charger au préalable en mémoire :

- **CSV** : `SELECT * FROM read_csv_auto('data.csv')`
- **Parquet** : `SELECT * FROM read_parquet('data.parquet')` (format columnar natif)
- **JSON** : `SELECT * FROM read_json_auto('data.json')`
- **Glob** : `SELECT * FROM read_parquet('data/year=2024/month=*.parquet')`

Cette fonctionnalité permet d'analyser des téraoctets de données sur disque sans les charger entièrement en mémoire — DuckDB pousse les filtres et les projections au niveau de la lecture.
```

## Intégration pandas

```{prf:remark}
:label: ch19-rem-integration-pandas

DuckDB s'intègre nativement avec pandas et Arrow : il peut lire un DataFrame directement (zéro-copie avec Arrow), exécuter du SQL dessus, et retourner le résultat sous forme de DataFrame. On enregistre un DataFrame avec `duckdb.register('nom_table', df)` ou on l'utilise directement dans le SQL via son nom de variable Python.
```

## Génération du dataset

```{code-cell} python
import duckdb

np.random.seed(42)
N = 500_000  # 500 000 lignes

regions = ['Nord', 'Sud', 'Est', 'Ouest', 'Centre']
categories = ['Électronique', 'Vêtements', 'Alimentation', 'Sport', 'Maison']
vendeurs = [f"V{i:03d}" for i in range(1, 51)]

dates = pd.date_range("2022-01-01", "2023-12-31", periods=N)

df_ventes = pd.DataFrame({
    'date':      dates,
    'annee':     dates.year,
    'mois':      dates.month,
    'trimestre': dates.quarter,
    'region':    np.random.choice(regions, N),
    'categorie': np.random.choice(categories, N),
    'vendeur':   np.random.choice(vendeurs, N),
    'quantite':  np.random.randint(1, 20, N),
    'prix_unit': np.round(np.random.exponential(scale=45, size=N) + 5, 2),
    'remise':    np.round(np.random.choice([0, 0.05, 0.10, 0.15, 0.20], N,
                                            p=[0.5, 0.2, 0.15, 0.1, 0.05]), 2),
})
df_ventes['montant'] = np.round(df_ventes['quantite'] * df_ventes['prix_unit'] * (1 - df_ventes['remise']), 2)

print(f"Dataset créé : {len(df_ventes):,} lignes, {df_ventes.memory_usage(deep=True).sum() / 1e6:.1f} Mo")
print(df_ventes.head(3).to_string())
```

## Requêtes analytiques DuckDB

```{code-cell} python
# Connexion DuckDB et enregistrement du DataFrame
conn = duckdb.connect()
conn.register("ventes", df_ventes)

# 1. Chiffre d'affaires par région et catégorie
print("=== CA par région (top 3 catégories) ===")
result1 = conn.execute("""
    SELECT
        region,
        categorie,
        ROUND(SUM(montant), 0)            AS ca_total,
        COUNT(*)                          AS nb_transactions,
        ROUND(AVG(montant), 2)            AS panier_moyen,
        ROUND(MEDIAN(montant), 2)         AS panier_median,
        ROUND(QUANTILE_CONT(montant, 0.9), 2) AS p90_montant
    FROM ventes
    GROUP BY region, categorie
    ORDER BY ca_total DESC
    LIMIT 8
""").df()
print(result1.to_string(index=False))
```

```{code-cell} python
# 2. Tendance mensuelle avec window functions
print("\n=== Tendance mensuelle : CA et croissance MoM ===")
result2 = conn.execute("""
    WITH ca_mensuel AS (
        SELECT
            annee,
            mois,
            ROUND(SUM(montant), 0) AS ca
        FROM ventes
        GROUP BY annee, mois
    )
    SELECT
        annee,
        mois,
        ca,
        LAG(ca) OVER (ORDER BY annee, mois)            AS ca_mois_prec,
        ROUND(
            100.0 * (ca - LAG(ca) OVER (ORDER BY annee, mois))
            / NULLIF(LAG(ca) OVER (ORDER BY annee, mois), 0),
        1) AS croissance_pct
    FROM ca_mensuel
    ORDER BY annee, mois
    LIMIT 10
""").df()
print(result2.to_string(index=False))
```

## Fonctions analytiques avancées

```{prf:definition}
:label: ch19-def-qualify

La clause **QUALIFY** est l'équivalent de HAVING pour les fonctions fenêtrées. Elle filtre les lignes après application des window functions, évitant une sous-requête. Exemple : `QUALIFY RANK() OVER (PARTITION BY region ORDER BY ca DESC) <= 3` sélectionne les 3 meilleurs vendeurs par région directement.
```

```{prf:definition}
:label: ch19-def-asof-join

L'**ASOF JOIN** est une jointure temporelle qui associe chaque ligne de la table gauche à la ligne de la table droite dont la clé est la plus proche et inférieure ou égale. Typiquement utilisé pour joindre des événements à la cotation de prix valide à ce moment-là, ou pour associer des logs à la configuration active lors de l'événement.
```

```{prf:definition}
:label: ch19-def-pivot

**PIVOT** transforme des valeurs de lignes en colonnes — l'équivalent d'un tableau croisé dynamique en SQL. DuckDB supporte `PIVOT table ON colonne USING AGG(col) GROUP BY ...`. L'opération inverse, **UNPIVOT**, transforme des colonnes en lignes.
```

```{code-cell} python
# 3. QUALIFY : top 2 catégories par région
print("=== Top 2 catégories par région (QUALIFY) ===")
result3 = conn.execute("""
    SELECT
        region,
        categorie,
        ROUND(SUM(montant), 0) AS ca,
        RANK() OVER (PARTITION BY region ORDER BY SUM(montant) DESC) AS rang
    FROM ventes
    GROUP BY region, categorie
    QUALIFY rang <= 2
    ORDER BY region, rang
""").df()
print(result3.to_string(index=False))

# 4. Statistiques de corrélation
print("\n=== Corrélation quantité / montant ===")
result4 = conn.execute("""
    SELECT
        categorie,
        ROUND(CORR(quantite, montant), 3)       AS corr_qte_montant,
        ROUND(REGR_SLOPE(montant, quantite), 2) AS slope,
        ROUND(REGR_R2(montant, quantite), 3)    AS r_squared
    FROM ventes
    GROUP BY categorie
    ORDER BY corr_qte_montant DESC
""").df()
print(result4.to_string(index=False))
```

```{code-cell} python
# 5. PIVOT : CA par catégorie x trimestre
print("=== PIVOT : CA par catégorie x trimestre ===")
result_pivot = conn.execute("""
    PIVOT (
        SELECT categorie, trimestre, ROUND(SUM(montant), 0) AS ca
        FROM ventes
        GROUP BY categorie, trimestre
    )
    ON trimestre
    USING SUM(ca)
    GROUP BY categorie
    ORDER BY categorie
""").df()
result_pivot.columns = ['Catégorie', 'T1', 'T2', 'T3', 'T4']
print(result_pivot.to_string(index=False))
```

## Benchmark DuckDB vs pandas

```{prf:example}
:label: ch19-ex-benchmark

Pour les agrégations analytiques sur des DataFrames volumineux, DuckDB est systématiquement plus rapide que pandas pour deux raisons : (1) il accède aux données colonne par colonne (moins de mémoire lue), (2) il parallélise sur tous les cœurs CPU. L'avantage est particulièrement marqué sur les agrégations avec GROUP BY complexes et les window functions.
```

```{code-cell} python
def benchmark(func, label, n_runs=5):
    times = []
    for _ in range(n_runs):
        t0 = time.perf_counter()
        func()
        times.append((time.perf_counter() - t0) * 1000)
    median_t = np.median(times)
    print(f"  {label:<45} : {median_t:7.1f} ms (médiane {n_runs} runs)")
    return median_t

print(f"Benchmark sur {N:,} lignes\n")
results = {}

# --- Test 1 : GROUP BY simple ---
print("Test 1 : SUM(montant) GROUP BY region, categorie")
results['t1_pandas'] = benchmark(
    lambda: df_ventes.groupby(['region', 'categorie'])['montant'].sum(),
    "pandas groupby")
results['t1_duckdb'] = benchmark(
    lambda: conn.execute("SELECT region, categorie, SUM(montant) FROM ventes GROUP BY region, categorie").df(),
    "duckdb SQL")

# --- Test 2 : agrégation multiple ---
print("\nTest 2 : multi-agrégats (sum, mean, median, count)")
results['t2_pandas'] = benchmark(
    lambda: df_ventes.groupby('region')['montant'].agg(['sum', 'mean', 'count']),
    "pandas agg")
results['t2_duckdb'] = benchmark(
    lambda: conn.execute("SELECT region, SUM(montant), AVG(montant), MEDIAN(montant), COUNT(*) FROM ventes GROUP BY region").df(),
    "duckdb SQL")

# --- Test 3 : window function ---
print("\nTest 3 : rolling 3 mois + cumsum (window function)")
results['t3_pandas'] = benchmark(
    lambda: (df_ventes.groupby(['annee', 'mois'])['montant'].sum()
             .reset_index()
             .assign(rolling=lambda d: d['montant'].rolling(3).mean())),
    "pandas rolling")
results['t3_duckdb'] = benchmark(
    lambda: conn.execute("""
        SELECT annee, mois, SUM(montant) AS ca,
               AVG(SUM(montant)) OVER (ORDER BY annee, mois ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling3
        FROM ventes GROUP BY annee, mois ORDER BY annee, mois
    """).df(),
    "duckdb window")
```

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

# Visualisation du benchmark
fig, axes = plt.subplots(1, 2, figsize=(14, 6))
palette = sns.color_palette("muted", 2)

# Graphique 1 : barplot comparatif
tests = ['GROUP BY\nsimple', 'Multi-\nagrégats', 'Window\nfunction']
t_pandas = [results['t1_pandas'], results['t2_pandas'], results['t3_pandas']]
t_duckdb = [results['t1_duckdb'], results['t2_duckdb'], results['t3_duckdb']]

x = np.arange(len(tests))
width = 0.35
ax = axes[0]
bars1 = ax.bar(x - width/2, t_pandas, width, label='pandas', color=palette[0], alpha=0.85)
bars2 = ax.bar(x + width/2, t_duckdb, width, label='DuckDB', color=palette[1], alpha=0.85)
ax.set_xticks(x)
ax.set_xticklabels(tests)
ax.set_ylabel("Temps (ms, médiane 5 runs)")
ax.set_title(f"DuckDB vs pandas — {N//1000}k lignes", fontsize=12, fontweight='bold')
ax.legend()
ax.yaxis.grid(True, alpha=0.4)
ax.set_axisbelow(True)
for bar in bars1 + bars2:
    h = bar.get_height()
    ax.text(bar.get_x() + bar.get_width()/2, h + 1, f"{h:.0f}", ha='center', va='bottom', fontsize=8)

# Graphique 2 : tendance mensuelle CA
ax2 = axes[1]
trend = conn.execute("""
    SELECT annee, mois, ROUND(SUM(montant)/1000, 1) AS ca_k
    FROM ventes GROUP BY annee, mois ORDER BY annee, mois
""").df()
trend['periode'] = trend.apply(lambda r: f"{int(r.annee)}-{int(r.mois):02d}", axis=1)

colors_trend = [palette[0] if y == 2022 else palette[1] for y in trend['annee']]
ax2.bar(range(len(trend)), trend['ca_k'], color=colors_trend, alpha=0.8)
ax2.set_xticks([0, 6, 12, 18])
ax2.set_xticklabels(['Jan 2022', 'Jul 2022', 'Jan 2023', 'Jul 2023'], fontsize=9)
ax2.set_xlabel("Période")
ax2.set_ylabel("CA (k€)")
ax2.set_title("Tendance mensuelle du CA", fontsize=12, fontweight='bold')

from matplotlib.patches import Patch
legend_elements = [Patch(fc=palette[0], alpha=0.8, label='2022'),
                   Patch(fc=palette[1], alpha=0.8, label='2023')]
ax2.legend(handles=legend_elements)
ax2.yaxis.grid(True, alpha=0.4)
ax2.set_axisbelow(True)

plt.suptitle(f"DuckDB — Analyse de {N:,} transactions", fontsize=13, fontweight='bold')
plt.tight_layout()
plt.show()
```

## Comparaison DuckDB / SQLite / pandas

```{prf:example}
:label: ch19-ex-comparaison-outils

Choisir entre DuckDB, SQLite et pandas dépend du cas d'usage :

- **SQLite** : CRUD transactionnel, données structurées < 10 Go, application légère. Accès ligne par ligne. Simple, fiable, universel.
- **pandas** : manipulation exploratoire de DataFrames en mémoire, transformations complexes en Python, petits jeux de données (< quelques Go RAM).
- **DuckDB** : requêtes analytiques SQL sur de grands jeux de données, lecture directe Parquet/CSV, intégration dans un pipeline data Python. Remplace avantageusement pandas pour les agrégations pures.
```

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

# Tableau comparatif
fig, ax = plt.subplots(figsize=(13, 5))
ax.axis('off')

criteres = ["Stockage", "Requêtes SQL", "Parallélisme", "Lecture Parquet", "Intégration Python",
            "ACID complet", "Cas d'usage principal", "Taille optimale"]
sqlite  = ["Ligne par ligne", "Standard", "Non", "Limité", "sqlite3 intégré",
           "Oui", "CRUD applicatif", "< 10 Go"]
pandas  = ["RAM (numpy)", "Non (méthodes)", "Limité", "Via pyarrow", "Native",
           "Non", "Exploration/EDA", "< RAM dispo"]
duckdb_col  = ["Colonne par colonne", "Avancé + QUALIFY,PIVOT", "Oui (multi-core)", "Natif",
               "duckdb.connect()", "Oui", "OLAP analytique", "Go → To"]

col_labels = ["Critère", "SQLite", "pandas", "DuckDB"]
table_data = [[c, s, p, d] for c, s, p, d in zip(criteres, sqlite, pandas, duckdb_col)]

colors_table = []
palette_tbl = sns.color_palette("muted", 4)
for i, row in enumerate(table_data):
    row_colors = ['#f5f5f5', '#e8f4fd', '#fef9e7', '#eafaf1']
    if i % 2 == 0:
        row_colors = ['#ebebeb', '#d6eaf8', '#fdf2e9', '#d5f5e3']
    colors_table.append(row_colors)

table = ax.table(
    cellText=table_data,
    colLabels=col_labels,
    cellLoc='left',
    loc='center',
    cellColours=colors_table,
)
table.auto_set_font_size(False)
table.set_fontsize(8.5)
table.scale(1, 1.8)

for j, (label, color) in enumerate(zip(col_labels, ['#555', '#2980b9', '#d4ac0d', '#27ae60'])):
    table[(0, j)].set_facecolor(color)
    table[(0, j)].set_text_props(color='white', fontweight='bold')

ax.set_title("DuckDB / SQLite / pandas — Comparaison", fontsize=13, fontweight='bold', pad=15)
plt.show()
```

## Lecture directe de Parquet et CSV

```{code-cell} python
# Exporter le dataset en Parquet et CSV, puis interroger directement
import tempfile, os

with tempfile.TemporaryDirectory() as tmpdir:
    parquet_path = os.path.join(tmpdir, "ventes.parquet")
    csv_path     = os.path.join(tmpdir, "ventes.csv")

    df_ventes.to_parquet(parquet_path, index=False)
    df_ventes.to_csv(csv_path, index=False)

    parquet_size = os.path.getsize(parquet_path) / 1e6
    csv_size     = os.path.getsize(csv_path) / 1e6
    print(f"Taille CSV     : {csv_size:.1f} Mo")
    print(f"Taille Parquet : {parquet_size:.1f} Mo (compression {csv_size/parquet_size:.1f}x)")

    # Requête directe sur Parquet (sans charger dans pandas)
    result_pq = duckdb.execute(f"""
        SELECT
            region,
            ROUND(SUM(montant), 0) AS ca,
            COUNT(*)               AS nb_ventes
        FROM read_parquet('{parquet_path}')
        GROUP BY region
        ORDER BY ca DESC
    """).df()
    print("\nCA par région (lu depuis Parquet) :")
    print(result_pq.to_string(index=False))
```

```{prf:remark}
:label: ch19-rem-parquet

**Parquet** est le format de fichier columnar standard de l'écosystème data. Il compresse typiquement 5 à 10 fois mieux que CSV (grâce au stockage colonne et aux codecs Snappy/Zstd), et DuckDB peut pousser les filtres directement dans la lecture Parquet (predicate pushdown) — seules les colonnes nécessaires et les lignes satisfaisant les `WHERE` sont lues depuis le disque. Un fichier Parquet de 1 Go en CSV peut se réduire à 100-150 Mo.
```

```{prf:example}
:label: ch19-ex-aggregats-stats

DuckDB intègre des fonctions d'agrégation statistiques rarement disponibles en SQL standard :

- `MEDIAN(col)` : médiane exacte.
- `QUANTILE_CONT(col, 0.9)` : quantile continu (interpolé).
- `CORR(x, y)` : coefficient de corrélation de Pearson.
- `REGR_SLOPE(y, x)` et `REGR_R2(y, x)` : régression linéaire simple.
- `MODE()` : valeur la plus fréquente.
- `ENTROPY(col)` : entropie de Shannon.

Ces fonctions permettent des analyses statistiques complètes directement en SQL, sans passer par pandas ou scipy.
```

## Résumé

DuckDB représente une évolution majeure dans l'outillage analytique Python : la puissance SQL complète (fenêtrage, PIVOT, statistiques) appliquée à des données locales — DataFrames, Parquet, CSV — sans serveur et avec les performances d'une base OLAP.

```{prf:remark}
:label: ch19-rem-synthese

Les points clés à retenir :

- DuckDB est une base OLAP embarquée : stockage columnar, exécution vectorisée, multi-thread — aucun serveur requis.
- Il lit nativement CSV, Parquet et JSON en SQL, avec predicate pushdown pour ne lire que les données nécessaires.
- `duckdb.register('nom', df)` ou l'interpolation directe du nom de variable Python permettent d'interroger des DataFrames sans copie.
- DuckDB est plus rapide que pandas pour les agrégations GROUP BY et les window functions sur de grandes tables.
- `QUALIFY` filtre après les window functions ; `PIVOT/UNPIVOT` croise les dimensions ; `ASOF JOIN` joint par proximité temporelle.
- Parquet est le format de stockage recommandé : compression 5-10x, lecture partielle (predicate pushdown), interopérable avec Spark, Arrow, Polars.
```