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

HOME

Introducció

Les Expressions de Taula Comunes (CTEs) són una característica poderosa de SQL que permet definir subconsultes temporals que poden ser referenciades dins de la consulta principal. Les CTEs milloren la llegibilitat i la mantenibilitat del codi SQL, especialment quan es treballa amb consultes complexes.

Conceptes clau

Definició de CTE: Una CTE és una subconsulta que es defineix una vegada i es pot utilitzar múltiples vegades dins de la consulta principal.
Sintaxi: Les CTEs es defineixen utilitzant la clàusula WITH seguida del nom de la CTE i la subconsulta que la defineix.

Sintaxi bàsica

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE another_condition;

Exemple pràctic

Escenari

Suposem que tenim una taula sales amb les següents columnes: sale_id, product_id, quantity, price, sale_date. Volem calcular el total de vendes per producte i després filtrar els productes que han generat més de 1000 unitats venudes.

Pas 1: Definir la CTE

Primer, definim una CTE per calcular el total de vendes per producte.

WITH total_sales AS (
    SELECT product_id, SUM(quantity) AS total_quantity
    FROM sales
    GROUP BY product_id
)

Pas 2: Utilitzar la CTE

Després, utilitzem la CTE per filtrar els productes que han venut més de 1000 unitats.

WITH total_sales AS (
    SELECT product_id, SUM(quantity) AS total_quantity
    FROM sales
    GROUP BY product_id
)
SELECT product_id, total_quantity
FROM total_sales
WHERE total_quantity > 1000;

Explicació del codi

Definició de la CTE: La CTE total_sales calcula el total de quantitats venudes per cada producte.
Consulta principal: La consulta principal selecciona els productes de la CTE total_sales on el total de quantitats venudes és superior a 1000.

Avantatges de les CTEs

Millora de la llegibilitat: Les CTEs permeten dividir consultes complexes en parts més manejables i comprensibles.
Reutilització de codi: Les CTEs es poden referenciar múltiples vegades dins de la mateixa consulta, evitant la duplicació de codi.
Facilitat de depuració: Les CTEs permeten aïllar parts de la consulta per facilitar la depuració i el manteniment.

Exercicis pràctics

Exercici 1

Descripció: Utilitza una CTE per calcular el salari mitjà per departament i després filtra els departaments amb un salari mitjà superior a 50000.

Taula: employees amb columnes employee_id, department_id, salary.

Solució:

WITH avg_salary AS (
    SELECT department_id, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
)
SELECT department_id, average_salary
FROM avg_salary
WHERE average_salary > 50000;

Exercici 2

Descripció: Utilitza una CTE per trobar els clients que han fet més de 5 compres en el darrer any.

Taula: purchases amb columnes purchase_id, customer_id, purchase_date.

Solució:

WITH recent_purchases AS (
    SELECT customer_id, COUNT(purchase_id) AS purchase_count
    FROM purchases
    WHERE purchase_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
    GROUP BY customer_id
)
SELECT customer_id, purchase_count
FROM recent_purchases
WHERE purchase_count > 5;

Errors comuns

No utilitzar la clàusula WITH correctament: Assegura't que la clàusula WITH estigui seguida immediatament pel nom de la CTE i la subconsulta.
Referenciar columnes incorrectament: Quan utilitzis una CTE, assegura't que les columnes referenciades existeixin en la subconsulta de la CTE.

Consells addicionals

Nesting de CTEs: Pots definir múltiples CTEs en una sola consulta, separant-les amb comes.
CTEs recursives: Les CTEs també poden ser recursives, permetent la definició de subconsultes que es referencien a si mateixes.

Conclusió

Les Expressions de Taula Comunes (CTEs) són una eina essencial per a qualsevol programador SQL que treballi amb consultes complexes. Milloren la llegibilitat, la reutilització del codi i faciliten la depuració. Practicar amb CTEs t'ajudarà a escriure codi SQL més net i eficient.

Expressions de taula comunes (CTEs)

Introducció

Conceptes clau

Sintaxi bàsica

Exemple pràctic

Escenari

Pas 1: Definir la CTE

Pas 2: Utilitzar la CTE

Explicació del codi

Avantatges de les CTEs

Exercicis pràctics

Exercici 1

Exercici 2

Errors comuns

Consells addicionals

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