El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

Les funcions de finestra (o "window functions") són una característica poderosa de SQL que permet realitzar càlculs sobre un conjunt de files relacionades amb la fila actual. A diferència de les funcions agregades, que retornen un únic valor per a un conjunt de files, les funcions de finestra poden retornar múltiples valors per a cada fila del conjunt.

Conceptes clau

Partició: Divideix les files en grups sobre els quals s'aplicarà la funció de finestra.
Ordre: Defineix l'ordre en què es processaran les files dins de cada partició.
Marc de la finestra: Especifica el rang de files que s'inclouran en el càlcul per a cada fila.

Sintaxi bàsica

funció_de_finestra() OVER (
    [PARTITION BY columna1, columna2, ...]
    [ORDER BY columna1 [ASC|DESC], columna2 [ASC|DESC], ...]
    [ROWS|RANGE BETWEEN ...]
)

Exemples pràctics

Funció `ROW_NUMBER()`

La funció ROW_NUMBER() assigna un número únic a cada fila dins de la partició d'un conjunt de resultats.

SELECT
    id,
    nom,
    departament,
    ROW_NUMBER() OVER (PARTITION BY departament ORDER BY nom) AS fila_num
FROM
    empleats;

Explicació:

PARTITION BY departament: Divideix les files per departament.
ORDER BY nom: Ordena les files dins de cada departament per nom.
ROW_NUMBER(): Assigna un número de fila únic dins de cada partició.

Funció `RANK()`

La funció RANK() assigna un rang a cada fila dins de la partició d'un conjunt de resultats, amb l'opció de tenir rangs duplicats.

SELECT
    id,
    nom,
    departament,
    salari,
    RANK() OVER (PARTITION BY departament ORDER BY salari DESC) AS rang
FROM
    empleats;

Explicació:

PARTITION BY departament: Divideix les files per departament.
ORDER BY salari DESC: Ordena les files dins de cada departament per salari en ordre descendent.
RANK(): Assigna un rang a cada fila dins de cada partició, permetent rangs duplicats per a salaris iguals.

Funció `DENSE_RANK()`

La funció DENSE_RANK() és similar a RANK(), però no deixa espais en els rangs.

SELECT
    id,
    nom,
    departament,
    salari,
    DENSE_RANK() OVER (PARTITION BY departament ORDER BY salari DESC) AS rang_dens
FROM
    empleats;

Explicació:

PARTITION BY departament: Divideix les files per departament.
ORDER BY salari DESC: Ordena les files dins de cada departament per salari en ordre descendent.
DENSE_RANK(): Assigna un rang dens a cada fila dins de cada partició, sense deixar espais en els rangs.

Funció `SUM()`

La funció SUM() pot ser utilitzada com a funció de finestra per calcular la suma acumulativa.

SELECT
    id,
    nom,
    departament,
    salari,
    SUM(salari) OVER (PARTITION BY departament ORDER BY nom) AS suma_acumulativa
FROM
    empleats;

Explicació:

PARTITION BY departament: Divideix les files per departament.
ORDER BY nom: Ordena les files dins de cada departament per nom.
SUM(salari): Calcula la suma acumulativa del salari dins de cada partició.

Exercicis pràctics

Exercici 1: Assignar números de fila

Crea una consulta que assigni un número de fila únic a cada empleat dins de cada departament, ordenat per data de contractació.

SELECT
    id,
    nom,
    departament,
    data_contractacio,
    ROW_NUMBER() OVER (PARTITION BY departament ORDER BY data_contractacio) AS fila_num
FROM
    empleats;

Exercici 2: Calcular el rang de salaris

Crea una consulta que assigni un rang als empleats dins de cada departament basat en el seu salari, en ordre descendent.

SELECT
    id,
    nom,
    departament,
    salari,
    RANK() OVER (PARTITION BY departament ORDER BY salari DESC) AS rang
FROM
    empleats;

Exercici 3: Suma acumulativa de vendes

Crea una consulta que calculi la suma acumulativa de les vendes per a cada venedor, ordenades per data de venda.

SELECT
    venedor_id,
    data_venda,
    import_venda,
    SUM(import_venda) OVER (PARTITION BY venedor_id ORDER BY data_venda) AS suma_acumulativa
FROM
    vendes;

Resum

Les funcions de finestra són una eina poderosa per realitzar càlculs complexos en SQL. Permeten treballar amb conjunts de files relacionades i realitzar càlculs com numeració de files, rangs, i sumes acumulatives. Comprendre com utilitzar aquestes funcions pot millorar significativament la capacitat d'analitzar i manipular dades en PostgreSQL.

Funcions de finestra

Conceptes clau

Sintaxi bàsica

Exemples pràctics

Funció `ROW_NUMBER()`

Funció `RANK()`

Funció `DENSE_RANK()`

Funció `SUM()`

Exercicis pràctics

Exercici 1: Assignar números de fila

Exercici 2: Calcular el rang de salaris

Exercici 3: Suma acumulativa de vendes

Resum

Curs de PostgreSQL

Mòdul 1: Introducció a PostgreSQL

Mòdul 2: Operacions bàsiques de SQL

Mòdul 3: Consultes SQL avançades

Mòdul 4: Disseny de bases de dades i normalització

Mòdul 5: Funcionalitats avançades de PostgreSQL

Mòdul 6: Optimització i millora del rendiment

Mòdul 7: Seguretat i gestió d'usuaris

Mòdul 8: Treballant amb JSON i funcionalitats NoSQL

Mòdul 9: Extensions i eines avançades

Mòdul 10: Estudis de cas i aplicacions del món real

Funcions de finestra

Conceptes clau

Sintaxi bàsica

Exemples pràctics

Funció ROW_NUMBER()

Funció RANK()

Funció DENSE_RANK()

Funció SUM()

Exercicis pràctics

Exercici 1: Assignar números de fila

Exercici 2: Calcular el rang de salaris

Exercici 3: Suma acumulativa de vendes

Resum

Curs de PostgreSQL

Mòdul 1: Introducció a PostgreSQL

Mòdul 2: Operacions bàsiques de SQL

Mòdul 3: Consultes SQL avançades

Mòdul 4: Disseny de bases de dades i normalització

Mòdul 5: Funcionalitats avançades de PostgreSQL

Mòdul 6: Optimització i millora del rendiment

Mòdul 7: Seguretat i gestió d'usuaris

Mòdul 8: Treballant amb JSON i funcionalitats NoSQL

Mòdul 9: Extensions i eines avançades

Mòdul 10: Estudis de cas i aplicacions del món real

Funció `ROW_NUMBER()`

Funció `RANK()`

Funció `DENSE_RANK()`

Funció `SUM()`