PostgreSQL – CTE

En PostgreSQL, la CTE (expresión de tabla común) se usa como un conjunto de resultados temporal al que el usuario puede hacer referencia dentro de otra instrucción SQL como SELECT , INSERT , UPDATE o DELETE . Los CTE son temporales en el sentido de que solo existen durante la ejecución de la consulta. Los CTE se utilizan normalmente para simplificar uniones y subconsultas complejas en PostgreSQL.

Syntax:
WITH cte_name (column_list) AS (
    CTE_query_definition 
)
statement;

Analicemos la sintaxis anterior.

• El primer paso es establecer el nombre del CTE seguido de una lista de columnas opcional.
• Luego especificamos una consulta que devuelve el conjunto de resultados dentro del cuerpo de la cláusula WITH. si no se especifica explícitamente, la lista de selección de CTE_query_definition se convertirá en la lista de columnas de CTE.
• Finalmente, use el CTE como una tabla o vista en la declaración que puede ser SELECCIONAR, INSERTAR, ACTUALIZAR o ELIMINAR.

Por ejemplo, usaremos la base de datos de muestra (es decir, dvdrental ).

Ejemplo 1:
En esto, definiremos una expresión de tabla común nombrada cte_film usando la cláusula WITH con la tabla de películas y alquiler de la siguiente manera para determinar la duración de las películas.

WITH cte_film AS (
    SELECT 
        film_id, 
        title,
        (CASE 
            WHEN length < 30 THEN 'Short'
            WHEN length < 90 THEN 'Medium'
            ELSE 'Long'
        END) length    
    FROM
        film
)
SELECT
    film_id,
    title,
    length
FROM 
    cte_film
WHERE
    length = 'Long'
ORDER BY 
    title;

Producción:

Ejemplo 2:
La siguiente declaración ilustra el uso del CTE con la RANK() función de ventana en la film tabla para clasificar las películas según su duración de la siguiente manera:

WITH cte_film AS  (
    SELECT film_id,
        title,
        rating,
        length,
        RANK() OVER (
            PARTITION BY rating
            ORDER BY length DESC) 
        length_rank
    FROM 
        film
)
SELECT *
FROM cte_film
WHERE length_rank = 1;

Producción: