Repàs de SQL avançat

On es treballa el SQL des de zero

Els fonaments de SQL (SELECT, JOIN, subconsultes, GROUP BY, window functions bàsiques...) es treballen íntegrament al mòdul M0372 — Gestió de Bases de Dades:

Curs M0372

Window Functions (M0372)

Aquest bloc dona per fet que ja domines el SQL treballat al mòdul M0372 (Gestió de Bases de Dades): SELECT/FROM/WHERE, JOIN, subconsultes, GROUP BY/HAVING i les window functions bàsiques (ROW_NUMBER, RANK, LAG/LEAD, OVER (PARTITION BY ...)).

Aquesta pàgina no és matèria nova ni té sessió pròpia: és material de repàs i aprofundiment, pensat perquè el revisis pel teu compte abans (o durant) les primeres sessions del bloc. Les tècniques que veuràs aquí — CTEs recursives, window functions amb frames explícits, GROUPING SETS, MERGE, LATERAL/APPLY — apareixen sovint a l'hora de reescriure queries lentes (Optimització de queries) i a la pràctica avaluable del bloc.

Per què un repàs de SQL en un mòdul de Big Data

Abans d'optimitzar una consulta (índexs, particionament, plans d'execució) cal saber-la escriure bé. Moltes consultes "lentes" no ho són per manca d'índexs, sinó perquè estan mal plantejades: un JOIN que hauria de ser una window function, una subconsulta correlacionada que hauria de ser un LATERAL, o un GROUP BY repetit tres vegades que un sol GROUPING SETS resoldria en una passada.

---

CTEs recursives

Una Common Table Expression (CTE) és una consulta amb nom que es defineix amb WITH i es pot reutilitzar dins de la consulta principal. Una CTE recursiva es defineix a partir de si mateixa, i és la manera estàndard de recórrer estructures jeràrquiques (organigrames, categories niuades, arbres de comentaris) en SQL.

Una CTE recursiva té sempre dues parts unides per UNION ALL: el cas base (l'arrel de la jerarquia) i el cas recursiu (que fa referència a la pròpia CTE i s'atura quan no troba més files).

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Organigrama: tots els subordinats (directes i indirectes) d'un empleat
WITH RECURSIVE cadena_comandament AS (
    -- Cas base: l'empleat arrel
    SELECT id, nom, cap_id, 1 AS nivell
    FROM empleats
    WHERE id = 1

    UNION ALL

    -- Cas recursiu: els subordinats de cada fila ja trobada
    SELECT e.id, e.nom, e.cap_id, cc.nivell + 1
    FROM empleats e
    INNER JOIN cadena_comandament cc ON e.cap_id = cc.id
)
SELECT * FROM cadena_comandament ORDER BY nivell;

-- Suportat des de MySQL 8.0 / MariaDB 10.2 amb la mateixa sintaxi WITH RECURSIVE
WITH RECURSIVE cadena_comandament AS (
    SELECT id, nom, cap_id, 1 AS nivell
    FROM empleats
    WHERE id = 1

    UNION ALL

    SELECT e.id, e.nom, e.cap_id, cc.nivell + 1
    FROM empleats e
    INNER JOIN cadena_comandament cc ON e.cap_id = cc.id
)
SELECT * FROM cadena_comandament ORDER BY nivell;

-- SQL Server admet WITH RECURSIVE (sense la paraula RECURSIVE explícita)
WITH cadena_comandament AS (
    SELECT id, nom, cap_id, 1 AS nivell
    FROM empleats
    WHERE id = 1

    UNION ALL

    SELECT e.id, e.nom, e.cap_id, cc.nivell + 1
    FROM empleats e
    INNER JOIN cadena_comandament cc ON e.cap_id = cc.id
)
SELECT * FROM cadena_comandament
ORDER BY nivell
OPTION (MAXRECURSION 100);  -- límit de seguretat, per defecte 100

-- Oracle admet WITH ... (RECURSIVE opcional des d'11g R2) i també l'alternativa clàssica CONNECT BY
WITH cadena_comandament (id, nom, cap_id, nivell) AS (
    SELECT id, nom, cap_id, 1
    FROM empleats
    WHERE id = 1

    UNION ALL

    SELECT e.id, e.nom, e.cap_id, cc.nivell + 1
    FROM empleats e
    INNER JOIN cadena_comandament cc ON e.cap_id = cc.id
)
SELECT * FROM cadena_comandament ORDER BY nivell;

-- Alternativa idiomàtica d'Oracle, anterior a les CTE recursives:
SELECT id, nom, cap_id, LEVEL AS nivell
FROM empleats
START WITH id = 1
CONNECT BY PRIOR id = cap_id;

Recursió infinita

Si les dades tenen un cicle (per error, un empleat que és cap del seu propi cap) i no hi ha límit de recursió, la consulta no acabarà mai o exhaurirà la memòria. PostgreSQL i MySQL no tenen límit per defecte (cal afegir-lo manualment amb una condició WHERE nivell < N al cas recursiu); SQL Server el limita a 100 nivells per defecte (OPTION (MAXRECURSION n) per canviar-ho).

---

Window functions avançades: frames explícits

El repàs de M372 cobreix ROW_NUMBER, RANK i LAG/LEAD amb PARTITION BY. El que sovint no s'explicita és la clàusula de frame (ROWS BETWEEN / RANGE BETWEEN), que defineix exactament quines files "veu" la finestra per a cada fila — imprescindible per calcular mitjanes mòbils o acumulats parcials.

-- Sintaxi general del frame (PostgreSQL, MySQL 8+, SQL Server, Oracle: idèntica)
funció_finestra() OVER (
    PARTITION BY columna
    ORDER BY columna_ordre
    ROWS BETWEEN <inici> AND <final>
)

Expressió	Significat
UNBOUNDED PRECEDING	Des de la primera fila de la partició
N PRECEDING	Les N files anteriors
CURRENT ROW	La fila actual
N FOLLOWING	Les N files següents
UNBOUNDED FOLLOWING	Fins a l'última fila de la partició

-- Mitjana mòbil de 3 dies (l'actual i els 2 anteriors) — vàlid als 4 motors
SELECT
    data_venda,
    import_total,
    AVG(import_total) OVER (
        ORDER BY data_venda
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS mitjana_mobil_3dies,
    SUM(import_total) OVER (
        ORDER BY data_venda
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS acumulat_total
FROM vendes;

ROWS vs RANGE: no són el mateix

ROWS BETWEEN compta files físiques. RANGE BETWEEN agrupa per valor de la columna d'ORDER BY: si dues files empaten en el valor (per exemple, dues vendes el mateix dia), RANGE les tracta com una sola posició de la finestra i totes dues reben el mateix resultat, mentre que ROWS les tracta per separat. Quan hi pot haver empats a la columna d'ordenació, RANGE sol donar el resultat que s'espera intuïtivament; per defecte (sense especificar ROWS/RANGE), el motor usa RANGE UNBOUNDED PRECEDING.

Altres funcions de finestra útils més enllà de ROW_NUMBER/RANK/LAG/LEAD:

• NTILE(n): reparteix les files en n grups de mida (gairebé) igual — útil per a quartils, decils, segmentació de clients per volum de compra.
• PERCENT_RANK() i CUME_DIST(): posició relativa (0 a 1) d'una fila dins la seva partició, útils per a percentils.
• FIRST_VALUE() / LAST_VALUE(): primer o últim valor de la finestra (amb LAST_VALUE, cal anar amb compte amb el frame per defecte — sol requerir ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING per obtenir el resultat esperat).

-- Segmentació de clients en 4 grups (quartils) segons import total gastat
SELECT
    client_id,
    SUM(import_total) AS total_gastat,
    NTILE(4) OVER (ORDER BY SUM(import_total) DESC) AS quartil
FROM vendes
GROUP BY client_id;

---

Agregació condicional i GROUPING SETS / ROLLUP / CUBE

Un patró molt habitual és calcular diverses agregacions amb condicions diferents en una sola passada, en lloc de llançar diverses queries o fer diversos JOIN:

-- Agregació condicional: una sola passada per la taula, quatre mètriques
SELECT
    categoria,
    COUNT(*) AS total_comandes,
    SUM(CASE WHEN estat = 'completat' THEN 1 ELSE 0 END) AS completades,
    SUM(CASE WHEN estat = 'cancel·lat' THEN 1 ELSE 0 END) AS cancel·lades,
    ROUND(
        100.0 * SUM(CASE WHEN estat = 'completat' THEN 1 ELSE 0 END) / COUNT(*), 1
    ) AS pct_completades
FROM comandes
GROUP BY categoria;

Quan calen subtotals a diversos nivells de detall alhora (per categoria, per any, i el total general, tot en un sol resultat), GROUP BY sol no n'hi ha prou. GROUPING SETS, ROLLUP i CUBE generen diverses agrupacions en una sola consulta:

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- ROLLUP: subtotals jeràrquics (categoria+any, categoria, total general)
SELECT categoria, EXTRACT(YEAR FROM data_venda) AS any_venda, SUM(import_total) AS total
FROM vendes
GROUP BY ROLLUP (categoria, any_venda);

-- GROUPING SETS: només les combinacions que interessen (control total)
SELECT categoria, EXTRACT(YEAR FROM data_venda) AS any_venda, SUM(import_total) AS total
FROM vendes
GROUP BY GROUPING SETS (
    (categoria, any_venda),
    (categoria),
    ()  -- total general
);

-- MySQL suporta ROLLUP (sintaxi WITH ROLLUP), no GROUPING SETS ni CUBE nadius
SELECT categoria, YEAR(data_venda) AS any_venda, SUM(import_total) AS total
FROM vendes
GROUP BY categoria, any_venda WITH ROLLUP;

-- SQL Server suporta els tres: ROLLUP, CUBE i GROUPING SETS
SELECT categoria, YEAR(data_venda) AS any_venda, SUM(import_total) AS total
FROM vendes
GROUP BY GROUPING SETS (
    (categoria, YEAR(data_venda)),
    (categoria),
    ()
);

-- Oracle suporta els tres, amb la mateixa sintaxi estàndard
SELECT categoria, EXTRACT(YEAR FROM data_venda) AS any_venda, SUM(import_total) AS total
FROM vendes
GROUP BY GROUPING SETS (
    (categoria, EXTRACT(YEAR FROM data_venda)),
    (categoria),
    ()
);

CUBE vs ROLLUP vs GROUPING SETS

ROLLUP(a, b) genera subtotals jeràrquics: (a,b), (a), () — respecta l'ordre. CUBE(a, b) genera totes les combinacions possibles: (a,b), (a), (b), (). GROUPING SETS et deixa triar exactament quines combinacions vols, sense les que no necessitis. ROLLUP i CUBE són, de fet, casos particulars de GROUPING SETS.

---

MERGE / UPSERT: inserir o actualitzar en una sola sentència

Un patró molt freqüent en càrregues ETL és: "si la fila ja existeix, actualitza-la; si no existeix, insereix-la" (conegut com a upsert). Fer-ho amb un SELECT previ i després un INSERT o UPDATE condicional té una condició de carrera: entre el SELECT i l'INSERT, una altra transacció podria haver inserit la mateixa fila.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- ON CONFLICT: la forma idiomàtica de PostgreSQL (des de la 9.5)
INSERT INTO estoc (producte_id, quantitat, ultima_actualitzacio)
VALUES (101, 50, NOW())
ON CONFLICT (producte_id)
DO UPDATE SET
    quantitat = estoc.quantitat + EXCLUDED.quantitat,
    ultima_actualitzacio = NOW();

-- ON DUPLICATE KEY UPDATE: requereix una clau UNIQUE o PRIMARY KEY a producte_id
INSERT INTO estoc (producte_id, quantitat, ultima_actualitzacio)
VALUES (101, 50, NOW())
ON DUPLICATE KEY UPDATE
    quantitat = quantitat + VALUES(quantitat),
    ultima_actualitzacio = NOW();

-- MERGE: l'estàndard SQL complet, també disponible a Oracle i PostgreSQL 15+
MERGE INTO estoc AS desti
USING (SELECT 101 AS producte_id, 50 AS quantitat) AS origen
    ON desti.producte_id = origen.producte_id
WHEN MATCHED THEN
    UPDATE SET
        quantitat = desti.quantitat + origen.quantitat,
        ultima_actualitzacio = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (producte_id, quantitat, ultima_actualitzacio)
    VALUES (origen.producte_id, origen.quantitat, GETDATE());

-- MERGE, la sentència que va originar l'estàndard SQL:2003
MERGE INTO estoc desti
USING (SELECT 101 AS producte_id, 50 AS quantitat FROM dual) origen
    ON (desti.producte_id = origen.producte_id)
WHEN MATCHED THEN
    UPDATE SET
        quantitat = desti.quantitat + origen.quantitat,
        ultima_actualitzacio = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (producte_id, quantitat, ultima_actualitzacio)
    VALUES (origen.producte_id, origen.quantitat, SYSDATE);

MERGE també a PostgreSQL des de la 15

PostgreSQL 15 va afegir MERGE amb sintaxi pràcticament idèntica a la d'Oracle/SQL Server, com a alternativa a ON CONFLICT quan la lògica és més complexa (per exemple, WHEN MATCHED AND condició THEN ... amb diverses branques).

---

LATERAL / APPLY: subconsultes correlacionades al FROM

Una subconsulta normal al FROM no pot fer referència a columnes d'altres taules de la mateixa consulta. LATERAL (PostgreSQL, MySQL 8+) i CROSS APPLY/OUTER APPLY (SQL Server) ho permeten: és la manera idiomàtica de resoldre el patró "top N per grup".

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Les 3 comandes més recents de CADA client (no del total)
SELECT c.nom, ultimes.data_comanda, ultimes.import_total
FROM clients c
CROSS JOIN LATERAL (
    SELECT data_comanda, import_total
    FROM comandes
    WHERE comandes.client_id = c.id
    ORDER BY data_comanda DESC
    LIMIT 3
) AS ultimes;

-- LATERAL disponible des de MySQL 8.0.14
SELECT c.nom, ultimes.data_comanda, ultimes.import_total
FROM clients c,
LATERAL (
    SELECT data_comanda, import_total
    FROM comandes
    WHERE comandes.client_id = c.id
    ORDER BY data_comanda DESC
    LIMIT 3
) AS ultimes;

-- CROSS APPLY: equivalent a un LATERAL JOIN (INNER); OUTER APPLY equival a un LEFT JOIN LATERAL
SELECT c.nom, ultimes.data_comanda, ultimes.import_total
FROM clients c
CROSS APPLY (
    SELECT TOP 3 data_comanda, import_total
    FROM comandes
    WHERE comandes.client_id = c.id
    ORDER BY data_comanda DESC
) AS ultimes;

-- Oracle 12c+ suporta LATERAL de manera explícita; alternativa clàssica: window function + filtre
SELECT c.nom, ultimes.data_comanda, ultimes.import_total
FROM clients c
CROSS JOIN LATERAL (
    SELECT data_comanda, import_total
    FROM comandes
    WHERE comandes.client_id = c.id
    ORDER BY data_comanda DESC
    FETCH FIRST 3 ROWS ONLY
) ultimes;

Alternativa sense LATERAL: window function + filtre

El mateix resultat es pot obtenir amb ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY data_comanda DESC) dins d'una CTE i un WHERE row_num <= 3 a la consulta externa. Sovint és més llegible per a "top N global per grup", mentre que LATERAL/APPLY és més flexible quan la subconsulta necessita lògica addicional (agregacions pròpies, diverses columnes calculades).

---

Operadors de conjunts: UNION, INTERSECT, EXCEPT

Combinen els resultats de diverses consultes amb el mateix nombre de columnes i tipus compatibles:

Operador	Significat	A SQL Server / Oracle
UNION	Unió, eliminant duplicats	Igual
UNION ALL	Unió, conservant duplicats (més ràpid: no cal ordenar/deduplicar)	Igual
INTERSECT	Files presents a totes dues consultes	Igual
EXCEPT	Files de la primera consulta absents a la segona	A Oracle: MINUS

-- Clients que han comprat el 2025 PERÒ NO el 2024 (churn candidats)
SELECT client_id FROM comandes WHERE EXTRACT(YEAR FROM data_comanda) = 2024
EXCEPT
SELECT client_id FROM comandes WHERE EXTRACT(YEAR FROM data_comanda) = 2025;
-- A Oracle: substituir EXCEPT per MINUS

UNION ALL per defecte si no cal deduplicar

UNION fa un DISTINCT implícit sobre tot el resultat combinat, cosa que té un cost real en consultes grans (cal ordenar o fer servir una taula hash per detectar duplicats). Si saps que els conjunts no es solapen (per exemple, per construcció, com particions per any), fes servir sempre UNION ALL.

---

Exercici pràctic

Sense necessitat de cap dataset extern: sobre la mateixa base de dades de vendes que faràs servir a la pràctica PR507402 (o una versió reduïda de prova), escriu les consultes següents aplicant les tècniques d'aquesta pàgina:

1. Una CTE recursiva que, donada una taula categories amb una columna categoria_pare_id, llisti totes les subcategories (a qualsevol profunditat) d'una categoria arrel.
2. Una window function amb frame explícit que calculi la mitjana mòbil de 7 dies de les vendes diàries.
3. Una consulta amb GROUPING SETS (o ROLLUP) que retorni, en un sol resultat, el total de vendes per categoria, per any, i el total general.
4. Un MERGE/UPSERT que actualitzi l'estoc d'un producte si ja existeix o l'insereixi si no.
5. Una consulta amb LATERAL/CROSS APPLY (o l'alternativa amb window function) que trobi les 3 comandes de més import de cada client.

Guarda les cinc consultes en un fitxer .sql comentat: no cal lliurar-lo, però serà la base per a les reescriptures de queries que faràs a l'activitat AC5074/02/04.

---

Bloc 2 | Mòdul M5074 Sistemes de Big Data | Institut Sa Palomera (Blanes) | Curs CEIABD 2026-2027