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

HOME

Les funcions de finestra (o funcions de finestra analítica) són una característica poderosa de SQL que permet realitzar càlculs sobre un conjunt de files relacionades amb la fila actual. Aquestes funcions són molt útils per a tasques com càlculs acumulatius, classificacions i altres operacions que necessiten accedir a múltiples files en una taula.

Conceptes clau

Abans de començar amb les funcions de finestra, és important comprendre alguns conceptes clau:

Partició: Un conjunt de files sobre les quals s'aplica la funció de finestra. És similar a un "grup" en les funcions d'agregació.
Ordre: La seqüència en què es processen les files dins de cada partició.
Marc de la finestra: Un subconjunt de files dins de la partició que es consideren per a cada fila en particular.

Sintaxi bàsica

La sintaxi general per a una funció de finestra és la següent:

<funció_de_finestra>() OVER (
    [PARTITION BY <columna1>, <columna2>, ...]
    [ORDER BY <columna1> [ASC|DESC], <columna2> [ASC|DESC], ...]
    [ROWS BETWEEN <inici> AND <final>]
)

Funcions de finestra comunes

`ROW_NUMBER()`

Aquesta funció assigna un número únic a cada fila dins de la partició, començant per 1.

SELECT
    columna1,
    columna2,
    ROW_NUMBER() OVER (PARTITION BY columna1 ORDER BY columna2) AS fila_num
FROM
    taula;

`RANK()`

Aquesta funció assigna un rang a cada fila dins de la partició, amb files amb valors iguals rebent el mateix rang. Els rangs poden tenir salts.

SELECT
    columna1,
    columna2,
    RANK() OVER (PARTITION BY columna1 ORDER BY columna2) AS rang
FROM
    taula;

`DENSE_RANK()`

Similar a RANK(), però sense salts en els rangs.

SELECT
    columna1,
    columna2,
    DENSE_RANK() OVER (PARTITION BY columna1 ORDER BY columna2) AS rang_dens
FROM
    taula;

`SUM()`

Aquesta funció calcula la suma acumulativa de valors dins de la partició.

SELECT
    columna1,
    columna2,
    SUM(columna2) OVER (PARTITION BY columna1 ORDER BY columna2) AS suma_acumulativa
FROM
    taula;

`AVG()`

Aquesta funció calcula la mitjana acumulativa de valors dins de la partició.

SELECT
    columna1,
    columna2,
    AVG(columna2) OVER (PARTITION BY columna1 ORDER BY columna2) AS mitjana_acumulativa
FROM
    taula;

Exemple pràctic

Suposem que tenim una taula vendes amb les següents columnes: vendedor, mes i vendes.

CREATE TABLE vendes (
    vendedor STRING,
    mes INT64,
    vendes INT64
);

INSERT INTO vendes (vendedor, mes, vendes) VALUES
('Anna', 1, 100),
('Anna', 2, 150),
('Anna', 3, 200),
('Bernat', 1, 120),
('Bernat', 2, 180),
('Bernat', 3, 160);

Càlcul de la suma acumulativa de vendes per cada venedor

SELECT
    vendedor,
    mes,
    vendes,
    SUM(vendes) OVER (PARTITION BY vendedor ORDER BY mes) AS suma_acumulativa
FROM
    vendes;

Resultat:

vendedor	mes	vendes	suma_acumulativa
Anna	1	100	100
Anna	2	150	250
Anna	3	200	450
Bernat	1	120	120
Bernat	2	180	300
Bernat	3	160	460

Exercicis pràctics

Exercici 1

Calcula el rang de vendes per cada venedor en cada mes.

SELECT
    vendedor,
    mes,
    vendes,
    RANK() OVER (PARTITION BY vendedor ORDER BY vendes DESC) AS rang_vendes
FROM
    vendes;

Exercici 2

Calcula la mitjana acumulativa de vendes per cada venedor.

SELECT
    vendedor,
    mes,
    vendes,
    AVG(vendes) OVER (PARTITION BY vendedor ORDER BY mes) AS mitjana_acumulativa
FROM
    vendes;

Conclusió

Les funcions de finestra són una eina poderosa per a l'anàlisi de dades en SQL. Permeten realitzar càlculs complexos de manera eficient i són essencials per a qualsevol analista de dades que treballi amb grans conjunts de dades. En el proper mòdul, explorarem altres tècniques avançades de SQL a BigQuery.

Funcions de finestra

Conceptes clau

Sintaxi bàsica

Funcions de finestra comunes

`ROW_NUMBER()`

`RANK()`

`DENSE_RANK()`

`SUM()`

`AVG()`

Exemple pràctic

Càlcul de la suma acumulativa de vendes per cada venedor

Exercicis pràctics

Exercici 1

Exercici 2

Conclusió

Curs de BigQuery

Mòdul 1: Introducció a BigQuery

Mòdul 2: SQL bàsic a BigQuery

Mòdul 3: SQL intermedi a BigQuery

Mòdul 4: SQL avançat a BigQuery

Mòdul 5: Gestió de dades a BigQuery

Mòdul 6: Optimització del rendiment de BigQuery

Mòdul 7: Seguretat i compliment de BigQuery

Mòdul 8: Integració i automatització de BigQuery

Mòdul 9: Aprenentatge automàtic a BigQuery (BQML)

Mòdul 10: Casos d'ús de BigQuery en el món real