Introducción

Las Expresiones de Tabla Comunes (CTEs, por sus siglas en inglés) son una característica poderosa de SQL que permite crear tablas temporales que pueden ser referenciadas dentro de una consulta. Las CTEs mejoran la legibilidad y la organización de las consultas SQL, especialmente cuando se trabaja con consultas complejas.

Conceptos Clave

CTE: Una CTE es una tabla temporal que se define dentro de una consulta SQL.
WITH Clause: La cláusula WITH se utiliza para definir una CTE.
Recursividad: Las CTEs pueden ser recursivas, lo que permite realizar operaciones repetitivas como el procesamiento de jerarquías.

Sintaxis Básica

La sintaxis básica para definir una CTE es la siguiente:

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

Ejemplo Práctico

Ejemplo 1: CTE Simple

Supongamos que tenemos una tabla employees con las siguientes columnas: employee_id, first_name, last_name, department, y salary. Queremos obtener una lista de empleados que ganan más de $50,000.

WITH high_earners AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE salary > 50000
)
SELECT *
FROM high_earners;

Explicación:

La CTE high_earners selecciona empleados con un salario superior a $50,000.
La consulta principal selecciona todos los campos de la CTE high_earners.

Ejemplo 2: CTE Recursiva

Supongamos que tenemos una tabla employees con una estructura jerárquica donde cada empleado tiene un manager_id. Queremos obtener una lista de todos los empleados y sus respectivos niveles jerárquicos.

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, first_name, last_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM employee_hierarchy;

Explicación:

La CTE employee_hierarchy comienza seleccionando empleados sin un manager_id (nivel 1).
La parte recursiva de la CTE une la tabla employees con la CTE employee_hierarchy para encontrar empleados en niveles superiores.
La consulta principal selecciona todos los campos de la CTE employee_hierarchy.

Ejercicios Prácticos

Ejercicio 1: Filtrar Empleados por Departamento

Objetivo: Crear una CTE que filtre empleados del departamento 'Sales' y luego seleccione sus nombres y salarios.

Instrucciones:

Define una CTE llamada sales_employees.
Filtra empleados cuyo department sea 'Sales'.
Selecciona first_name, last_name, y salary de la CTE.

Solución:

WITH sales_employees AS (
    SELECT first_name, last_name, salary
    FROM employees
    WHERE department = 'Sales'
)
SELECT first_name, last_name, salary
FROM sales_employees;

Ejercicio 2: Calcular el Salario Promedio por Departamento

Objetivo: Crear una CTE que calcule el salario promedio por departamento y luego seleccione los departamentos con un salario promedio superior a $60,000.

Instrucciones:

Define una CTE llamada avg_salary_by_dept.
Calcula el salario promedio (AVG(salary)) agrupado por department.
Selecciona department y avg_salary de la CTE donde avg_salary sea mayor a $60,000.

Solución:

WITH avg_salary_by_dept AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT department, avg_salary
FROM avg_salary_by_dept
WHERE avg_salary > 60000;

Conclusión

Las Expresiones de Tabla Comunes (CTEs) son una herramienta esencial para escribir consultas SQL más legibles y organizadas. Permiten definir tablas temporales dentro de una consulta, lo que facilita la gestión de consultas complejas y la realización de operaciones recursivas. Practicar con CTEs te ayudará a dominar su uso y a mejorar tus habilidades en SQL.

En el próximo tema, exploraremos las Funciones de Ventana, que te permitirán realizar cálculos avanzados sobre conjuntos de filas relacionadas.

Expresiones de Tabla Comunes (CTEs)

Introducción

Conceptos Clave

Sintaxis Básica

Ejemplo Práctico

Ejemplo 1: CTE Simple

Ejemplo 2: CTE Recursiva

Ejercicios Prácticos

Ejercicio 1: Filtrar Empleados por Departamento

Ejercicio 2: Calcular el Salario Promedio por Departamento

Conclusión

Curso de BigQuery

Módulo 1: Introducción a BigQuery

Módulo 2: SQL Básico en BigQuery

Módulo 3: SQL Intermedio en BigQuery

Módulo 4: SQL Avanzado en BigQuery

Módulo 5: Gestión de Datos en BigQuery

Módulo 6: Optimización del Rendimiento de BigQuery

Módulo 7: Seguridad y Cumplimiento en BigQuery

Módulo 8: Integración y Automatización de BigQuery

Módulo 9: Machine Learning en BigQuery (BQML)

Módulo 10: Casos de Uso de BigQuery en el Mundo Real