Window Functions

Què són les window functions?

Les window functions (funcions de finestra) calculen un valor per a cada fila basant-se en un conjunt de files relacionades — la finestra — però, a diferència de GROUP BY, no col·lapsen les files: cada fila del resultat conserva la seva identitat i el valor calculat apareix al costat de les dades originals.

-- GROUP BY: col·lapsa 100 files en 5 grups → 5 files al resultat
SELECT categoria, SUM(preu) FROM productes GROUP BY categoria;

-- Window function: afegeix la suma a cada fila → continua havent-hi 100 files
SELECT nom, categoria, preu, SUM(preu) OVER (PARTITION BY categoria) AS total_categoria
FROM productes;

Casos d'ús típics: rànquings, totals acumulats, mitjanes mòbils, comparació d'una fila amb l'anterior o la següent.

La finestra: com es calcula fila a fila

A diferència del GROUP BY, la window function no col·lapsa les files: cada fila del resultat conserva la seva identitat i el valor calculat apareix com una columna nova. La "finestra" és el conjunt de files que el motor té en compte per calcular el valor de cada fila concreta.

Visualitzacio de la finestra — ROW_NUMBER i SUM OVER (PARTITION BY)

Dades inicials Taula empleats amb 5 files de dos departaments. Les columnes row_num i sum_dept encara no estan calculades. A cada pas veurem com el motor les omple fila a fila.

nom	dept	salari	row_num	sum_dept
Anna	IT	3 000	—	—
Bernat	IT	4 000	—	—
Carla	IT	4 000	—	—
Dani	Vendes	2 500	—	—
Elena	Vendes	3 500	—	—

Fila actual processada

Finestra activa (PARTITION BY dept)

Fora de la finestra actual

Fila 1: Anna (IT) El motor identifica la particio de IT (files 1-3, ressaltades en blau). Dins d'aquesta particio, Anna és la primera fila ordenada per salari: ROW_NUMBER = 1. La suma total de salaris d'IT és 3000 + 4000 + 4000 = SUM = 11 000.

nom	dept	salari	row_num	sum_dept
Anna	IT	3 000	1	11 000
Bernat	IT	4 000	—	—
Carla	IT	4 000	—	—
Dani	Vendes	2 500	—	—
Elena	Vendes	3 500	—	—

Files 2-3: Bernat i Carla (IT) Seguim dins la mateixa particio IT. SUM es manté a 11 000 per a tota la particio. ROW_NUMBER avanca: Bernat = 2, Carla = 3. Nota: Bernat i Carla tenen el mateix salari, pero ROW_NUMBER sempre dona valors únics (ordre arbitrari en empats). Useu RANK() si voleu empats iguals.

nom	dept	salari	row_num	sum_dept
Anna	IT	3 000	1	11 000
Bernat	IT	4 000	2	11 000
Carla	IT	4 000	—	—
Dani	Vendes	2 500	—	—
Elena	Vendes	3 500	—	—

Fila 4: Dani (Vendes) Canviem de particio: ara el motor treballa amb la particio Vendes (files 4-5). La finestra blava es reinicia. ROW_NUMBER torna a 1. La suma de Vendes és 2500 + 3500 = SUM = 6 000.

nom	dept	salari	row_num	sum_dept
Anna	IT	3 000	1	11 000
Bernat	IT	4 000	2	11 000
Carla	IT	4 000	3	11 000
Dani	Vendes	2 500	1	6 000
Elena	Vendes	3 500	—	—

PARTITION BY reinicia el comptador i la suma per a cada grup

Resultat final — totes les files calculades Totes les files conserven la seva identitat (no col·lapsen com amb GROUP BY). Cada fila te el seu ROW_NUMBER dins la seva particio i la suma total del seu departament. La consulta equivalent és:

SELECT nom, dept, salari, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salari), SUM(salari) OVER (PARTITION BY dept) FROM empleats;

nom	dept	salari	row_num	sum_dept
Anna	IT	3 000	1	11 000
Bernat	IT	4 000	2	11 000
Carla	IT	4 000	3	11 000
Dani	Vendes	2 500	1	6 000
Elena	Vendes	3 500	2	6 000

5 files al resultat (no col·lapsen, a diferencia de GROUP BY que n'hauria retornat 2)

Sintaxi general

funció() OVER (
    [PARTITION BY  columnes_de_partició]
    [ORDER BY      columnes_d_ordenació]
    [frame_clause]
)

Clàusula	Descripció
`PARTITION BY`	Divideix les files en grups independents (com un GROUP BY però sense col·lapsar). Si s'omet, tota la taula és una sola finestra.
`ORDER BY`	Determina l'ordre dins de cada partició. Obligatori per a funcions de rànquing i desplaçament.
`frame_clause`	Defineix quines files de la partició formen la finestra respecte a la fila actual (`ROWS` o `RANGE BETWEEN ...`).

Funcions de rànquing

ROW_NUMBER, RANK i DENSE_RANK

Funció	Comportament en empats
`ROW_NUMBER()`	Número únic i consecutiu; en cas d'empat, l'ordre és arbitrari
`RANK()`	El mateix rànquing als empats, però deixa forats (1, 1, 3...)
`DENSE_RANK()`	El mateix rànquing als empats, sense forats (1, 1, 2...)

PostgreSQLMySQL / MariaDBSQL ServerOracle

SELECT
    nom,
    departament,
    salari,
    ROW_NUMBER() OVER (PARTITION BY departament ORDER BY salari DESC) AS fila,
    RANK()       OVER (PARTITION BY departament ORDER BY salari DESC) AS rang,
    DENSE_RANK() OVER (PARTITION BY departament ORDER BY salari DESC) AS rang_dens
FROM empleats;

-- Top 3 productes per categoria (filtrant per window function amb CTE)
WITH ranked AS (
    SELECT
        nom, categoria, preu,
        RANK() OVER (PARTITION BY categoria ORDER BY preu DESC) AS pos
    FROM productes
)
SELECT nom, categoria, preu FROM ranked WHERE pos <= 3;

-- Suportat des de MySQL 8.0 / MariaDB 10.2
SELECT
    nom,
    departament,
    salari,
    ROW_NUMBER() OVER (PARTITION BY departament ORDER BY salari DESC) AS fila,
    RANK()       OVER (PARTITION BY departament ORDER BY salari DESC) AS rang,
    DENSE_RANK() OVER (PARTITION BY departament ORDER BY salari DESC) AS rang_dens
FROM empleats;

-- Top 3 per categoria
WITH ranked AS (
    SELECT nom, categoria, preu,
           RANK() OVER (PARTITION BY categoria ORDER BY preu DESC) AS pos
    FROM productes
)
SELECT nom, categoria, preu FROM ranked WHERE pos <= 3;

SELECT
    nom,
    departament,
    salari,
    ROW_NUMBER() OVER (PARTITION BY departament ORDER BY salari DESC) AS fila,
    RANK()       OVER (PARTITION BY departament ORDER BY salari DESC) AS rang,
    DENSE_RANK() OVER (PARTITION BY departament ORDER BY salari DESC) AS rang_dens
FROM empleats;

-- Top 3 per categoria
WITH ranked AS (
    SELECT nom, categoria, preu,
           RANK() OVER (PARTITION BY categoria ORDER BY preu DESC) AS pos
    FROM productes
)
SELECT nom, categoria, preu FROM ranked WHERE pos <= 3;

SELECT
    nom,
    departament,
    salari,
    ROW_NUMBER() OVER (PARTITION BY departament ORDER BY salari DESC) AS fila,
    RANK()       OVER (PARTITION BY departament ORDER BY salari DESC) AS rang,
    DENSE_RANK() OVER (PARTITION BY departament ORDER BY salari DESC) AS rang_dens
FROM empleats;

-- Top 3 per categoria
WITH ranked AS (
    SELECT nom, categoria, preu,
           RANK() OVER (PARTITION BY categoria ORDER BY preu DESC) AS pos
    FROM productes
)
SELECT nom, categoria, preu FROM ranked WHERE pos <= 3;

-- Oracle també ofereix la sintaxi analítica alternativa (llegat pre-SQL:2003):
SELECT nom, categoria, preu,
       RANK() OVER (PARTITION BY categoria ORDER BY preu DESC) AS pos
FROM productes;

NTILE i PERCENT_RANK

NTILE(n) divideix les files en n grups iguals i retorna el número de grup (1..n). Útil per a quartils, decils, etc.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Dividir els productes en 4 grups de preu (quartils)
SELECT
    nom, preu,
    NTILE(4) OVER (ORDER BY preu) AS quartil,
    PERCENT_RANK() OVER (ORDER BY preu) AS pct_rang,   -- 0.0 a 1.0
    CUME_DIST()    OVER (ORDER BY preu) AS dist_acum   -- proporció acumulada
FROM productes;

SELECT
    nom, preu,
    NTILE(4)       OVER (ORDER BY preu) AS quartil,
    PERCENT_RANK() OVER (ORDER BY preu) AS pct_rang,
    CUME_DIST()    OVER (ORDER BY preu) AS dist_acum
FROM productes;

SELECT
    nom, preu,
    NTILE(4)       OVER (ORDER BY preu) AS quartil,
    PERCENT_RANK() OVER (ORDER BY preu) AS pct_rang,
    CUME_DIST()    OVER (ORDER BY preu) AS dist_acum
FROM productes;

SELECT
    nom, preu,
    NTILE(4)       OVER (ORDER BY preu) AS quartil,
    PERCENT_RANK() OVER (ORDER BY preu) AS pct_rang,
    CUME_DIST()    OVER (ORDER BY preu) AS dist_acum
FROM productes;

Funcions de desplaçament

Permeten accedir al valor d'una fila anterior o posterior dins la finestra, sense fer un self-join.

Funció	Descripció
`LAG(col, n, default)`	Valor de la fila `n` posicions enrere
`LEAD(col, n, default)`	Valor de la fila `n` posicions endavant
`FIRST_VALUE(col)`	Primer valor de la finestra
`LAST_VALUE(col)`	Últim valor de la finestra
`NTH_VALUE(col, n)`	Valor en la posició `n` de la finestra

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Comparació de vendes mensuals amb el mes anterior
SELECT
    mes,
    total_vendes,
    LAG(total_vendes, 1, 0) OVER (ORDER BY mes) AS vendes_mes_anterior,
    total_vendes - LAG(total_vendes, 1, 0) OVER (ORDER BY mes) AS variacio,
    LEAD(total_vendes) OVER (ORDER BY mes) AS vendes_mes_seguent
FROM vendes_mensuals;

-- Primer i últim preu de cada categoria
SELECT
    nom, categoria, preu,
    FIRST_VALUE(preu) OVER (PARTITION BY categoria ORDER BY preu) AS preu_minim_cat,
    LAST_VALUE(preu)  OVER (
        PARTITION BY categoria ORDER BY preu
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS preu_maxim_cat
FROM productes;

SELECT
    mes,
    total_vendes,
    LAG(total_vendes, 1, 0) OVER (ORDER BY mes) AS vendes_mes_anterior,
    total_vendes - LAG(total_vendes, 1, 0) OVER (ORDER BY mes) AS variacio,
    LEAD(total_vendes) OVER (ORDER BY mes) AS vendes_mes_seguent
FROM vendes_mensuals;

SELECT
    mes,
    total_vendes,
    LAG(total_vendes, 1, 0)  OVER (ORDER BY mes) AS vendes_mes_anterior,
    total_vendes - LAG(total_vendes, 1, 0) OVER (ORDER BY mes) AS variacio,
    LEAD(total_vendes, 1, 0) OVER (ORDER BY mes) AS vendes_mes_seguent
FROM vendes_mensuals;

SELECT
    mes,
    total_vendes,
    LAG(total_vendes, 1, 0)  OVER (ORDER BY mes) AS vendes_mes_anterior,
    total_vendes - LAG(total_vendes, 1, 0) OVER (ORDER BY mes) AS variacio,
    LEAD(total_vendes, 1, 0) OVER (ORDER BY mes) AS vendes_mes_seguent
FROM vendes_mensuals;

-- Primer i últim de la finestra completa
SELECT
    nom, categoria, preu,
    FIRST_VALUE(preu) OVER (PARTITION BY categoria ORDER BY preu
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS preu_minim_cat,
    LAST_VALUE(preu)  OVER (PARTITION BY categoria ORDER BY preu
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS preu_maxim_cat
FROM productes;

LAST_VALUE i el frame per defecte

El frame per defecte quan hi ha ORDER BY és ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Això fa que LAST_VALUE retorni el valor de la fila actual, no el darrer de la partició. Per obtenir el darrer valor real cal especificar ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Agregació com a window function

Qualsevol funció d'agregació (SUM, AVG, COUNT, MAX, MIN) es pot usar com a window function afegint OVER (...). Això permet calcular totals acumulats, mitjanes mòbils i percentatges sense perdre el detall de les files.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Total acumulat de vendes (running total)
SELECT
    data_com,
    total,
    SUM(total) OVER (ORDER BY data_com) AS total_acumulat,
    AVG(total) OVER (ORDER BY data_com
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS mitja_7_dies,
    COUNT(*)   OVER () AS total_comandes,           -- sense ORDER BY: tota la taula
    ROUND(total / SUM(total) OVER () * 100, 2) AS pct_sobre_total
FROM comandes
WHERE estat = 'entregada';

-- Comparació de cada producte amb la mitja de la seva categoria
SELECT
    nom, categoria, preu,
    AVG(preu) OVER (PARTITION BY categoria) AS preu_mig_categoria,
    preu - AVG(preu) OVER (PARTITION BY categoria) AS desviacio
FROM productes;

SELECT
    data_com,
    total,
    SUM(total) OVER (ORDER BY data_com) AS total_acumulat,
    COUNT(*)   OVER () AS total_comandes,
    ROUND(total / SUM(total) OVER () * 100, 2) AS pct_sobre_total
FROM comandes
WHERE estat = 'entregada';

-- Desviació respecte a la mitja de la categoria
SELECT
    nom, categoria, preu,
    AVG(preu) OVER (PARTITION BY categoria) AS preu_mig_categoria,
    preu - AVG(preu) OVER (PARTITION BY categoria) AS desviacio
FROM productes;

SELECT
    data_com,
    total,
    SUM(total) OVER (ORDER BY data_com
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_acumulat,
    AVG(total) OVER (ORDER BY data_com
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS mitja_7_dies,
    COUNT(*)   OVER () AS total_comandes,
    ROUND(total * 100.0 / SUM(total) OVER (), 2) AS pct_sobre_total
FROM comandes
WHERE estat = 'entregada';

SELECT
    data_com,
    total,
    SUM(total) OVER (ORDER BY data_com
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_acumulat,
    AVG(total) OVER (ORDER BY data_com
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS mitja_7_dies,
    COUNT(*)   OVER () AS total_comandes,
    ROUND(total / SUM(total) OVER () * 100, 2) AS pct_sobre_total
FROM comandes
WHERE estat = 'entregada';

-- Desviació respecte a la mitja de la categoria
SELECT
    nom, categoria, preu,
    AVG(preu) OVER (PARTITION BY categoria) AS preu_mig_categoria,
    preu - AVG(preu) OVER (PARTITION BY categoria) AS desviacio
FROM productes;

Frame clause: ROWS i RANGE BETWEEN

La frame clause defineix quines files de la partició formen la finestra per a cada càlcul:

ROWS  BETWEEN inici AND fi
RANGE BETWEEN inici AND fi

Límit	Significat
`UNBOUNDED PRECEDING`	Des de la primera fila de la partició
`n PRECEDING`	Les `n` files anteriors a l'actual
`CURRENT ROW`	La fila actual
`n FOLLOWING`	Les `n` files posteriors a l'actual
`UNBOUNDED FOLLOWING`	Fins a l'última fila de la partició

La diferència entre ROWS i RANGE:

ROWS: compta files físiques (per posició).
RANGE: agrupa files amb el mateix valor d'ORDER BY (per valor).

PostgreSQLMySQL / MariaDBSQL ServerOracle

SELECT
    mes, total,
    -- Acumulat des del principi
    SUM(total) OVER (ORDER BY mes ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acum,
    -- Mitja mòbil dels 3 mesos anteriors + actual (4 mesos)
    AVG(total) OVER (ORDER BY mes ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS mitja_4m,
    -- Suma de tota la partició (equivalent a SUM OVER ())
    SUM(total) OVER (ORDER BY mes ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total_anual
FROM vendes_mensuals;

SELECT
    mes, total,
    SUM(total) OVER (ORDER BY mes ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acum,
    AVG(total) OVER (ORDER BY mes ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS mitja_4m
FROM vendes_mensuals;

SELECT
    mes, total,
    SUM(total) OVER (ORDER BY mes ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acum,
    AVG(total) OVER (ORDER BY mes ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS mitja_4m,
    SUM(total) OVER (ORDER BY mes ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total_anual
FROM vendes_mensuals;

SELECT
    mes, total,
    SUM(total) OVER (ORDER BY mes ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acum,
    AVG(total) OVER (ORDER BY mes ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS mitja_4m,
    SUM(total) OVER (ORDER BY mes ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total_anual
FROM vendes_mensuals;

Compatibilitat entre motors

Funció	PostgreSQL	MySQL	MariaDB	SQL Server	Oracle
`ROW_NUMBER`	8.4+	8.0+	10.2+	2005+	8i+
`RANK` / `DENSE_RANK`	8.4+	8.0+	10.2+	2005+	8i+
`NTILE`	8.4+	8.0+	10.2+	2005+	8i+
`LAG` / `LEAD`	9.0+	8.0+	10.2+	2012+	11g+
`FIRST_VALUE` / `LAST_VALUE`	8.4+	8.0+	10.2+	2012+	11g+
`NTH_VALUE`	9.0+	8.0+	10.2+	—	11g+
`PERCENT_RANK` / `CUME_DIST`	8.4+	8.0+	10.2+	2012+	11g+
Frame clause	8.4+	8.0+	10.2+	2012+	11g+

AC0372/04/09 — Miniactivitat

RA4 · CA4.5, CA4.6, CA4.7

Usant la BD de pràctiques, escriviu consultes amb window functions per a:

Les 3 instal·lacions mes reservades de cada tipus, usant RANK().
Per a cada reserva, mostreu el preu i la diferència respecte a la reserva anterior del mateix usuari (LAG).
El total acumulat de la recaptació diària al llarg de l'any (running total).
El percentatge que representa cada reserva sobre el total de l'usuari (SUM OVER PARTITION BY).
Reescriviu la consulta 1 usant DENSE_RANK i compareu els resultats quan hi ha empats.