¿Qué es una expresión común de tabla (CTE) en SQL?

La expresión común de tabla (CTE) es una potente construcción de SQL que ayuda a simplificar una consulta. Las CTEs funcionan como tablas virtuales (con registros y columnas), creadas durante la ejecución de una consulta, utilizadas por la consulta y eliminadas tras la ejecución de la consulta. Las CTE suelen actuar como puente para transformar los datos de las tablas de origen al formato esperado por la consulta.

Una expresión de tabla común, o CTE, es un conjunto de resultados temporal con nombre creado a partir de una sentencia SELECT sencilla que puede utilizarse en una sentencia SELECT posterior. Cada CTE de SQL es como una consulta con nombre, cuyo resultado se almacena en una tabla virtual (una CTE) a la que se hará referencia posteriormente en la consulta principal.

La mejor manera de aprender las expresiones comunes de tabla es mediante la práctica. Recomiendo el curso interactivo de LearnSQL.esConsultas recursivas y expresiones de tabla comunes de . Contiene más de 100 ejercicios que enseñan las CTEs empezando por lo básico y progresando hasta temas avanzados como las expresiones comunes de tabla recursivas.

Las CTEs ayudan a simplificar las consultas

Comencemos con la sintaxis de una expresión común de tabla.

WITH my_cte AS (
  SELECT a,b,c
  FROM T1
)
SELECT a,c
FROM my_cte
WHERE ....

El nombre de esta CTE es my_ctey la consulta CTE es SELECT a,b,c FROM T1. La CTE comienza con la palabra clave WITH, tras la cual se especifica el nombre de la CTE y, a continuación, el contenido de la consulta entre paréntesis. La consulta principal viene después del paréntesis de cierre y hace referencia al CTE. Aquí, la consulta principal (también conocida como consulta externa) es SELECT a,c FROM my_cte WHERE ….

Hay muchos ejemplos básicos en este excelente artículo introductorio sobre las CTE. Otros artículos introductorios incluyen "Improving Query Readability with Common Table Expressions" y "When Should I Use a Common Table Expression (CTE)?" que explican las expresiones comunes de tabla.

Aprendizaje de las Expresiones Comunes de Tabla SQL mediante Ejemplos

En esta sección, presentamos algunos ejemplos de consultas SQL que utilizan expresiones comunes de tabla. Todos los ejemplos se basan en una base de datos de una cadena de tiendas de teléfonos móviles. La tabla salesque se muestra a continuación, tiene un registro por producto vendido:

En el primer ejemplo, obtenemos un informe con los mismos registros de la tabla sales pero añadimos una columna adicional con el precio del artículo más caro vendido en la misma sucursal ese día. Para obtener el precio del artículo más caro, utilizamos una expresión de tabla común como la siguiente:

WITH highest AS (
  SELECT
    branch,
    date,
    MAX(unit_price) AS highest_price
  FROM sales
  GROUP BY branch, date
)
SELECT
  sales.*,
  h.highest_price
FROM sales
JOIN highest h
  ON sales.branch = h.branch
    AND sales.date = h.date

Esta consulta define un CTE SQL llamado highest cuyo resultado crea una tabla virtual. La tabla virtual tiene las columnas sucursal, fecha y highest_price, que contienen el nombre de la sucursal, la fecha y el precio unitario más alto vendido ese día en esa sucursal, respectivamente.

A continuación, se ejecuta la consulta externa, que utiliza la highest tabla virtual como si fuera una tabla normal. Por último, unimos el resultado de la CTE highest con la tabla sales tabla.

A continuación se muestra el resultado de la consulta completa:

En el siguiente ejemplo, generamos un informe con los mayores ingresos diarios por sucursal.

WITH daily_revenue AS (
  SELECT
    branch,
    date,
    SUM(unit_price * quantity) AS daily_revenue
  FROM   sales
  WHERE EXTRACT(YEAR FROM date) = 2021
  GROUP BY 1,2
)
SELECT
  branch,
  MAX(daily_revenue) max_daily_revenue
FROM daily_revenue
GROUP BY 1
ORDER BY 2 DESC

En esta consulta, un CTE llamado daily_revenue tiene las columnas sucursal, fecha y daily_revenue para cada fecha de 2021. Luego, en la consulta externa, obtenemos la mayor cantidad de ingresos para cada sucursal en 2021. El informe se ordena por max_daily_revenue en orden descendente.

A continuación se muestran los resultados de esta consulta.

Uso de CTEs en Consultas SQL avanzado

Puede definir dos o más CTEs y utilizarlos en la consulta principal. En el siguiente ejemplo, le mostramos cómo dividir y organizar una consulta larga utilizando CTEs SQL. Al nombrar las diferentes partes de la consulta, las CTEs hacen que la consulta sea fácil de leer.

Supongamos que queremos un informe con los ingresos mensuales totales de Londres en 2021, pero también queremos los ingresos de cada sucursal de Londres en el mismo informe. En este caso, creamos dos CTE y los unimos en la consulta principal.

WITH london1_monthly_revenue AS (
  SELECT
    EXTRACT(MONTH FROM date) as month,
    SUM(unit_price * quantity) AS revenue
  FROM sales
  WHERE EXTRACT(YEAR FROM date) = 2021
    AND branch = 'London-1'
  GROUP BY 1
),
london2_monthly_revenue AS (
  SELECT
    EXTRACT(MONTH FROM date) as month,
    SUM(unit_price * quantity) AS revenue
  FROM sales
  WHERE EXTRACT(YEAR FROM date) = 2021
    AND branch = 'London-2'
  GROUP BY 1
)
SELECT
  l1.month,
  l1.revenue + l2.revenue AS london_revenue,
  l1.revenue AS london1_revenue,
  l2.revenue AS london2_revenue
FROM london1_monthly_revenue l1, london2_monthly_revenue l2
WHERE l1.month = l2.month

En la consulta anterior, definimos dos CTEs, london1_monthly_revenue y london2_monthly_revenuepara obtener los ingresos mensuales en 2021 de cada sucursal de Londres. Por último, unimos ambos CTEs utilizando la columna del mes y calculamos los ingresos totales de Londres sumando los ingresos de las dos sucursales.

A continuación se muestra el resultado de la consulta:

En el siguiente ejemplo, obtenemos un informe para informar a cada sucursal de la fecha en la que se vendió el mayor ticket (es decir, el importe de la combinación artículo-cantidad) y el importe de este ticket. Para ello, necesitamos crear un CTE que clasifique los tickets (la columna position es la clasificación) para cada sucursal por el importe del ticket.

WITH tickets AS (
  SELECT distinct
    branch,
    date,
    unit_price * quantity AS ticket_amount,
    ROW_NUMBER() OVER (
      PARTITION BY branch
      ORDER by unit_price * quantity DESC
    ) AS position
  FROM sales
  ORDER BY 3 DESC
)
SELECT
  branch,
  date,
  ticket_amount
FROM tickets
WHERE position =1

En la consulta anterior, creamos un CTE con las columnas branch, date, ticket_amount, y position. Luego, en la consulta externa, filtramos sólo los registros con position = 1 para obtener lo que queremos, el ticket más grande por sucursal.

El resultado de la consulta se muestra a continuación:

CTEs anidados en consultas SQL

El siguiente ejemplo muestra una CTE anidada. La idea es generar un informe con todos los artículos de precio superior a 90 dólares y la cantidad de estos artículos vendidos por la sucursal Londres-2.

WITH over_90_items AS (
  SELECT DISTINCT
    item,
    unit_price
  FROM sales
  WHERE unit_price >=90
),
london2_over_90 AS (
  SELECT
    o90.item,
    o90.unit_price,
    coalesce(SUM(s.quantity), 0) as total_sold
  FROM over_90_items o90
  LEFT JOIN sales s
  ON o90.item = s.item AND s.branch = 'London-2'
  GROUP BY o90.item, o90.unit_price
)
SELECT item, unit_price, total_sold
FROM   london2_over_90;

El primer CTE es over_90_itemsque selecciona todos los artículos cuyo precio es igual o superior a 90 $. El segundo CTE es london2_over_90que selecciona la cantidad vendida por Londres-2 de cada artículo incluido en over_90_items. Esta consulta tiene un CTE anidado: observe que FROM en el segundo CTE hace referencia al primero. Utilizamos LEFT JOIN sales porque es posible que Londres-2 no haya vendido todos los artículos de over_90_items.

El resultado de la consulta es :

Antes de pasar a la siguiente sección, tengo algunos artículos que sugerir sobre expresiones comunes de tabla. Tanto "SQL CTEs Explained with Examples" como "Where Can I Find Good SQL CTE Exercises?" tienen muchos ejemplos y ejercicios.

Consultas recursivas y expresiones de tabla comunes y Expresiones Comunes de Tabla

En las bases de datos relacionales, es común tener tablas que representan jerarquías de datos como empleado-gerente, parte-subparte o padre-hijo. Para recorrer estas jerarquías en cualquier dirección (de arriba a abajo o de abajo a arriba), las bases de datos utilizan una construcción llamada CTE recursiva.

RECURSIVE es una palabra reservada para definir una CTE para recorrer una estructura de datos recursiva. La forma de la consulta recursiva es la siguiente:

WITH RECURSIVE  cte_name AS (
     CTE_query_definition  -- non recursive query term
UNION ALL
     CTE_query_definition  -- recursive query term
)
SELECT * FROM cte_name;

Las consultas recursivas están fuera del alcance de este artículo introductorio, pero tengo otros tres para sugerir a aquellos que quieran más sobre el tema: "Cómo organizar consultas SQL con CTEs", "Hazlo en SQL: Recursive SQL Tree Traversal" y "Get to Know the Power of SQL Consultas recursivas y expresiones de tabla comunes". En ellos se explican en detalle las consultas recursivas con multitud de ejemplos.

Las CTE de SQL son un potente recurso

Las expresiones comunes de tabla son un poderoso recurso del lenguaje SQL. Nos permiten crear consultas más legibles y gestionar las diferencias de formato entre los datos de la tabla y los datos del informe. En este artículo, hemos cubierto qué es una CTE y cómo utilizarlas en diferentes tipos de consultas. También hemos mencionado que las CTE pueden utilizarse en consultas recursivas.

Recomiendo el curso Consultas recursivas y expresiones de tabla comunes en LearnSQL.esdonde aprenderás de forma interactiva a trabajar con expresiones comunes de tabla en SQL. También aprenderás a procesar estructuras de datos recursivas como grafos y árboles en SQL utilizando CTEs recursivas.

Además, echa un vistazo a la pista SQL avanzadoen el que irás más allá de los conceptos básicos para convertirte en un maestro de SQL. Si necesitas prepararte para una entrevista SQL, hay un interesante artículo "Top 5 SQL CTE Interview Questions" que trata las expresiones de tabla más comunes desde otra perspectiva.

Si has aprendido a utilizar CTEs, entonces estás un paso más adelante en el camino hacia un desarrollador SQL. ¡Sigue creciendo!