Window Functions în SQL: când și cum le folosești

Un raport simplu pe vânzări lunare. Cifrele sunt acolo, agregările funcționează. Apoi vine cererea aparent banală: „pune și creșterea procentuală față de luna anterioară". În SQL clasic, asta înseamnă self-join cu o subquery pe luna precedentă. Funcționează, dar începe să arate urât. Window functions în SQL rezolvă elegant această clasă de probleme — și o duzină alte scenarii similare care apar zilnic în analytics.

Articolul ăsta nu e un tutorial pentru cineva care învață SQL. Asumă că știi SELECT, JOIN și GROUP BY. E despre când window functions merită folosite, când nu, și ce probleme reale apar în practică.

Diferența fundamentală față de GROUP BY

GROUP BY colapsează rândurile. Window function nu. Asta e tot.

Sună simplu, dar implicația e majoră. Cu GROUP BY, dacă vrei să vezi totalul pe lună și rândurile individuale ale fiecărei tranzacții, ai nevoie de două query-uri sau de un join cu o subquery. Cu window function, ambele apar într-un singur SELECT.

Sintaxa minimă:

SELECT
    order_date,
    customer_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS total_per_customer
FROM orders;

Fiecare rând își păstrează identitatea. Coloana nouă conține totalul pe client, calculat „peste fereastra" definită de OVER. Asta înseamnă window functions SQL — funcții care operează pe un subset de rânduri logic conectat, fără să le colapseze.

Categoriile de window functions care contează

Standardul SQL include vreo 11 funcții window. În practică, cinci acoperă 90% din ce ai nevoie.

Ranking: ROW_NUMBER, RANK, DENSE_RANK

ROW_NUMBER() dă o numerotare unică pentru fiecare rând în partition, în ordinea specificată. Util când vrei „primul order al fiecărui client" sau „top 3 produse pe categorie".

SELECT *
FROM (
    SELECT
        product_id,
        category,
        revenue,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
    FROM products
) ranked
WHERE rn <= 3;

RANK() și DENSE_RANK() tratează diferit egalitățile. RANK lasă goluri (1, 2, 2, 4), DENSE_RANK nu (1, 2, 2, 3). Pentru rapoarte de top clienți unde egalitățile contează, alegerea afectează cum apare clasamentul în raport.

Lag și Lead: comparații temporale

LAG() și LEAD() sunt poate cele mai utile window functions pentru rapoarte business. Acces direct la rândul anterior sau următor în partition.

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS delta,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) /
          NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS growth_pct
FROM monthly_revenue;

Trei coloane de business — luna anterioară, diferența absolută, creșterea procentuală — fără un singur self-join. NULLIF previne împărțirea la zero când nu există lună anterioară.

Running totals: SUM peste fereastră ordonată

Când ordonezi într-o fereastră, SUM devine cumulativ.

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS customer_running_total
FROM orders;

Două coloane diferite: total cumulativ global și total cumulativ per client. Asta e exact pattern-ul pentru rapoarte de „cohorta" sau pentru analize LTV.

NTILE: împărțirea în segmente

NTILE(n) împarte rândurile în n grupuri aproximativ egale. Util pentru segmentări rapide — quartile, deciles, scor de prioritizare.

SELECT
    customer_id,
    total_revenue,
    NTILE(4) OVER (ORDER BY total_revenue DESC) AS revenue_quartile
FROM customer_summary;

Cuartila 1 = top 25% clienți. Imediat utilizabil într-un join cu tabela de campanii pentru a trimite oferte diferite per segment.

Frame clauses: partea pe care toată lumea o ignoră

Aici devine interesant. Window function are trei componente: PARTITION BY (grupează), ORDER BY (ordonează), și opțional o clauza de frame care definește exact ce rânduri intră în fereastră.

Frame default-ul când ai ORDER BY e RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. De aici comportamentul cumulativ.

Pentru moving averages, frame-ul explicit e necesar.

SELECT
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_avg
FROM daily_orders;

Media mobilă pe 7 zile. ROWS înseamnă număr de rânduri; RANGE înseamnă valori logice. Diferența contează când există zile lipsă — ROWS ia ultimele 7 rânduri din tabel indiferent dacă sunt consecutive calendaristic, RANGE ar fi nevoie de o definiție temporală explicită (suportată în PostgreSQL, parțial în alte sisteme).

Performanță: când window functions devin scumpe

O concepție comună e că window functions sunt rapide. Sunt — față de alternativele lor (multiple self-joins). Dar nu sunt gratis.

Cost dominant: sortarea implicită din ORDER BY intern. Pe tabele mari fără index potrivit, o window function poate trigger un full sort de zeci de gigabytes.

Trei reguli practice:

Indexează coloanele din PARTITION BY + ORDER BY în ordinea corectă. Un index pe (customer_id, order_date) face dramatic mai rapidă o fereastră de tip „running total per customer".
Limitează partition-urile când e posibil. WHERE aplicat înainte de window function reduce datele procesate. Atenție însă — WHERE filtrează rândurile, nu schimbă ferestrele restante.
Pe BigQuery, Snowflake și alte data warehouses columnar, window functions sunt optimizate suplimentar, dar costul tot apare. Pe BigQuery specific, urmărește bytes processed înainte și după. O dublare a costului per query e ușor de creat dacă nu controlezi partition-urile.

Pe MySQL, suportul window functions e disponibil de la versiunea 8.0. Performanța rezonabilă, dar lipsesc opțiuni avansate de frame disponibile în PostgreSQL. Documentația PostgreSQL pentru window functions e excelentă ca referință completă.

Capcanele care apar în production

După câteva sute de query-uri scrise în production, aceleași probleme apar repetitiv. Câteva merită menționate.

NULL în ORDER BY. Comportamentul default diferă între sisteme. PostgreSQL pune NULL-urile la sfârșit pe ASC; MySQL le pune la început. Pentru rapoarte care folosesc ROW_NUMBER pe coloane cu NULL-uri, asta poate inversa logica. Folosește explicit NULLS FIRST sau NULLS LAST când contează.

Window function în WHERE. Nu funcționează. WHERE row_number() OVER (...) = 1 dă eroare. Window functions se calculează după WHERE, înainte de ORDER BY final. Soluția e subquery sau CTE.

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

Confuzia între window și aggregate. SUM(amount) fără OVER e aggregate, cere GROUP BY. SUM(amount) OVER (...) e window, păstrează rândurile. Mixul în același SELECT e legal — dar trebuie atenție.

Ferestre identice repetate. Dacă același OVER (PARTITION BY x ORDER BY y) apare de 5 ori într-un query, e mai curat să definești o fereastră named.

SELECT
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER w AS running_total,
    AVG(amount) OVER w AS running_avg,
    COUNT(*)   OVER w AS running_count
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date);

Sintaxa WINDOW e suportată de PostgreSQL, MySQL 8+, Oracle, SQL Server. Pe BigQuery — nu, paradoxal. Pe BigQuery trebuie scrise toate ferestrele integral.

Când să folosești și când să eviți

Window functions strălucesc când:

Ai nevoie de coloane derivate pe ordine temporală (LAG, LEAD).
Vrei ranking în partition (top N per grup).
Calculezi running totals sau moving averages.
Faci segmentări procentuale rapide (NTILE).

Sunt mai puțin potrivite când:

Logica e mai simplă cu un JOIN și un GROUP BY clar. Pe scenarii triviale, window function complică citirea fără beneficiu.
Datele sunt foarte mari și nu poți garanta indexare bună. O window function care declanșează un full sort pe 500M rânduri va consuma considerabil mai mult decât echivalentul cu agregare.
Lucrezi într-un sistem fără suport complet (versiuni vechi de MySQL < 8.0, SQLite înainte de 3.25).

Cum arată un raport real construit pe window functions

Scenariu concret. Echipa de marketing vrea săptămânal: top 10 clienți pe lună, cu trendul lor pe ultimele 3 luni, plus poziția lor în clasamentul global.

WITH monthly AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS monthly_revenue
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '4 months'
    GROUP BY customer_id, DATE_TRUNC('month', order_date)
),
enriched AS (
    SELECT
        month,
        customer_id,
        monthly_revenue,
        LAG(monthly_revenue, 1) OVER w AS prev_month,
        LAG(monthly_revenue, 2) OVER w AS two_months_ago,
        RANK() OVER (PARTITION BY month ORDER BY monthly_revenue DESC) AS rank_in_month
    FROM monthly
    WINDOW w AS (PARTITION BY customer_id ORDER BY month)
)
SELECT *
FROM enriched
WHERE month = DATE_TRUNC('month', CURRENT_DATE)
  AND rank_in_month <= 10
ORDER BY rank_in_month;

Un singur query. Trei window functions distincte. Output: top 10 clienți pe luna curentă, fiecare cu revenue-ul lor pe ultimele trei luni și cu rank-ul în partition.

Echivalentul fără window functions ar fi o serie de self-joins urâte — fezabile, dar dificil de menținut.

Pe ce să te concentrezi mai departe

Window functions sunt una dintre categoriile de feature-uri SQL care răsplătesc investiția în studiu. Sunt acolo de două decenii (introduse oficial în SQL:2003), dar adopția reală în echipe variază mult.

Trei lucruri merită aprofundate după ce stăpânești basics:

Frame clauses avansate — RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW pe PostgreSQL deschide pattern-uri elegante pentru analiză temporală.
FIRST_VALUE și LAST_VALUE — utile pentru „prima/ultima valoare pe partition", deși LAST_VALUE are subtilități cu frame default care surprind frecvent.
Pattern matching cu MATCH_RECOGNIZE — disponibil în Oracle, parțial în alte sisteme. Extensia logică a window functions pentru detectare de pattern-uri complexe.

Pentru un analist care lucrează zilnic cu date relaționale, window functions sunt mai aproape de „cunoștință de bază" decât de „feature avansat". Asta e direcția în care s-a mutat practica în ultimii cinci ani — și nu există motiv să creadă că va merge înapoi.