SQL

Qué significa expresión de tabla común o CTE en SQL Server y para qué sirve

Estrada Web Group
Estrada Web Group
Qué significa expresión de tabla común o CTE en SQL Server y para qué sirve

Resumen: en este tutorial, aprenderás sobre la expresión de tabla común o CTE en SQL Server mediante el uso de la cláusula WITH.

Introducción a CTE en SQL Server

CTE significa expresión de tabla común. Una CTE permite definir un conjunto de resultados con nombre temporal que está disponible temporalmente en el ámbito de ejecución de una declaración como SELECT, INSERT, UPDATE, DELETE, o MERGE.

A continuación, se muestra la sintaxis común de un CTE en SQL Server:

WITH expression_name[(column_name [,...])]
AS
    (CTE_definition)
SQL_statement;

En esta sintaxis:

  • Primero, se especifica el nombre de la expresión (expression_name) al que se puede hacer referencia más adelante en una consulta.
  • A continuación, se especifica una lista de columnas separadas por comas después de expression_name. El número de columnas debe ser el mismo que el número de columnas definido en CTE_definition.
  • Luego, se use la palabra clave AS después del nombre de la expresión o la lista de columnas si se especifica la lista de columnas.
  • Después, se define una instrucción SELECT cuyo conjunto de resultados completa la expresión de la tabla común.
  • Finalmente, se consulta la expresión de tabla común en una consulta (instruccion_SQL) como un SELECT, INSERT, UPDATE, DELETE, o MERGE.

Preferimos usar las expresiones de tabla comunes en lugar de usar subconsultas porque las expresiones de tabla comunes son más legibles. También usamos CTE en las consultas que contienen funciones analíticas

Ejemplos de CTE en SQL Server

Tomemos algunos ejemplos del uso de expresiones de tabla comunes.

A) Ejemplo simple de CTE de SQL Server

Esta consulta utiliza un CTE para devolver los montos de ventas por parte del personal de ventas en 2018 de la base de datos de pruebas:

WITH cte_sales_amounts (staff, sales, year) AS (
    SELECT    
        first_name + ' ' + last_name,
        SUM(quantity * list_price * (1 - discount)),
        YEAR(order_date)
    FROM    
        sales.orders o
    INNER JOIN sales.order_items i ON i.order_id = o.order_id
    INNER JOIN sales.staffs s ON s.staff_id = o.staff_id
    GROUP BY
        first_name + ' ' + last_name,
        year(order_date)
)

SELECT
    staff,
    sales
FROM
    cte_sales_amounts
WHERE
    year = 2018;

Resultado:

staff sales
Genna Serrano 247174.3531
Kali Vargas 135113.1647
Layla Terrell 56531.3358
Marcelene Boyer 520105.6064
Mireya Copeland 230246.9328
Venita Daniel 625358.3947

En este ejemplo:

  • Primero, definimos cte_sales_amounts como el nombre de la expresión de tabla común. el CTE devuelve un resultado que consta de tres columnas staff, year y sales derivadas de la consulta de definición.
  • En segundo lugar, construimos una consulta que devuelve el monto total de ventas por personal de ventas y año consultando los datos de las tablas orders, order_items y staffs.
  • Tercero, nos referimos al CTE en la consulta externa y seleccionamos solo las filas cuyo año sea 2018.

Ten en cuenta que este ejemplo es únicamente para fines de demostración para ayudar a comprender gradualmente cómo funcionan las expresiones de tabla comunes. Hay una forma más óptima de lograr el resultado sin usar CTE.

B) Usar una expresión de tabla común para hacer informes de promedios basados en conteos

Este ejemplo usa el CTE para devolver la cantidad promedio de pedidos de ventas en 2018 para todo el personal de ventas.

WITH cte_sales AS (
    SELECT
        staff_id,
        COUNT(*) order_count  
    FROM
        sales.orders
    WHERE
        YEAR(order_date) = 2018
    GROUP BY
        staff_id

)
SELECT
    AVG(order_count) average_orders_by_staff
FROM
    cte_sales;

Resultado:

average_orders_by_staff
-----------------------
48

(1 row affected)

En este ejemplo:

Primero, usamos cte_sales como el nombre de la expresión de tabla común. Omitimos la lista de columnas del CTE para que se derive de la declaración de definición de CTE. En este ejemplo, incluye las columnas staff_id y order_count.

En segundo lugar, usamos la siguiente consulta para definir el conjunto de resultados que completa la expresión de tabla común cte_sales. La consulta devuelve el número de pedidos en 2018 por parte del personal de ventas.

SELECT    
    staff_id,
    COUNT(*) order_count
FROM    
    sales.orders
WHERE
    YEAR(order_date) = 2018
GROUP BY
    staff_id;

En tercer lugar, nos referimos a cte_sales en la declaración externa y usamos la función AVG() para obtener el pedido de venta promedio de todo el personal.

SELECT
    AVG(order_count) average_orders_by_staff
FROM
    cte_sales;

C) Usar múltiples CTE de SQL Server en un solo ejemplo de consulta

El siguiente ejemplo utiliza dos CTE cte_category_counts y cte_category_sales para devolver el número de productos y ventas para cada categoría de producto. La consulta externa une dos CTE mediante la columna category_id

WITH cte_category_counts (
    category_id,
    category_name,
    product_count
)
AS (
    SELECT
        c.category_id,
        c.category_name,
        COUNT(p.product_id)
    FROM
        production.products p
        INNER JOIN production.categories c
            ON c.category_id = p.category_id
    GROUP BY
        c.category_id,
        c.category_name
),
cte_category_sales(category_id, sales) AS (
    SELECT    
        p.category_id,
        SUM(i.quantity * i.list_price * (1 - i.discount))
    FROM    
        sales.order_items i
        INNER JOIN production.products p
            ON p.product_id = i.product_id
        INNER JOIN sales.orders o
            ON o.order_id = i.order_id
    WHERE order_status = 4 -- completed
    GROUP BY
        p.category_id
)

SELECT
    c.category_id,
    c.category_name,
    c.product_count,
    s.sales
FROM
    cte_category_counts c
    INNER JOIN cte_category_sales s
        ON s.category_id = c.category_id
ORDER BY
    c.category_name;

Resultado:

category_id category_name product_count sales
1 Children Bicycles 59 259986.3712
2 Comfort Bicycles 30 346449.9962
3 Cruisers Bicycles 78 866524.4023
4 Cyclocross Bicycles 10 642584.8253
5 Electric Bikes 24 733493.7451
6 Mountain Bikes 60 2486420.2641
7 Road Bikes 60 1327155.6399

En este tutorial, aprendiste a usar expresiones de tabla comunes o CTE en SQL Server para construir consultas complejas de una manera fácil de entender.

Compartir artículo:

Más artículos geniales

Tipos de datos NCHAR de SQL Server

Tipos de datos NCHAR de SQL Server

Resumen: en este tutorial, aprenderás a usar el tipo de datos NCHAR de SQL Server para almacenar datos de cadenas de caracteres Unicode de longitud fija.

Ver artículo completo
Tipo de datos NVARCHAR de SQL Server

Tipo de datos NVARCHAR de SQL Server

Resumen: en este tutorial, aprenderás a usar el tipo de datos NVARCHAR de SQL Server para almacenar datos de cadenas Unicode de longitud variable.

Ver artículo completo
¿Cómo obtener el valor máximo en SQL Server con la función MAX()?

¿Cómo obtener el valor máximo en SQL Server con la función MAX()?

En este tutorial, aprenderás a usar la función MAX() de SQL Server para encontrar el valor máximo en un grupo de resultados.

Ver artículo completo

Manténgase actualizado

Obtenga excelente contenido en su bandeja de entrada todas las semanas.
Solo contenido excelente, no compartimos su correo electrónico con terceros.
Subir al inicio de la pantalla