No tienes acceso a esta clase

¡Continúa aprendiendo! Únete y comienza a potenciar tu carrera

Aprende Inglés, Programación, AI, Ciberseguridad y más a precio especial.

Antes: $249

Currency
$209
Suscríbete

Termina en:

2 Días
1 Hrs
51 Min
24 Seg

Common table expressions

23/34
Recursos

Aportes 51

Preguntas 4

Ordenar por:

¿Quieres ver más aportes, preguntas y respuestas de la comunidad?

Me tome una tarde para leer y tratar de entender esta clase, les dejo el tutorial que hice, espero les sirva, el feedback es bien recibido

https://platzi.com/tutoriales/1780-postgresql-datos/7204-common-table-expressions/

Dejo el codigo que en el tutorial explico a detalle

-- tabla temporal 1
WITH peliculas_rentadas AS (
	SELECT pelicula_id, COUNT(fecha_renta) AS rentas_acumuladas
	FROM inventarios
	JOIN  rentas
		ON inventarios.inventario_id = rentas.inventario_id
	GROUP BY inventarios.pelicula_id
	ORDER BY rentas_acumuladas DESC
), 

-- tabla temporal 2
peliculas_categoria_horror AS (
	SELECT pelicula_id, nombre
	FROM peliculas_categorias
	JOIN categorias 
		ON peliculas_categorias.categoria_id = categorias.categoria_id
	WHERE 
		categorias.nombre = 'Horror'
)

SELECT 
	peliculas.titulo,
	peliculas.clasificacion,
	peliculas_categoria_horror.nombre AS genero,
	peliculas_rentadas.rentas_acumuladas AS rentas_acumuladas,
	precio_renta * (peliculas_rentadas.rentas_acumuladas) AS monto_rentas_acumulado 
	
FROM peliculas
	JOIN peliculas_categoria_horror
		ON peliculas.pelicula_id = peliculas_categoria_horror.pelicula_id
	JOIN peliculas_rentadas
		ON peliculas.pelicula_id = peliculas_rentadas.pelicula_id

WHERE 
	peliculas.duracion > 100 and peliculas.precio_renta < 1 ;

Falto explicar un poco mejor este tema

No entendí por que daba 5050 al final, pero indagando un poco es la suma recursiva de todos los números hasta el 100
Traducido a Python sería algo así:

suma = 0
for i in range(101):
	suma = suma + i

Dejó el aporte por si alguien tuvo la misma duda.

Bueno este tema es algo más denso, creo que sería ideal usar un par de ejemplos adicionales para comprender mejor el tema, más sin embargo sintetizando lo que comenta Isra en esta clase:

  • Es una herramienta que usa expresiones de tablas para agilizar en postgres cálculos en casos de uso específicos.

  • Ofrecen una mayor aplicabilidad a casos de usos iterantes, casos en los cuales su estructura bajo lenguaje SQL es dispendiosa tanto en su construcción como su ejecución para el servidor.

  • Es necesario ahondar más en este tema ya que su versatilidad es interesante.

[https://www.postgresql.org/docs/12/queries-with.html#QUERIES-WITH-SELECT]

utilizar PostgreSQL CTE (expresiones de tabla comunes) para simplificar consultas complejas.

Introducción a las expresiones de tabla comunes de PostgreSQL o CTE
Una expresión de tabla común es un conjunto de resultados temporal que se puede hacer referencia en otro comando SQL incluyendo SELECT, INSERT, UPDATEo DELETE.

Las expresiones de tabla comunes son temporales en el sentido de que solo existen durante la ejecución de la consulta.

A continuación, se muestra la sintaxis de la creación de un CTE:

WITH cte_name (column_list) AS (
CTE_query_definition
)
statement;
En esta sintaxis:

Primero, especifique el nombre del CTE seguido de una lista de columnas opcional.
En segundo lugar, dentro del cuerpo de la WITHcláusula, especifique una consulta que devuelva un conjunto de resultados. Si no especifica explícitamente la lista de columnas después del nombre de CTE, la lista de selección de CTE_query_definitionse convertirá en la lista de columnas de CTE.
En tercer lugar, utilizar el CTE como una tabla o vista en la statementque puede ser una SELECT, INSERT, UPDATE, o DELETE.
Las expresiones de tabla comunes o CTE se utilizan normalmente para simplificar uniones y subconsultas complejas en PostgreSQL.

Podrian explicarme un poco mas para q usamos un Common table expression??

Algo a considerar es que las CTE permiten simplificar queries bastante complejas pero sobre todo permiten cargar en memoria subsets de registros lo cual aumenta la optimización y rendimiento del query final.

# Para los que no Entendieron Las Common Table Expressions (CTEs) son una característica de SQL que permite definir una consulta nombrada dentro de otra consulta. Esto proporciona una forma de modularizar y reutilizar partes de una consulta SQL, lo que puede hacer que las consultas sean más legibles y mantenibles. Aquí tienes ejemplos de CTEs en niveles básico, intermedio y avanzado: \## Nivel Básico En este ejemplo, utilizaremos una CTE para calcular la suma de los primeros 10 números naturales: ```js WITH numeros AS ( SELECT generate_series(1, 10) AS numero ) SELECT SUM(numero) AS suma_total FROM numeros; ``` En este caso, la CTE llamada numeros genera una serie de números del 1 al 10. Luego, fuera de la CTE, realizamos una consulta que suma todos los números generados. \## Nivel Intermedio En este ejemplo, utilizaremos una CTE para encontrar los empleados que ganan más que el salario promedio en una tabla de empleados: ```js WITH salario_promedio AS ( SELECT AVG(salario) AS salario_promedio FROM empleados ) SELECT nombre, salario FROM empleados WHERE salario > (SELECT salario_promedio FROM salario_promedio); ``` Aquí, la CTE salario\_promedio calcula el salario promedio de todos los empleados. Luego, la consulta principal selecciona los empleados cuyos salarios son mayores que el salario promedio calculado en la CTE. \## Nivel Avanzado En este ejemplo, utilizaremos múltiples CTEs y combinaremos los resultados utilizando una consulta final: ```js WITH cte_ventas AS ( SELECT producto_id, SUM(monto) AS total_ventas FROM ventas GROUP BY producto_id ), cte_productos_mas_vendidos AS ( SELECT producto_id FROM cte_ventas WHERE total_ventas > 1000 ) SELECT p.nombre, v.total_ventas FROM cte_productos_mas_vendidos c JOIN cte_ventas v ON c.producto_id = v.producto_id JOIN productos p ON p.id = v.producto_id; ``` En este ejemplo, la primera CTE (cte\_ventas) calcula el total de ventas para cada producto. Luego, la segunda CTE (cte\_productos\_mas\_vendidos) selecciona los productos que tienen ventas totales superiores a 1000. Finalmente, la consulta principal une las CTEs y selecciona el nombre del producto y el total de ventas para cada producto que cumple con el criterio especificado.

Wow, es super satisfactorio cuando despues del miedo a fallar e intentos logras dar con un query, comparto la forma en la que utilize las common table expressions para hallar las pelis con mayor duracion en su renta:

WITH pelis_mas_rentadas as(
SELECT MAX(duracion_renta) as duracion_renta, pelicula_id as pelicula_identificador
from peliculas
group by pelicula_identificador
),
pelis_generos_populares as(
	SELECT categorias.categoria_id, categorias.nombre as categoria_nombre
	from categorias
 )
 
 SELECT * 
 FROM pelis_generos_populares JOIN peliculas_categorias
 ON pelis_generos_populares.categoria_id = peliculas_categorias.categoria_id
 JOIN pelis_mas_rentadas
 ON pelis_mas_rentadas.pelicula_identificador = peliculas_categorias.pelicula_id
 
 order by pelis_mas_rentadas.duracion_renta desc;


las common table expressions son muy parecidas a lo que se conoce como Tally Tables en SQL Server, son muy utiles también cuando quieres por ejemplo ir sumando un dia consecutivo a una fecha inicial.

Miren, tampoco entendí muy bien xD, pero estuve leyendo comentarios, foros y vi unos videos y puedo simplificar a que:

Common Table Expressions (CTE) / Expresión de Tabla Común:

  • Son creadas en tiempo de ejecución.

  • Es necesario asignarle un nombre (AS).

  • Tienen una consulta interna.

  • Hay una consulta externa que hace uso de la consulta interna.

SINTAXIS

WITH  <nombre _CTE>
AS
	(
	<consulta_interna
	)
<consulta_externa>

Es compleja estas expresiones creo que falto un ejemplo mas aplicado al proyecto.

Básicamente lo que hace trabajar con una tabla recursiva sin necesidad de utilizar una que puede ocupar memoria o mas trabajo en el disco como explico el profesor es por eso que cuando hace la unión tiene una tabla nueva lo pudo haber hecho con las que tenemos pero lo que quiso mostrar es ver que se puede trabajar con valores iterativos. confunde un poco pero denle su tiempo y ordenen el código y entiendan como funciona.

Me tome una tarde para leer y tratar de entender esta clase, les dejo el tutorial que hice, espero les sirva, el feedback es bien recibido

https://platzi.com/tutoriales/1780-postgresql-datos/7204-common-table-expressions/

Common table expressions, forma parte del estándard SQL-99, y su implementación por parte de los motores de base de datos que soportan este estándar es opcional:

En PostgreSQL soportado a partir de la versión 8.4

Veamos:
Primero, este mecanismo permite encapsular consultas complejas cómo CTEs haciendo que la consulta principal quede simple y legible, con esto garantizamos reusabilidad por parte del motor de BD donde se haga referenceia a la CTE,
y esto es porque los motores de base de datos implementan mecanismos para optimizar el uso de estos recursos, obteniendo una única vez los datos del subconjunto definido en la CTE

muy intenresante

Recordemos:
Que PostgreSQL, es un sistema de gestión de bases de datos relacional orientado a objetos

A poner en práctica, son muy potentes.

Hola,

Comparto mi intento de CTE, en la clase le están dando un uso un tanto complejo, a mi parecer, profundizaré pero también es usado como una tabla temporal que de ser la lógica, performaría mucho más rapido.

WITH tabla_nombres_empleados
AS(
	SELECT empleado_id, CONCAT(nombre, ' ', apellido) AS Nombre
	FROM empleados
),

direccion_ciudad_tienda
AS(
	SELECT t.direccion_id, d.direccion_ciudad
	FROM tiendas AS t
	JOIN (
			SELECT d.direccion_id, CONCAT(d.direccion, ' - ', c.ciudad) AS direccion_ciudad
			FROM direcciones AS d
			JOIN ciudades AS c
				ON d.ciudad_id = c.ciudad_id
			WHERE d.direccion_id IN (SELECT direccion_id FROM tiendas)
	) AS d
		ON t.direccion_id = d.direccion_id
)

SELECT t.tienda_id, n.nombre AS jefe, d.direccion_ciudad AS direccion
FROM tiendas AS t
	JOIN tabla_nombres_empleados AS n
		ON t.jefe_tienda_id = n.empleado_id
	JOIN direccion_ciudad_tienda AS d
		ON t.direccion_id = d.direccion_id

Quedaria mejor explicando con un ejemplo, de un caso en los que son utiles

WITH RECURSIVE tabla_recursiva(n) AS (
	VALUES(1)
	UNION ALL
	SELECT n+1 FROM tabla_recursiva WHERE n < 10
) SELECT SUM(n) FROM tabla_recursiva;

Dejo el código de la clase

WITH RECURSIVE tabla_recursiva(n) AS (
	VALUES(1)
	UNION ALL
	SELECT n+1 from tabla_recursiva WHERE n < 100
) SELECT SUM(n) FROM tabla_recursiva;
Como ejemplo de como funciona la recursividad en PostgreSQL utilizando las `Common Table Expression` esta bien, sin embargo a la clase le hizo falta un ejemplo **real** del uso de ésta funcionalidad, el curso nos dio una base de datos a la que le podemos sacar mucho provecho y hubiera estado mucho mejor uno o más ejemplo de las CTE utilizando queries sobre la BBDD `platzimovies`.
Son **tablas temporales** dentro de un Query. A estas tablas se les puede agregar un alias y **usarse varias veces** dentro del mismo Query. # Ejemplo practico: ```js WITH VentasDiarias AS ( SELECT fecha, SUM(monto) AS total_ventas FROM ventas WHERE fecha BETWEEN '2024-04-01' AND '2024-04-30' GROUP BY fecha ) SELECT fecha, total_ventas, AVG(total_ventas) OVER () AS promedio_ventas_diarias FROM VentasDiarias ORDER BY fecha; ``` ### Explicación: Se crea una tabla temporal que suma las ventas diarias para luego sacar el promedio de ventas por mes.
-- Creamos una expresión de tabla común (CTE) para calcular la cantidad de películas por género
WITH CountMoviesByGenre AS (
    SELECT
        g.nombre AS genero,
        COUNT(*) AS cantidad_peliculas
    FROM
        generos g
    JOIN
        peliculas p ON g.id = p.genero_id
    GROUP BY
        g.nombre
)

-- Utilizamos la CTE para seleccionar las películas junto con la cantidad de películas por género
SELECT
    p.id AS id_pelicula,
    p.titulo,
    g.genero,
    cmg.cantidad_peliculas
FROM
    peliculas p
JOIN
    generos g ON p.genero_id = g.id
JOIN
    CountMoviesByGenre cmg ON g.nombre = cmg.genero
ORDER BY
    cmg.cantidad_peliculas DESC;

Las Common Table Expressions (CTEs) en PostgreSQL son consultas temporales que te permiten definir y nombrar subconsultas dentro de una consulta principal. Se crean usando la cláusula `WITH`. En un caso real, podrías usar una CTE para simplificar consultas complejas dividiéndolas en partes más manejables. Por ejemplo, imagina que tienes una base de datos con información de empleados y departamentos. Podrías utilizar una CTE para calcular el promedio de salarios por departamento de manera más legible y organizada: ```sql WITH SalariosPorDepartamento AS ( SELECT departamento\_id, AVG(salario) AS salario\_promedio FROM empleados GROUP BY departamento\_id ) SELECT d.nombre AS departamento, s.salario\_promedio FROM SalariosPorDepartamento s JOIN departamentos d ON s.departamento\_id = d.id; ``` En este ejemplo, la CTE `SalariosPorDepartamento` calcula el salario promedio por departamento, y luego la consulta principal utiliza esta CTE para mostrar el nombre del departamento y su salario promedio. Esto hace que la consulta sea más modular y fácil de entender.

¿Por qué desarrollan ejemplos poco prácticos y no desarrollan ejemplos prácticos como el UPDATE?

No esta tan clara esta clase desde mi punto de vista.

Una forma de entender esta las common table expressions es con conocimiento de otros cursos sobre bases de datos:

  • Lo unico nuevo es la sentencia WITH RECURSIVE Que lo que hace es indicar que se va a crear una tabla recursiva. Luego, se realiza un JOIN cuando se coloca UNION ALL

  • Se esta realizando un loop o ciclo for puesto que se refiere una tabla que es ella misma. Esto se hace para que el programa recorra la tabla

  • Al colocarle n como parametro a la tabla, significa que el valor esta por ser asignado. Luego le asignamos el valor de 1

  • Despues con la sentencia SELECT creamos un ciclo infinito de n+1, es decir, cada vez que el programa recorra la tabla sumamos 1 al parametro

  • Finalmente se coloca n < 100 que indica cuando termina este ciclo for, que sera las primeras 100 recorridos.

Esta tabla lo que permite es realizar una tabla que solo va a existir en memoria, una especie de tabla calculada.

Gracias a Rusbel Bermudez Rivera y su ejemplo. Aquí su ejercicio resuelto usando common table expressions. Tomó 92ms correrlo vs los 105 que me daba usando solo JOINS:

-- Peliculas más retadas
WITH peliculas_rentadas AS(SELECT
		i.pelicula_id,
		COUNT(r.renta_id) AS numero_rentas
		FROM rentas r
		JOIN inventarios i
			ON r.inventario_id = i.inventario_id
		GROUP BY i.pelicula_id
		ORDER BY numero_rentas DESC
),

-- Peliculas del genero horror
	peliculas_horror AS(SELECT
		pc.pelicula_id,
		c.nombre AS genero
		FROM categorias c
		JOIN peliculas_categorias pc
			ON pc.categoria_id = c.categoria_id
		WHERE c.nombre = 'Horror')
		
-- Peliculas de Horror, de mas de 100 min y cuyo precio de renta supera el dolar más rentadas.		
SELECT
	p.titulo,
	pr.numero_rentas,
	pr.numero_rentas * p.precio_renta AS valor_acumulado
FROM peliculas p
JOIN peliculas_rentadas pr ON pr.pelicula_id = p.pelicula_id
JOIN peliculas_horror ph ON ph.pelicula_id = p.pelicula_id
WHERE p.duracion > 100 AND p.precio_renta >1;

23. Common table expressions

WITH RECURSIVE tabla_recursiva(n) AS (
	VALUES(1)
	UNION ALL
	SELECT n+1 FROM tabla_recursiva WHERE n < 100
)SELECT SUM(n) FROM tabla_recursiva;

Si alguno se pregunto porque al hacer la suma de los valores le da “5050” y no 100, es porque en realidad se hace la suma de “1”+“2”+“3”… hasta que los numeros sumados sean 99.
Es como sumar la columnas de “id” (en caso de que este ordenado desde 1 hasta 99).

Entiendo la recursividad y sé que puede ser buena en muchos casos. Pero si necesitan generar lo mismo en Postgresql sin usar recursividad existe un tipo de funciones llamadas SRF (set returning functions), que como su nombre lo indica retorna un conjunto. Para el ejemplo de profesor se prodría usar:

SELECT SUM(a) from generate_series(1,100) a;

explicacion de la clase comparando con python

el ejemplo de la clase trata de realizar una consulta de una consulta, un query anidado.

  1. se crear una tabla temporal de un query determinado y luego poder hacer un query sobre el resultado final. En el ejemplo creamos una tabla temporal con una columna llamada “n” la cual contiene registros del 1 al 100, se usa RECURSIVE que es el equivalente a for en python.
  2. se hace una consulta sobre la tabla temporal creada. en el ejemplo se realiza una sumaatoria de la columna n.

Muchos se confunden por que tratan de entender el bucle RECURSIVE que se realizo(el cual solo sirvio para crear una tabla virtual con registros del 1 al 100), cuando realmente lo importante de la clase, es decir vamos a realizar una consulta de una tabla que resulto de otra consulta . si quieren saber de donde salio el 5050 es lo mismo que hacer esto en python:

acumulacion = 0
for i in range(1,101):
	acumulacion += i
print(acumulacion)

Este recurso explica de manera gráfica la recursividad en CTEs:
https://medium.com/swlh/recursion-in-sql-explained-graphically-679f6a0f143b

primera clase de este docente que no entiendo, espero amplíen información

Creo que es mejor introducir CTEs con un par de queries distintas donde la segunda utiliza lo obtenido en la primera antes que hacerlo con una recursión que incluso no es tan utilizado como lo primero.
También recomiendo indagar sobre este tema porque es extremadamente útil y hay consultas que son imposibles de hacer sin esto.

Seria bueno hacerlo con los datos que tenemos de ejemplo para ver la bondad de esta utilidad que se ve poderosa, aunque el profe menciono en que se pude usar seria bueno ver el ejemplo

Qué enredo 😕

Pregunta, a traves de una funcion, si utilizamos el metodo python (creando la extension de plpython) se podrá hacer lo mismo? Lo intentare, y les cuento; quizá, solo quizá, sea mas simple de entender (quien sabe)

🤔

Aquí un recurso de la página de postgres:

https://www.postgresqltutorial.com/postgresql-cte/

interesante

Comparto el query final

with recursive tabla_recursiva(n) as(
	values(1)
	union ALL
	select n+1 from tabla_recursiva where n < 100
) select sum(n) from tabla_recursiva
;```

Me habia encontrado con querys de este tipo, y pensé que era una propiedad mas de postgresql, aunque en ese entonces entedí la lógica que tenian, ahora sé su definición.

Recomiendo leer la documentación que compartió @Camilo Duque,para tener las Comon table expresions mas claras

WITH RECURSIVE: Proceso iterativo a partir de common table expressions.

Hola, no me quedó muy claro este tema de Common Table Expression. ¿Pudieran darme otro ejemplo para entenderlas mejor?

intentare explicar lo que entendí:
las common table expressions tienen múltiples usos, el primero y que te puede ayudar a entenderlas un poco aunque no es practico es usarlas como filtro:

WITH top_peliculas AS (
    SELECT titulo
    FROM peliculas
    WHERE precio_renta < 3
)
SELECT SUM(duracion) AS duracion_top_peliculas
FROM peliculas
WHERE titulo IN (SELECT titulo FROM top_peliculas)
GROUP BY titulo;

adicionalmente puedes agregar la funcionalidad RECURSIVE , que permite iterar por decirlo de una manera, un buen ejemplo es el que compartió Sandra Milena Rairán Pinilla

WITH RECURSIVE precio_recursivo(n) AS (
   VALUES (0)
   UNION ALL
   SELECT precio_renta FROM peliculas WHERE precio_renta < 0.99
)
SELECT sum(n) FROM precio_recursivo;

conclusión: la funcionalidades no se ven con ejemplos tan sencillos pero sin duda esta funcionalidad tiene mucho poder, por ejemplo filtros muy complejos y reutilizables. para mas info en: https://www.postgresql.org/docs/12/queries-with.html

Creo debimos primero ver que son las common talbe expressions y ya después de entender eso agregarle la recursividad, tema muy interesante y super útil, ojala pudieramos verlo más profundo.
https://www.postgresqltutorial.com/postgresql-recursive-query/
este tutorial me ayudó bastante