No figura en recursos el script de la base de datos
Caro:
Recuerda revisar los recursos para que puedas ejecutar esto en tu consola.
Los recursos:
WITHAverageSalariesAS(SELECT d.department_name,AVG(e.salary)ASAvgSalaryFROM employees e
JOIN departments d ON e.department_id= d.department_idGROUPBY d.department_name),HighSalaryDepartmentsAS(SELECT department_name,AvgSalaryFROMAverageSalariesWHEREAvgSalary>45000)SELECT e.first_name, e.last_name, e.salary, d.department_nameFROM employees e
JOIN departments d ON e.department_id= d.department_idJOINHighSalaryDepartments hsd ON d.department_name= hsd.department_nameWHERE e.salary> hsd.AvgSalary;
Ventajas de Usar CTE
Legibilidad: Hace que el código sea más fácil de leer y entender.
Modularidad: Permite dividir una consulta compleja en partes más manejables.
Reutilización: Se pueden definir una vez y usar múltiples veces dentro de la misma consulta.
Las CTE son una herramienta poderosa para escribir consultas SQL más claras y eficientes.
Gran ejemplo, gracias compañero 👏
Aqui se entiende mucho mejor, gracias.
Hace un tiempo le comente a Fredy en fb sobre la falta de las CTE en los curso de BD's ahora veo que si leen lo que les comentamos jajaja
Les recomiendo no quedarse con lo de esta clase, las CTE puede ser el cambio entre dia y noche en el tiempo de procesamiento de un query de alto nivel, sobre todo si empezamos a limitar con una subquery todas las demás que tengamos dentro del CTE.
Esto de las CTE me recuerda a cuando en programación uno crea funciones más pequeñas sobre pasos específicos de un algoritmo para luego invocar a estas en una función "main" o "principal" que ejecuta todos los pasos, cosa que mejora mucho la legibilidad y la capacidad de debuggear el código.
Pensarlo con esa analogía me sirvió mucho y más porque vengo de codear en lenguajes como Java o Python donde se usa mucho eso. Espero que a alguien más le funcione pensarlo así :)
Que mal fue evolucionando este curso, voy hasta el final porque no me gusta dejar las cosas a medias, pero voy con más dudas que respuestas...
Me pasa lo mismo, yo continuo con el curso porque conocer nuevos conceptos y herramientas, pero es conocimiento muy superficial, obtuve mejores explicaciones de chatGPT
Dónde están los recursos?
Debería estar en la zona de abaja de la pantalla. Como la pesataña de recursos está vacía, no aparece, pero generalmente está ahí.
Caro 👩💻 identifique que son los Departament:
WITH AverageSalaries AS (
SELECT d.DepartmentName AS Departamento, AVG(e.Salary) AS SueldoPromedio
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
)
La mala practica al renombrar las tablas es debido a que estamos utilizando una sola letra lo cual podría ser confuso porque no nos da información de donde pertenecería esa tabla.
el renombrar sirve cuando haces consultas cortas, pero si son muy complicadas se puede renombrar pero de tal forma que se vea de que tabla proviene
¿Dónde están los parámetros para crear la base de datos? No hay los recursos para la base de datos.
Ya toco inventarse uno mismo...jaja
Mi CTE:
WITH sumary_salaries AS (
SELECT d.departament_name, SUM(e.salary) AS sumary_salary
FROM EMPLOYEE e
JOIN DEPARTAMENT d ON e.departament_id = d.departament_id
GROUP BY d.departament_name
),
average_salaries AS (
SELECT d.departament_name, AVG(e.salary) AS AVG_salary
FROM EMPLOYEE e
JOIN DEPARTAMENT d ON e.departament_id = d.departament_id
GROUP BY d.departament_name
)
SELECT s.departament_name, s.sumary_salary
FROM sumary_salaries s
JOIN average_salaries a ON s.departament_name = a.departament_name
WHERE s.sumary_salary > a.AVG_salary / 10;
Resultado:
me gustó aprender sobre las CTE ya que tengo experiencia en SQL pero este concepto para alguien que no sabe nada de sql se le hara dificil de entender
🙋 yo soy nuevo y si esta medio confuso para entender, pero esta bonito como en el curso que estoy tomando de Django al ver la inserción de datos en sqLite, ya todo se te simplifica o lo entiendes mejor 🤗
Esto es super explicativo, de verdad se nota el potencial de CTE.
¿Qué son las CTE en SQL?
CTE son las siglas de Common Table Expression (Expresión de Tabla Común) en español. Son como tablas temporales que se definen dentro de una consulta SQL y solo existen durante la ejecución de esa consulta.
Imagina una CTE como una variable que almacena un resultado de consulta intermedio. Este resultado puede ser utilizado en otras partes de la misma consulta, lo que permite escribir consultas más complejas y legibles.
¿Para qué sirven las CTE?
Simplificar consultas complejas: Dividiendo una consulta larga y complicada en partes más pequeñas y manejables.
Reutilizar resultados intermedios: Evitando tener que repetir subconsultas varias veces.
Mejorar la legibilidad del código: Al darle un nombre a un conjunto de resultados, el código se vuelve más fácil de entender y mantener.
Implementar recursividad: Las CTE se pueden utilizar para resolver problemas recursivos, como calcular jerarquías de datos.
Estructura básica de una CTE
SQLWITH nombre_cte AS (
-- Consulta que define la CTE
)
SELECT * FROM nombre_cte;
Usa el código con precaución.
WITH: Palabra clave para iniciar la definición de la CTE.
nombre_cte: Un nombre que se le asigna a la CTE para poder referenciarla posteriormente.
Consulta que define la CTE: La consulta que produce el resultado que se almacenará en la CTE.
Ejemplo práctico
Supongamos que tenemos una tabla de empleados con una columna manager_id que indica el ID del jefe de cada empleado. Queremos encontrar a todos los empleados que reportan directamente o indirectamente a un determinado jefe.
SQLWITH Empleados_Jerarquia AS (
SELECT employee_id, manager_id
FROM employees
WHERE manager_id = 123 -- ID del jefe al que queremos buscar reportes UNION ALL SELECT e.employee_id, e.manager_id
FROM employees e
INNER JOIN Empleados_Jerarquia eh ON e.manager_id = eh.employee_id
)
SELECT * FROM Empleados_Jerarquia;
Usa el código con precaución.
En este ejemplo:
La CTE Empleados_Jerarquia almacena inicialmente a todos los empleados que reportan directamente al jefe con ID 123.
Luego, se utiliza una unión recursiva (UNION ALL) para agregar a la CTE a todos los empleados que reportan a los empleados ya incluidos en la CTE.
Finalmente, se seleccionan todos los registros de la CTE para obtener la lista completa de empleados.
Ventajas de usar CTE
Legibilidad: Las CTE hacen que el código sea más fácil de leer y entender.
Reutilización: Los resultados de una CTE se pueden reutilizar en múltiples partes de la consulta.
Flexibilidad: Las CTE se pueden utilizar en una amplia variedad de situaciones.
Rendimiento: En algunos casos, las CTE pueden mejorar el rendimiento de las consultas.
En resumen, las CTE son una herramienta poderosa en SQL que te permite escribir consultas más complejas y eficientes. Al entender cómo funcionan y cuándo utilizarlas, puedes mejorar significativamente tus habilidades de escritura de SQL.
¿Te gustaría ver más ejemplos de cómo utilizar CTE en diferentes escenarios?
Temas relacionados que podrías encontrar interesantes:
CTE recursivas: Para resolver problemas jerárquicos.
CTE en diferentes SGBD: Pequeñas diferencias en la sintaxis entre MySQL, PostgreSQL, SQL Server, etc.
CTE y rendimiento: Consejos para optimizar el uso de CTE. FUENTE CHATGPT
Sería útil que los recursos incluyeran detalles sobre las columnas necesarias para crear la tabla. Siento que el curso fue elaborado con prisa al inicio; los recursos eran completos, pero ahora parece que faltan partes importantes.
Tengo una pregunta, si tengo una consulta que realice con varios inner joins y left joins a varia tablas, seria mejor traformala a un CTE y obteniendo unicamente los campos necesarios de esa tabla, son tablas de bussines, entonces eso mejorara el rendimiento si solo llamo los campos necesarios con el WHIT?
Las CTE mejoran la legibilidad del código y el mantenimiento al componerse de subconsultas y permitir simplificar consultas complejas. El rendimiento puede mejorarse al filtrar solo los campos necesarios, pero esto depende de la implementación específica y el sistema de gestión de bases de datos utilizado.
Sí, usar CTEs puede mejorar significativamente el rendimiento y la legibilidad en consultas complejas con múltiples JOINs. Te muestro un ejemplo:
### Antes (Consulta con múltiples JOINs)
SELECT   o.order\_id,  c.customer\_name,  p.product\_name,  i.quantity,  s.status\_name,  d.delivery\_date,  pm.payment\_methodFROM orders o
INNERJOIN customers c ON o.customer\_id = c.customer\_id
INNERJOINorder\_items i ON o.order\_id = i.order\_id
INNERJOIN products p ON i.product\_id = p.product\_id
LEFTJOINorder\_status s ON o.status\_id = s.status\_id
LEFTJOIN deliveries d ON o.order\_id = d.order\_id
LEFTJOIN payments pm ON o.payment\_id = pm.payment\_id
WHERE o.order\_date >='2024-01-01';
### Después (Usando CTEs)
WITH orden\_base AS(  \-- Solo traemos los campos necesarios de orders y customers  SELECT   o.order\_id,  o.customer\_id,  c.customer\_name  FROM orders o  INNER JOIN customers c ON o.customer\_id = c.customer\_id  WHERE o.order\_date >= '2024-01-01'),detalles\_producto AS(  \-- Procesamos los detalles del producto por separado  SELECT   i.order\_id,  p.product\_name,  i.quantity  FROM order\_items i  INNER JOIN products p ON i.product\_id = p.product\_id),informacion\_entrega AS(  \-- Información de entrega y estado  SELECT   o.order\_id,  s.status\_name,  d.delivery\_date  FROM orders o  LEFT JOIN order\_status s ON o.status\_id = s.status\_id  LEFT JOIN deliveries d ON o.order\_id = d.order\_id)\-- Consulta final combinando las CTEs
SELECT   ob.order\_id,  ob.customer\_name,  dp.product\_name,  dp.quantity,  ie.status\_name,  ie.delivery\_date,  pm.payment\_methodFROM orden\_base ob
JOIN detalles\_producto dp ON ob.order\_id = dp.order\_id
JOIN informacion\_entrega ie ON ob.order\_id = ie.order\_id
LEFTJOIN payments pm ON ob.order\_id = pm.payment\_id;
### Ventajas de este enfoque:
1. **Mejor rendimiento**:
- Solo selecciona los campos necesarios
- Reduce la cantidad de datos procesados en memoria
- Permite optimizar cada subconsulta por separado
2. **Mantenibilidad**:
- Código más organizado y fácil de entender
- Más fácil de depurar y modificar
- Cada CTE tiene una responsabilidad específica
3. **Optimización de consultas**:
WITH ventas\_filtradas AS(  SELECT order\_id, customer\_id, total\_amount  FROM orders  WHERE total\_amount > 1000 -- Filtro temprano  AND order\_date >= '2024-01-01')\-- El resto de JOINs trabajará con un conjunto de datos más pequeño
4. **Índices más efectivos**:
\-- Crear índices específicos para las columnas usadas en los JOINs
CREATEINDEX idx\_order\_customer ON orders(customer\_id);CREATEINDEX idx\_order\_items\_order ONorder\_items(order\_id);
5. **Mejor uso de recursos**:
WITH datos\_resumidos AS(  SELECT   customer\_id,  COUNT(\*) as total\_orders,  SUM(total\_amount) as total\_spent  FROM orders  GROUP BY customer\_id -- Agregación temprana)\-- Consultas posteriores trabajarán con datos ya agregados
### Recomendaciones adicionales:
1. **Monitoreo de rendimiento**:
EXPLAINANALYZEWITH...-- tu consulta con CTEsSELECT...
2. **Materialización cuando sea necesario**:
WITH MATERIALIZED datos\_grandes AS(  \-- Para conjuntos de datos que se usarán múltiples veces  SELECT ... FROM tabla\_grande)
3. **Evitar CTEs innecesarias**:
\--No usar CTEs para consultas simples
\-- Mal uso:
WITHsimple\_data AS(  SELECT \* FROM tabla)SELECT \*FROMsimple\_data;\-- Mejor:
SELECT \*FROM tabla;
Esta estructura no solo mejora el rendimiento sino que también hace que el código sea más mantenible y más fácil de optimizar en el futuro.
Cual seria un caso práctico real de los CTE? porque con el ejemplo, creo que funcionaria hacer la consulta normalmente.
--=============================================--Eliminar registros duplicados en una tabla usando CTE--=============================================DECLARE @DuplicateTABLE(IDINT,FNAMEVARCHAR(10),MNAMEVARCHAR(10),ELEMENTOINT)INSERTINTO @DuplicateVALUES(1,'AAA','CCC',1)INSERTINTO @DuplicateVALUES(2,'BBB','DDD',2)INSERTINTO @DuplicateVALUES(1,'AAA','CCC',3)INSERTINTO @DuplicateVALUES(2,'BBB','DDD',4)INSERTINTO @DuplicateVALUES(1,'AAA','CCC',5)INSERTINTO @DuplicateVALUES(2,'BBB','DDD',6)INSERTINTO @DuplicateVALUES(3,'BCB','DGD',7)INSERTINTO @DuplicateVALUES(3,'hhh','DGD',8)INSERTINTO @DuplicateVALUES(2,'BBB','DDD',9)INSERTINTO @DuplicateVALUES(2,'BBB','DDD',10)INSERTINTO @DuplicateVALUES(4,'BBB','DDD',11)INSERTINTO @DuplicateVALUES(3,'uuu','DGD',12)INSERTINTO @DuplicateVALUES(3,'ddd','DGD',13)INSERTINTO @DuplicateVALUES(1,'AAA','CCC',14)SELECT*FROM @Duplicate;WITHCTEas(SELECTROW_NUMBER()OVER(PARTITIONBYID,FName,MNameORDERBY(SELECT1))ASRowID,*FROM @Duplicate)SELECTID,FName,MName,ELEMENTOFROMCTEWHERERowID=1```--=============================================--Eliminar registros duplicados en una tabla usando CTE--=============================================DECLARE @DuplicateTABLE(IDINT,FNAMEVARCHAR(10),MNAMEVARCHAR(10),ELEMENTOINT)INSERTINTO @DuplicateVALUES(1,'AAA','CCC',1)INSERTINTO @DuplicateVALUES(2,'BBB','DDD',2)INSERTINTO @DuplicateVALUES(1,'AAA','CCC',3)INSERTINTO @DuplicateVALUES(2,'BBB','DDD',4)INSERTINTO @DuplicateVALUES(1,'AAA','CCC',5)INSERTINTO @DuplicateVALUES(2,'BBB','DDD',6)INSERTINTO @DuplicateVALUES(3,'BCB','DGD',7)INSERTINTO @DuplicateVALUES(3,'hhh','DGD',8)INSERTINTO @DuplicateVALUES(2,'BBB','DDD',9)INSERTINTO @DuplicateVALUES(2,'BBB','DDD',10)INSERTINTO @DuplicateVALUES(4,'BBB','DDD',11)INSERTINTO @DuplicateVALUES(3,'uuu','DGD',12)INSERTINTO @DuplicateVALUES(3,'ddd','DGD',13)INSERTINTO @DuplicateVALUES(1,'AAA','CCC',14) SELECT \*FROM @Duplicate;WITHCTEas(SELECTROW\_NUMBER()OVER(PARTITIONBYID,FName,MNameORDERBY(SELECT1))ASRowID, \*FROM @Duplicate )SELECTID,FName,MName,ELEMENTOFROMCTEWHERERowID=1
Otra cosa, puedo seleccionar la CTE durante la misma creacion asi:
El apunte es viejito, pero aplica (levante la mano quien conoce AdventureWorks !)
La explicación infiere que las CTE se usan en los procesos de ETL, si bien son útiles, no es el único lugar donde las podemos usar.
Como la profesora dice estas CTE son temporales, no se almacenan físicamente, son queries que se almacenan en la memoria, solamente existen mientras se ejecuta el query.
Entonces la puedo usar por ejemplo para jugar con una consulta compleja y cuando ya tengo el resultado que quiero, muevo el contenido de la CTE hacia el cuerpo de una VISTA. asi:
preparo una consulta comlpeta dentro de un CTE:
CREATE VIEW Vista_Ejemplo AS SELECT columna1, columna2 FROM Tabla WHERE condicion;```js
WITH CTE_Ejemplo AS (
SELECT columna1, columna2
FROM Tabla
WHERE condicion
)
SELECT * FROM CTE_Ejemplo;
```js
CREATE VIEW Vista_Ejemplo AS
SELECT columna1, columna2
FROM Tabla
WHERE condicion;
Te explico las Expresiones de Tablas Comunes (CTE - Common Table Expressions) con ejemplos sencillos.
### Ejemplo Básico - Tienda de Ropa
\-- CTE simple para calcular ventas por categoría
WITH ventas\_por\_categoria AS(  SELECT   categoria,  COUNT(\*) as total\_ventas,  SUM(precio) as ingresos\_totales  FROM ventas  GROUP BY categoria)SELECT \*FROM ventas\_por\_categoria
WHERE total\_ventas >100;
### Ejemplo con Múltiples CTEs - Biblioteca
\-- CTEs múltiples para análisis de préstamos
WITH prestamos\_activos AS(  SELECT libro\_id, COUNT(\*) as veces\_prestado  FROM prestamos  WHERE devuelto = false  GROUP BY libro\_id),libros\_populares AS(  SELECT   l.titulo,  l.autor,  p.veces\_prestado  FROM libros l  JOIN prestamos\_activos p ON l.id = p.libro\_id)SELECT \*FROM libros\_populares
ORDERBY veces\_prestado DESCLIMIT5;
### CTE Recursiva - Estructura Organizacional
\-- CTE recursiva para mostrar jerarquía de empleados
WITH RECURSIVE jerarquia\_empleados AS(  \-- Caso base: empleados sin jefe (nivel más alto)  SELECT   id,  nombre,  jefe\_id,  1 as nivel  FROM empleados  WHERE jefe\_id IS NULL     UNION ALL     \-- Caso recursivo: empleados con sus jefes  SELECT   e.id,  e.nombre,  e.jefe\_id,  j.nivel + 1  FROM empleados e  JOIN jerarquia\_empleados j ON e.jefe\_id = j.id)SELECT \*FROM jerarquia\_empleados
ORDERBY nivel, nombre;
### Ejemplo Práctico - Restaurante
\-- Análisis de ventas de un restaurante
WITH ventas\_diarias AS(  SELECT   fecha,  SUM(total) as venta\_total  FROM pedidos  GROUP BY fecha),promedio\_semanal AS(  SELECT   DATE\_TRUNC('week', fecha) as semana,  AVG(venta\_total) as promedio\_ventas  FROM ventas\_diarias  GROUP BY DATE\_TRUNC('week', fecha))SELECT   semana,  promedio\_ventas,  LAG(promedio\_ventas) OVER (ORDER BY semana) as promedio\_semana\_anteriorFROM promedio\_semanal;
### Ejemplo de Análisis de Clientes
\-- Segmentación de clientes
WITH compras\_cliente AS(  SELECT   cliente\_id,  COUNT(\*) as total\_compras,  SUM(monto) as monto\_total  FROM compras  GROUP BY cliente\_id),segmentos AS(  SELECT   cliente\_id,  CASE   WHEN monto\_total > 1000 THEN 'VIP'  WHEN monto\_total > 500 THEN 'Regular'  ELSE 'Ocasional'  END as segmento  FROM compras\_cliente)SELECT   segmento,  COUNT(\*) as cantidad\_clientesFROM segmentos
GROUPBY segmento;
### Ventajas de usar CTEs:
1. **Legibilidad**
\-- Más legible con CTE
WITH datos\_filtrados AS(  SELECT \* FROM ventas WHERE fecha >= '2024-01-01')SELECT \*FROM datos\_filtrados WHERE monto >1000;\-- Menos legible sin CTE
SELECT \*FROM   (SELECT \* FROM ventas WHERE fecha >= '2024-01-01') WHERE monto >1000;
2. **Reutilización**
WITH datos\_base AS(  SELECT \* FROM productos WHERE stock > 0)\-- Usar datos\_base múltiples veces
SELECT \*FROM datos\_base WHERE precio <100UNIONALLSELECT \*FROM datos\_base WHERE categoria ='Electrónica';
3. **Modularidad**
WITH productos\_activos AS(  SELECT \* FROM productos WHERE estado = 'activo'),ventas\_recientes AS(  SELECT \* FROM ventas WHERE fecha > CURRENT\_DATE - INTERVAL '30 days'),analisis\_final AS(  SELECT   p.nombre,  COUNT(v.id) as total\_ventas  FROM productos\_activos p  LEFT JOIN ventas\_recientes v ON p.id = v.producto\_id  GROUP BY p.nombre)SELECT \*FROM analisis\_final;
Las CTEs son especialmente útiles cuando:
- Necesitas dividir una consulta compleja en partes más pequeñas
- Quieres reutilizar resultados intermedios
- Necesitas hacer consultas recursivas
- Buscas mejorar la legibilidad del código
Las CTE vienen siendo el equivalente a una funcion en programación
