Window Function (SQL Analytic Function)

Window Functions (aussi appelées Analytic Functions) sont des fonctions SQL qui calculent sur une fenêtre de rows liées à la row courante, retournant un résultat per-row (vs aggregate qui collapse à un single row par group). Pierre angulaire des analytics SQL modernes pour rankings, running totals, lag/lead comparisons, moving averages.

Syntaxe : `function() OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ... )`

Fonctions courantes :
(1) **ROW_NUMBER()** — 1, 2, 3, ... sequential par order.
(2) **RANK()** — handle ties avec gaps (1, 2, 2, 4).
(3) **DENSE_RANK()** — no gaps (1, 2, 2, 3).
(4) **NTILE(n)** — quartiles, deciles, etc.
(5) **LAG(col, n)** / **LEAD(col, n)** — value from previous/next row.
(6) **FIRST_VALUE() / LAST_VALUE() / NTH_VALUE()**.
(7) **SUM/AVG/MIN/MAX/COUNT() OVER ()** — running aggregates.
(8) **CUME_DIST(), PERCENT_RANK()** — percentile distributions.

Exemples :
```sql
-- Top 3 produits par catégorie
SELECT * FROM (
SELECT category, product, sales,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rank
FROM products
) t WHERE rank <= 3;

-- Running total ventes
SELECT date, amount,
SUM(amount) OVER (ORDER BY date) AS running_total
FROM orders;

-- Comparer revenu mensuel vs mois précédent
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

-- Moyenne mobile 7 jours
SELECT date, value,
AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM metrics;
```

Frame clauses (advanced) : `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`, `RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW` (time-based windows).

Use cases : rankings/leaderboards, running totals (financial), moving averages, year-over-year comparisons, gap detection (sequential numbers), session-ization events, deduplication retain first occurrence per partition.

Support : tous SGBD modernes — Postgres (excellent), MySQL 8.0+, SQL Server (très complet), Oracle (origine de SQL analytics), Snowflake, BigQuery, Redshift. Performance : typically efficient mais lourd queries sur very large datasets — consider materialized views pour caching. Compétences DP-300, DEA-C01.