A 04-05 vam deixar una frase pendent: el top N per grup i "qualsevol càlcul que hagi d'agregar sense col·lapsar les files" necessiten funcions de finestra. Aquest és el problema. SUM(import) sobre les 47 línies de BotigaVerda retorna una fila amb 727,95 €; però el que gairebé sempre vols són les 47 línies, cadascuna amb el seu import i, al costat, el total — per calcular el percentatge que representa, la seva posició en un rànquing o quant portes acumulat.

Un GROUP BY no ho pot fer: col·lapsa exactament el que vols conservar. La clàusula OVER sí, i és la diferència entre saber SQL i saber-lo fer servir. En aquesta lliçó veuràs l'anatomia d'OVER (PARTITION BY ... ORDER BY ... marc), on s'executa una funció de finestra i per què això fa impossible filtrar-la al WHERE —l'error més freqüent que hi ha—, les tres famílies amb la seva taula de referència, el marc i el parany clàssic de LAST_VALUE, i sis casos reals de BotigaVerda: acumulat mensual, mitjana mòbil, variació respecte al mes anterior, top N per categoria, rànquing de clients i cada producte enfront de la mitjana de la seva categoria.

1. La idea central: agregar sense col·lapsar
2. L'anatomia d'OVER
3. On s'executa: l'error del WHERE
4. Rànquing: ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK
5. Desplaçament: LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
6. Agregats com a finestra, i el marc: ROWS enfront de RANGE
7. WINDOW: donar nom a una finestra
8. Els casos de BotigaVerda
9. Rendiment i dialecte
10. Errors habituals i consells
11. Exercicis
12. Conclusió

Una funció de finestra calcula un valor per a cada fila a partir d'un conjunt de files relacionades amb ella —la seva finestra—, sense reduir el nombre de files del resultat.

La forma més petita possible és OVER (), la finestra buida: "totes les files del resultat".

(3 primeres de 47 files.) Les 47 línies hi continuen sent, i cadascuna porta enganxats els 727,95 € del total i el seu percentatge. Amb GROUP BY això és impossible: o tens el detall, o tens el total. Aquí tens tots dos.

I aquesta és la comparació que cal fixar abans de continuar:

La forma general és funcio(args) OVER (PARTITION BY expr ORDER BY expr ROWS/RANGE ...), amb tres peces: PARTITION BY diu en quins grups es divideix (sense ell, tot és un sol grup), ORDER BY en quin ordre es recorre cada grup, i el marc quina franja del grup entra en el càlcul.

Les tres parts són opcionals i independents, i cada combinació significa una cosa diferent:

El detall que sorprèn tothom: afegir ORDER BY a una finestra canvia el resultat d'un SUM, perquè activa un marc per omissió que va del principi fins a la fila actual. Sense ORDER BY, SUM dona el total del grup; amb ell, dona l'acumulat. No és un error: és la porta d'entrada al marc de l'apartat 6.

Les funcions de finestra s'avaluen gairebé al final: FROM → WHERE → GROUP BY → HAVING → funcions de finestra → SELECT → DISTINCT → ORDER BY → LIMIT. D'aquí se'n dedueixen dos fets importants. El primer: una funció de finestra veu només les files que van sobreviure al WHERE. Si filtres per pais = 'França', el SUM(...) OVER () serà el total de França, no el de la botiga. El segon és l'error número u de la lliçó:

I no és una limitació arbitrària: quan s'avalua el WHERE, la funció de finestra encara no s'ha calculat, perquè necessita saber quines files passen el filtre. Seria circular. La solució és sempre la mateixa —calcular en un nivell i filtrar en el següent—, i amb CTE (10-02) es llegeix perfectament:

Recorda-ho així: una funció de finestra no es filtra, s'embolcalla. El mateix val per al HAVING i per als JOIN.

BotigaVerda té un empat perfecte per veure-ho: l'arròs i el tomàquet, amb 14 unitats venudes cadascun.

(5 primeres de 17 files; la sisena, el raspall de bambú amb 9 unitats, rep 6, 4 i 3; la setena, Pasta d'espelta amb 7 unitats, rep 7, 7 i 4: els 17 productes que s'han venut alguna vegada.) Llegeix-ho per columnes i no ho oblidaràs. ROW_NUMBER numera 1, 2, 3, 4, 5, 6, 7: no repeteix mai, i per això cal un desempat explícit (, p.id) o l'elecció és arbitrària i pot canviar entre execucions. RANK dona 1, 1, 3, 4, 4, 4, 7: els empatats comparteixen posició i el següent salta tants llocs com empatats hi hagués; és el podi esportiu. DENSE_RANK dona 1, 1, 2, 3, 3, 3, 4: comparteixen posició sense deixar forats, així que és "el segon millor valor", no "el segon".

Quina fer servir: ROW_NUMBER per triar una fila per grup (desduplicar, top 1); RANK per a un rànquing publicable; DENSE_RANK quan el que numeres són valors diferents, no files.

Aquestes funcions miren una altra fila de la mateixa finestra. Sobre la sèrie mensual de BotigaVerda:

(3 primeres de 12 files.) Els tres arguments de LAG i LEAD són (columna, desplaçament, valor_per_defecte): per omissió el desplaçament és 1 i el valor per defecte és NULL —d'aquí el *(null)* del primer mes—, mentre que LAG(facturacio, 2, 0) mira dues files enrere i retorna 0 quan no hi ha tal fila. Aquest tercer argument evita un COALESCE i, sobretot, evita que una resta es converteixi en NULL.

FIRST_VALUE, LAST_VALUE i NTH_VALUE(expr, n) retornen el valor de la primera, l'última i l'enèsima fila del marc. Sobre aquesta mateixa sèrie i amb el marc complet, FIRST_VALUE dona 68,80 € (març del 2025), LAST_VALUE dona 49,33 € (febrer del 2026) i NTH_VALUE(facturacio, 3) dona 58,85 € a les dotze files. Aquest "amb el marc complet" no és un detall: el final d'aquest apartat 6 explica per què sense ell el resultat és un altre.

Qualsevol funció d'agregació del mòdul 4 accepta OVER: SUM, AVG, COUNT, MIN, MAX, STRING_AGG, i també les que porten FILTER. La sintaxi és idèntica; l'única cosa que canvia és que el resultat s'enganxa a cada fila en lloc de col·lapsar-la.

(3 dels 8 empleats —hi falta la Beatriz, 39.500 €, al lloc 3—; la columna acumulada tanca en 280.300,00 €, la massa salarial total.) Les xifres canòniques del curs —suma 280.300 €, mitjana 35.037,50 €— apareixen aquí al costat de cada empleat, i la lectura de negoci és immediata: el Daniel guanya 37,50 € menys que la mitjana exacta de l'empresa, i els tres primers salaris es mengen més de la meitat de la massa salarial.

El marc defineix quines files del grup entren en el càlcul per a cada fila concreta. S'escriu ROWS BETWEEN inici AND fi o RANGE BETWEEN inici AND fi, amb aquests extrems:

I la diferència entre les dues paraules clau és exactament aquesta: ROWS compta files físiques —2 PRECEDING són les dues files de sobre, es diguin com es diguin—, mentre que RANGE compta valors de l'ORDER BY: totes les files amb el mateix valor d'ordenació que l'actual són parelles i entren o surten juntes.

(4 primeres de 17 files.) RANGE dona 28 als dos empatats —els suma junts, perquè valen el mateix— i ROWS dona 14 i 28, avançant fila a fila. Els tres productes de 9 unitats repeteixen el patró: 67, 67, 67 amb RANGE; 49, 58, 67 amb ROWS.

El marc per omissió, quan hi ha ORDER BY i no escrius marc, és RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. És a dir: la finestra acaba a la fila actual. I aquí hi ha el parany més famós de tot SQL:

(2 primeres de 12 files.) ultim és sempre la mateixa fila, perquè el marc hi acaba: l'"última fila de la finestra" és l'actual. FIRST_VALUE funciona per casualitat, perquè la primera fila del marc sí que és la primera de totes. La correcció és escriure el marc sencer: LAST_VALUE(facturacio) OVER (ORDER BY mes ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING).

I llavors ultim val 49.33 a les dotze files: la facturació del febrer del 2026. Regla: tan bon punt facis servir LAST_VALUE o NTH_VALUE, escriu el marc explícitament. I compte, tampoc no és cert que "sense ORDER BY no hi ha marc": sense ORDER BY, el marc per omissió és el grup sencer, que és justament el que fa que OVER () doni el total general.

Quan la mateixa finestra apareix tres vegades, es nomena una vegada al final de la consulta —entre el HAVING i l'ORDER BY— i es referencia pel seu nom, com a l'apartat 5: SELECT mes, SUM(facturacio) OVER w, AVG(facturacio) OVER w FROM mv_vendes_mensuals WINDOW w AS (ORDER BY mes ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

Se'n poden declarar diverses separades per comes, i una pot heretar d'una altra: WINDOW w AS (PARTITION BY categoria_id), w2 AS (w ORDER BY preu DESC). És pur sucre sintàctic, però elimina la font d'errors més avorrida: canviar l'ORDER BY en dues de les tres còpies i oblidar-se de la tercera.

Els tres càlculs que demana qualsevol quadre de comandament, en una sola consulta sobre la sèrie de dotze mesos:

(Hi falta la fila del 2025-08: 48,27 € de facturació, 377,28 € d'acumulat, 62,78 € de mitjana mòbil i +3,67 € / +8,23 % respecte al juliol.) Tres lectures i un advertiment. L'acumulat tanca en 727,95 € i passa per 603,52 € al desembre del 2025: són les dues xifres canòniques del curs, i confirmen que la sèrie està bé. La mitjana mòbil de 3 mesos allisa el soroll: la facturació real salta entre 31,70 € i 97,20 €, mentre que la mitjana mòbil es mou en una banda molt més estreta, entre 41,88 € i 71,87 €. I la variació percentual és espectacular però enganyosa: aquest +196,70 % d'octubre no és cap boom comercial, és que el setembre va tenir una sola comanda. Amb volums petits, els percentatges menteixen — i aquesta és una lliçó d'anàlisi, no de SQL.

L'advertiment: les tres primeres files de mitjana_mobil_3 no són mitjanes de tres mesos, sinó d'un i de dos, perquè el marc 2 PRECEDING no té d'on estirar. Si l'informe ha de mostrar només mitjanes completes, cal anul·lar-les amb CASE WHEN COUNT(*) OVER (ORDER BY mes ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 3 THEN ... END.

Amb PARTITION BY, l'acumulat es reinicia. SUM(facturacio) OVER (PARTITION BY left(mes, 4) ORDER BY mes) dona l'acumulat de l'any: el gener del 2026 torna a començar en 75,10 € i el febrer tanca en 124,43 €, la facturació del 2026.

Aquí es tanquen dues promeses: el DISTINCT ON de 02-04 i el LATERAL de 07-04. El patró canònic és ROW_NUMBER dins d'una CTE, filtrat fora:

(6 de 9 files; tanquen Llar sostenible amb les bosses reutilitzables, 39,60 €, i el detergent, 32,48 €.) 9 files en total: quatre categories aporten dos productes i Higiene personal només un, perquè el desodorant no es va vendre mai. Complements no hi apareix: no ha venut res.

I la comparació amb les altres dues maneres, sobre l'exemple de 02-04 —el producte més car de cada categoria—, que les tres resolen amb les mateixes 6 files:

El criteri: top 1 a PostgreSQL i sense necessitat de portabilitat, DISTINCT ON; top N amb moltíssims grups i un índex que ho suporti, LATERAL; en qualsevol altre cas, ROW_NUMBER, que a més et regala la columna de posició.

(4 dels 12 clients amb compres — hi falten Julien Moreau, 4t amb 66,90 €, i Javier Ortega Ruiz, 5è amb 62,93 €; tanquen el Diego amb 31,70 €, l'Elena amb 30,30 € i la Marta amb 29,53 €.) La columna pct_acumulat és una anàlisi de Pareto feta amb una funció de finestra: els 6 primers clients expliquen el 65,89 % de la facturació. I fixa't que NTILE(4) reparteix els 12 clients en quatre quartils d'exactament 3, sense mirar els imports: reparteix per posició, no per valor.

A 07-02 això es resolia amb una subconsulta correlacionada que recorria productes una vegada per fila. Amb PARTITION BY és una sola passada:

(5 de les 20 files.) Els 20 productes hi continuen sent, cadascun amb la mitjana de la seva categoria al costat. El matcha, a 22,00 €, costa gairebé dues vegades i mitja la mitjana de Begudes (8,90 €); l'oli, una mica més del doble de la mitjana d'Alimentació (6,18 €). Cap subconsulta, cap repetició, una sola lectura de productes.

Una funció de finestra obliga el motor a ordenar per PARTITION BY + ORDER BY abans de calcular; a l'EXPLAIN hi veuràs un node WindowAgg gairebé sempre precedit d'un Sort. Tres conseqüències pràctiques: un índex sobre (columna_particio, columna_ordre) pot eliminar aquest Sort; diverses funcions que comparteixen la mateixa finestra es calculen en un sol WindowAgg, així que reutilitzar la finestra (amb WINDOW) també és més ràpid; i filtra abans amb WHERE sempre que puguis, perquè la finestra treballarà sobre menys files. Tot i així, una funció de finestra gairebé sempre guanya a l'alternativa: on una correlacionada fa N passades, la finestra en fa una.

Nota de dialecte: les funcions de finestra són estàndard SQL:2003 i avui són a tot arreu: PostgreSQL (des de la 8.4, amb el conjunt més complet), MySQL 8.0, MariaDB 10.2, SQLite 3.25, SQL Server 2012 i Oracle. Diferències que convé conèixer: SQL Server no admet RANGE amb n PRECEDING (només ROWS) i fins al 2022 no va tenir IGNORE NULLS a LAG/LEAD; MySQL 8 no té la clàusula FILTER; Oracle les anomena analytic functions i hi afegeix KEEP (DENSE_RANK FIRST/LAST); i GROUPS com a tercera modalitat de marc (a més de ROWS i RANGE), juntament amb EXCLUDE, existeix a PostgreSQL 11+ i falta a la majoria.

• Filtrar per una funció de finestra al WHERE o l'HAVING. window functions are not allowed in WHERE. Es calculen després. Embolcalla-la en una CTE o una taula derivada i filtra fora.
• Esperar que LAST_VALUE retorni l'últim valor. Amb el marc per omissió retorna la fila actual. Escriu ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. I al revés: afegir ORDER BY a un SUM() OVER sense voler un acumulat el converteix en acumulat; si vols el total del grup, no hi posis ORDER BY.
• Fer servir ROW_NUMBER sense desempat. Amb valors repetits, quina fila rep l'1 és arbitrari i pot canviar entre execucions. Afegeix sempre una columna única a l'ORDER BY de la finestra.
• Confondre RANK amb DENSE_RANK —el primer deixa forats després d'un empat (1, 1, 3) i el segon no (1, 1, 2)— o creure que RANGE i ROWS són sinònims: només ho són sense empats; amb empats, RANGE els fica tots junts (28 en lloc de 14).
• Oblidar que la finestra només veu el que ha passat el WHERE: un "percentatge sobre el total" en una consulta filtrada és el percentatge sobre el total filtrat. I posar una funció de finestra dins d'un agregat: SUM(ROW_NUMBER() OVER ()) no és vàlid; a l'inrevés sí, SUM(SUM(x)) OVER (...) és correcte i freqüent — l'agregat es calcula primer, la finestra després.
• Consell: comença sempre per OVER () i ves afegint. Primer el total general, després PARTITION BY, després ORDER BY, i només al final el marc. Executa després de cada pas i mira com canvia la columna.
• Consell: ROWS per omissió, que no té sorpreses amb els empats; RANGE només quan el comportament per parelles sigui deliberat. I anomena la finestra amb WINDOW tan bon punt es repeteixi dues vegades: evites canviar l'ORDER BY en una còpia i no a l'altra, i el motor la calcula una sola vegada.

Sobre les 47 línies de comanda, escriu una consulta que mostri, per a cada línia: la comanda, el producte, el seu import, el total de la seva comanda, el percentatge que representa dins de la comanda i la seva posició dins de la comanda per import. Sense GROUP BY, sense subconsultes: només OVER. Comprova a la comanda 1 que els percentatges sumen 100 i el total és 42,10 €.

Direcció vol els tres clients que més facturen de cada país, amb la seva posició. (1) Escriu-ho amb ROW_NUMBER i una CTE. (2) Quantes files retorna i per què no en són 9? (3) Si en lloc de "els tres primers" demanessin "tots els que empaten al tercer lloc", quina funció faries servir?

Un company vol marcar els mesos en què la facturació va superar la mitjana dels dotze mesos i ha escrit SELECT mes, facturacio FROM mv_vendes_mensuals WHERE facturacio > AVG(facturacio) OVER ();. (1) Quin error dona i per què? (2) Corregeix-ho amb una CTE, mostrant també la mitjana i la diferència. (3) Quants mesos superen la mitjana i quina és aquesta mitjana?

Solució 1

(3 primeres de 47 files.) 56,77 + 27,79 + 15,44 = 100,00 i el total de la comanda 1 és 42,10 €, com a 07-04. Les 47 files es conserven: PARTITION BY comanda_id calcula el total per comanda i l'enganxa a cadascuna de les seves línies. Aquí es veu el guany: amb GROUP BY hi hauria 20 files i cap detall.

Solució 2 — 1.

(6 de 7 files; tanca Tiago Almeida Nunes, 2n de Portugal amb 44,60 €.) 2. Set files, no nou, perquè França i Portugal només tenen dos clients amb compres cadascun. ROW_NUMBER no s'inventa files: si un grup en té menys de N, aporta les que tingui. (Espanya té 8 clients amb compres i n'aporta 3.)

3. RANK(), no DENSE_RANK ni ROW_NUMBER. Amb RANK, tots els empatats al tercer valor reben el 3 i WHERE posicio <= 3 els retorna tots. ROW_NUMBER hauria tallat arbitràriament per un; DENSE_RANK n'hauria retornat de més, perquè el seu "3" és el tercer valor diferent, no el tercer lloc.

Solució 3 — 1. Dona ERROR: window functions are not allowed in WHERE. El motor avalua el WHERE abans de calcular les funcions de finestra, així que en aquell moment AVG(facturacio) OVER () encara no existeix; i no podria existir, perquè la finestra necessita saber quines files passen el filtre i el filtre necessita el valor de la finestra. 2 i 3: amb WITH mensual AS (SELECT mes, facturacio, ROUND(AVG(facturacio) OVER (), 2) AS mitjana_anual FROM mv_vendes_mensuals) i després SELECT mes, facturacio, mitjana_anual, ROUND(facturacio - mitjana_anual, 2) AS diferencia FROM mensual WHERE facturacio > mitjana_anual ORDER BY facturacio DESC, en surten sis dels dotze mesos per sobre d'una mitjana de 60,66 € (727,95 € entre 12): 2025-10 amb +36,54 €, 2025-06 amb +34,82 €, 2026-01 amb +14,44 €, 2025-03 amb +8,14 €, 2025-12 amb +3,92 € i 2025-04 amb +0,62 €. La mitjana es calcula una sola vegada a la CTE i queda disponible com a columna normal: per això es pot filtrar per ella i restar-la en el mateix pas.

Les funcions de finestra són l'eina que faltava des del mòdul 4:

• Una funció de finestra calcula sobre un grup de files sense col·lapsar-les: les 47 línies de BotigaVerda surten intactes, cadascuna amb el seu total, el seu percentatge o la seva posició al costat. OVER () és la finestra buida —el total general— i és per on cal començar. L'anatomia és OVER (PARTITION BY ... ORDER BY ... marc), amb les tres parts opcionals, i afegir ORDER BY canvia el resultat d'un agregat: activa el marc "fins a la fila actual" i converteix el total en acumulat.
• S'avaluen després de WHERE, GROUP BY i HAVING, i per això no s'hi pot filtrar al WHERE (window functions are not allowed in WHERE). La solució universal: calcular en una CTE i filtrar fora. No es filtra, s'embolcalla.
• Rànquing: ROW_NUMBER numera sense repetir (1, 2, 3), RANK empata i deixa forat (1, 1, 3), DENSE_RANK empata sense forat (1, 1, 2) — demostrat amb l'empat real de l'arròs i el tomàquet a 14 unitats. A més NTILE per a quartils i PERCENT_RANK per a posicions relatives. Desplaçament: LAG i LEAD amb els seus arguments (columna, n, per_defecte), i FIRST_VALUE / LAST_VALUE / NTH_VALUE, que exigeixen marc explícit.
• El marc: ROWS compta files físiques, RANGE agrupa les files amb el mateix valor d'ordenació (28 en lloc de 14 a l'empat de l'arròs). El marc per omissió amb ORDER BY és RANGE UNBOUNDED PRECEDING AND CURRENT ROW —d'aquí que LAST_VALUE retorni la fila actual i no l'última—; sense ORDER BY, el grup sencer.
• Els casos de BotigaVerda, tots verificats: acumulat mensual que tanca en 727,95 € passant per 603,52 € al desembre; mitjana mòbil de 3 mesos entre 41,88 € i 71,87 €; variació mensual amb el seu +196,70 % enganyós d'octubre; top 2 per categoria amb ROW_NUMBER (9 files) i la taula que el confronta amb DISTINCT ON i amb LATERAL; rànquing de clients amb el Pareto que dona un 65,89 % acumulat en els sis primers; i cada producte enfront de la mitjana de la seva categoria en una sola passada, on 07-02 feia una correlacionada.

Amb això ja no hi ha cap pregunta analítica sobre BotigaVerda que no sàpigues escriure. Però tot el que has fet fins ara viu en una sentència: s'escriu, s'executa i s'oblida. El següent és desar lògica, no només consultes. A la lliçó següent, procediments emmagatzemats, veuràs codi que viu dins de la base de dades: la diferència real entre una funció i un procediment a PostgreSQL, el just de PL/pgSQL per escriure alguna cosa útil —variables, IF, bucles, RAISE, gestió d'excepcions—, i tres exemples en ordre de dificultat que acaben en sp_confirmar_comanda, el procediment on per fi viu la lògica transaccional que vas construir a mà a 09-03. I, sobretot, la discussió honesta sobre què val la pena posar-hi a dins i què no.