Narcis Miclaus

8 min de citit

SQL Server, de la zero Lecția 12 / 40

Funcții de fereastră: ROW_NUMBER, LAG, LEAD, SUM OVER

Caracteristica SQL care transformă self-join-urile și subquery-urile corelate în one-liner-e. Ranking, totaluri cumulative, delta de la o perioadă la alta și clauza OVER explicată.

Funcțiile de fereastră sunt feature-ul pe care regreți cel mai mult că nu l-ai învățat mai devreme. De fiecare dată când le prezint unui dezvoltator la mijloc de carieră, văd exact momentul în care își dă seama că jumătate din codul său trecut ar fi putut fi de cinci ori mai scurt. Atât de mult schimbă jocul.

O funcție de fereastră calculează o valoare pentru fiecare rând pe baza unei „ferestre” de rânduri înrudite — fără a colapsa rezultatul în agregate, așa cum face GROUP BY. Totaluri cumulative, ranking-uri, comparații cu rândul anterior, medii mobile, calcule de la o perioadă la alta — toate acestea devin expresii pe o linie, în loc de self-join-uri sau subquery-uri corelate.

Lecția de astăzi este lecția SQL cu cel mai mare impact din întregul curs. Citește-o de două ori.

Forma: funcție de agregare + clauză OVER

O funcție de fereastră este orice funcție de agregare sau de ranking urmată de o clauză OVER (...):

SELECT OrderId,
       CountryCode,
       Total,
       SUM(Total) OVER (PARTITION BY CountryCode) AS country_revenue,
       ROW_NUMBER() OVER (PARTITION BY CountryCode ORDER BY Total DESC) AS rank_in_country
FROM Sales.Orders;

SUM(Total) OVER (PARTITION BY CountryCode) înseamnă: „pentru fiecare rând, calculează SUM-ul lui Total pentru rândurile cu același CountryCode ca acesta”. Fiecare rând își păstrează datele proprii ȘI primește suma la nivel de țară. Fără colaps. Fără join.

Clauza OVER are trei piese opționale:

  • PARTITION BY <coloane> — împarte rândurile în grupuri (ferestre independente).
  • ORDER BY <coloane> — sortează în interiorul fiecărei partiții (contează pentru totaluri cumulative și ROW_NUMBER).
  • ROWS BETWEEN ... AND ... — frame-ul, controlează ce rânduri din partiție vede funcția.

Fiecare combinație a celor trei îți dă o fereastră diferită.

Funcții de ranking

ROW_NUMBER()

Atribuie 1, 2, 3… rândurilor ordonate în interiorul unei partiții.

-- Clasează clienții după LTV în interiorul țării lor
SELECT
    c.CustomerId,
    c.Name,
    c.Country,
    SUM(o.Total) AS ltv,
    ROW_NUMBER() OVER (
        PARTITION BY c.Country
        ORDER BY SUM(o.Total) DESC
    ) AS country_rank
FROM Sales.Customer c
JOIN Sales.Orders   o ON o.CustomerId = c.CustomerId
GROUP BY c.CustomerId, c.Name, c.Country;

Top 3 clienți per țară: împachetează cele de mai sus într-un CTE și filtrează WHERE country_rank <= 3. Acesta este tiparul „top N per grup” pe care l-am schițat în lecția 8.

RANK() și DENSE_RANK()

Identice cu ROW_NUMBER, dar tratează egalitățile diferit:

  • RANK() — egalitățile primesc același rang, rândul următor sare. 1, 2, 2, 4.
  • DENSE_RANK() — egalitățile primesc același rang, rândul următor nu sare. 1, 2, 2, 3.
  • ROW_NUMBER() — egalitățile primesc ranguri diferite, arbitrare. 1, 2, 3, 4.

Folosește RANK când „doi clienți au egalat pe locul 2, următorul este pe 4” este semantica dorită. ROW_NUMBER când ai nevoie de ranguri unice și dense pentru paginare sau deduplicare.

NTILE(n)

Împarte partiția în n găleți aproximativ egale:

-- Împarte clienții în 4 cuartile după LTV
SELECT CustomerId, ltv,
       NTILE(4) OVER (ORDER BY ltv DESC) AS ltv_quartile
FROM customer_ltv;

Cuartila 1 este top 25% după LTV, cuartila 4 este 25% de jos. Marketingul folosește asta tot timpul pentru segmentare.

Funcții de offset: LAG și LEAD

LAG(col, n, default) returnează coloana din rândul situat cu n rânduri mai devreme în partiție. LEAD este oglinda — din n rânduri mai târziu.

-- Pentru fiecare lună, afișează venitul și diferența față de luna precedentă
WITH monthly AS (
    SELECT DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS month_start,
           SUM(Total) AS revenue
    FROM Sales.Orders
    GROUP BY DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)
)
SELECT month_start,
       revenue,
       LAG(revenue, 1) OVER (ORDER BY month_start)      AS prev_month,
       revenue - LAG(revenue, 1) OVER (ORDER BY month_start) AS delta,
       (revenue - LAG(revenue, 1) OVER (ORDER BY month_start))
           * 100.0 / NULLIF(LAG(revenue, 1) OVER (ORDER BY month_start), 0) AS mom_pct
FROM monthly
ORDER BY month_start;

Creșterea de la o lună la alta. Înainte de funcțiile de fereastră, asta cerea joinarea tabelului agregat cu el însuși pe o lună de offset. Cu LAG, o singură trecere.

Tipar practic: „de la un an la altul” este LAG(revenue, 12) pe date lunare. „Delta față de trimestrul precedent” este LAG(revenue, 1) pe date trimestriale. Aceeași formă, alte offseturi.

Totaluri cumulative și medii mobile

Funcțiile de agregare cu ORDER BY în clauza OVER devin cumulative implicit:

-- Total cumulativ al venitului pe zile
SELECT CAST(OrderDate AS DATE) AS day,
       SUM(Total) OVER (ORDER BY CAST(OrderDate AS DATE)
                        ROWS UNBOUNDED PRECEDING) AS revenue_running
FROM Sales.Orders
ORDER BY day;

ROWS UNBOUNDED PRECEDING îi spune SQL Server-ului: „pentru fiecare rând, însumează de la primul rând al partiției până la rândul curent”. Acesta este comportamentul implicit când folosești ORDER BY fără un frame explicit, dar îl scriu explicit pentru că face intenția evidentă.

Media mobilă pe ultimele 7 zile:

SELECT day,
       daily_revenue,
       AVG(daily_revenue) OVER (
           ORDER BY day
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7d
FROM daily_revenue;

„De la 6 rânduri înainte până la rândul curent” = o fereastră de 7 zile. Metrica „media mobilă pe 7 zile” din dashboard-ul tău este literalmente această linie.

Specificarea frame-ului

Frame-ul controlează ce rânduri vede funcția. Patru tipare comune:

-- Total cumulativ (implicit cu ORDER BY)
ROWS UNBOUNDED PRECEDING

-- Fereastră trailing de dimensiune fixă
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

-- Fereastră centrată
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING

-- Întreaga partiție (ignoră ordinea)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Poți folosi și RANGE BETWEEN în loc de ROWS BETWEEN — RANGE se bazează pe valori (de ex., „toate rândurile aflate la 3 zile distanță de acesta”), în timp ce ROWS se bazează pe numărul de rânduri. Subtil și ușor avansat; ROWS este ce vrei în 90% din cazuri.

Total cumulativ cu reset: tiparul „cumulativ pe categorie”

-- Total cumulativ al venitului pe client, ordonat după istoricul comenzilor
SELECT o.CustomerId,
       o.OrderDate,
       o.Total,
       SUM(o.Total) OVER (
           PARTITION BY o.CustomerId
           ORDER BY o.OrderDate, o.OrderId
           ROWS UNBOUNDED PRECEDING
       ) AS ltv_at_this_point
FROM Sales.Orders AS o;

LTV cumulativ pentru fiecare client după fiecare comandă, resetându-se per client datorită lui PARTITION BY. Util pentru analiza cohortelor, curbele de retenție și orice întrebare de tipul „la momentul comenzii N, cheltuiala totală a acestui client era X”.

FIRST_VALUE și LAST_VALUE

Returnează prima sau ultima valoare din frame. Folosite în mod obișnuit pentru a transporta un atribut per-partiție alături de rând:

-- Fiecare comandă, plus data primei comenzi a clientului
SELECT o.CustomerId,
       o.OrderId,
       o.OrderDate,
       FIRST_VALUE(o.OrderDate) OVER (
           PARTITION BY o.CustomerId
           ORDER BY o.OrderDate, o.OrderId
       ) AS first_order_date,
       DATEDIFF(DAY,
           FIRST_VALUE(o.OrderDate) OVER (
               PARTITION BY o.CustomerId
               ORDER BY o.OrderDate, o.OrderId
           ),
           o.OrderDate
       ) AS days_since_first_order
FROM Sales.Orders AS o;

Fiecare rând știe acum câte zile au trecut de la prima comandă a clientului. Perfect pentru rapoarte de ciclu de viață al clientului.

Atenție cu LAST_VALUE: frame-ul implicit pentru majoritatea funcțiilor de fereastră este „de la primul rând la rândul curent”, deci LAST_VALUE fără un frame explicit returnează rândul curent, nu ultimul al partiției. Folosește mereu frame-ul explicit:

LAST_VALUE(OrderDate) OVER (
    PARTITION BY CustomerId
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Deduplicare cu ROW_NUMBER

Una dintre cele mai bune aplicații ale lui ROW_NUMBER: eliminarea rândurilor duplicate păstrând unul anume.

Să zicem că importul de clienți Runehold a creat din greșeală mai multe rânduri pentru aceeași adresă de email. Păstrează-l pe cel mai vechi:

WITH ranked AS (
    SELECT CustomerId, Email, Name, CreatedAt,
           ROW_NUMBER() OVER (
               PARTITION BY Email
               ORDER BY CreatedAt ASC, CustomerId ASC
           ) AS rn
    FROM Sales.Customer
)
DELETE FROM ranked WHERE rn > 1;

Uimitor de util. Și un loc unde tiebreaker-ul determinist contează: include mereu cheia primară în ORDER BY, astfel încât decizia „pe acesta îl păstrăm” să fie reproductibilă.

Funcții de fereastră vs GROUP BY

Confuzie frecventă a începătorilor:

-- GROUP BY: colapsează rândurile, un rând la ieșire pe țară
SELECT CountryCode, SUM(Total) AS revenue
FROM Sales.Orders
GROUP BY CountryCode;

-- Fereastră: păstrează fiecare rând, fiecare rând afișează totalul țării sale
SELECT OrderId, CountryCode, Total,
       SUM(Total) OVER (PARTITION BY CountryCode) AS country_revenue
FROM Sales.Orders;

Folosește GROUP BY când vrei rezultatul agregat ca rânduri. Folosește ferestre când vrei ca fiecare rând să știe despre agregatul grupului său.

Combinare cu CTE-uri pentru lizibilitate

Logica complexă de fereastră devine ilizibilă rapid. CTE-urile ajută:

WITH order_stats AS (
    SELECT o.OrderId,
           o.CustomerId,
           o.Total,
           o.OrderDate,
           ROW_NUMBER() OVER (PARTITION BY o.CustomerId
                              ORDER BY o.OrderDate, o.OrderId) AS order_seq,
           SUM(o.Total) OVER (PARTITION BY o.CustomerId
                              ORDER BY o.OrderDate, o.OrderId
                              ROWS UNBOUNDED PRECEDING) AS cumulative_ltv,
           LAG(o.OrderDate, 1) OVER (PARTITION BY o.CustomerId
                                      ORDER BY o.OrderDate, o.OrderId) AS prev_order_date
    FROM Sales.Orders AS o
)
SELECT CustomerId,
       OrderId,
       order_seq,
       OrderDate,
       cumulative_ltv,
       DATEDIFF(DAY, prev_order_date, OrderDate) AS days_since_prev_order
FROM order_stats
ORDER BY CustomerId, order_seq;

Fiecare rând: „Aceasta este a 3-a comandă a Mariei, LTV-ul ei cumulativ este 245 €, iar această comandă a fost plasată la 28 de zile după precedenta”. Acela este un raport de analiză de cohortă scris în ~15 linii. Înainte de funcțiile de fereastră, același raport avea peste 50 de linii de self-join-uri și oamenii plângeau.

Rulează asta pe propriul tău server

USE Runehold;
GO

-- Clasează clienții după LTV în interiorul țării lor
WITH customer_ltv AS (
    SELECT c.CustomerId, c.Name, c.Country,
           COALESCE(SUM(o.Total), 0) AS ltv
    FROM Sales.Customer AS c
    LEFT JOIN Sales.Orders AS o ON o.CustomerId = c.CustomerId
    GROUP BY c.CustomerId, c.Name, c.Country
)
SELECT Name, Country, ltv,
       ROW_NUMBER() OVER (PARTITION BY Country ORDER BY ltv DESC) AS country_rank,
       NTILE(4)     OVER (ORDER BY ltv DESC)                    AS global_quartile
FROM customer_ltv
ORDER BY Country, country_rank;

-- Delta venitului de la o zi la alta
WITH daily AS (
    SELECT CAST(OrderDate AS DATE) AS day, SUM(Total) AS revenue
    FROM Sales.Orders
    GROUP BY CAST(OrderDate AS DATE)
)
SELECT day,
       revenue,
       LAG(revenue) OVER (ORDER BY day) AS prev_day,
       revenue - LAG(revenue) OVER (ORDER BY day) AS delta_eur
FROM daily
ORDER BY day;

-- Total cumulativ pe client
SELECT o.CustomerId,
       o.OrderDate,
       o.Total,
       SUM(o.Total) OVER (
           PARTITION BY o.CustomerId
           ORDER BY o.OrderDate, o.OrderId
           ROWS UNBOUNDED PRECEDING
       ) AS ltv_at_order_time
FROM Sales.Orders AS o
ORDER BY o.CustomerId, o.OrderDate;

-- Media mobilă pe 3 comenzi pentru fiecare client
SELECT o.CustomerId,
       o.OrderDate,
       o.Total,
       AVG(o.Total) OVER (
           PARTITION BY o.CustomerId
           ORDER BY o.OrderDate, o.OrderId
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS three_order_avg
FROM Sales.Orders AS o
ORDER BY o.CustomerId, o.OrderDate;

Rulează fiecare, citește fiecare rând și uită-te cum se schimbă coloanele de fereastră. Odată ce forma devine naturală, jumătate dintre query-urile tale viitoare vor fi cu 30% mai scurte și de 10× mai expresive.

Lecția următoare: UNION vs UNION ALL. Una scurtă. O capcană. Apoi în lecția 14 ajungem la funcțiile de string — STRING_AGG, STRING_SPLIT și moartea soluțiilor cu CLR.

Caută