Construyendo Subconsultas Eficientes en PostgreSQL con CTEs: Ejemplos en Retail y Banca

generada con IA OpenIA

En el mundo del desarrollo de bases de datos, las subconsultas son una herramienta poderosa que nos permite realizar consultas complejas de manera modular y eficiente. PostgreSQL, uno de los sistemas de gestión de bases de datos más avanzados y populares, ofrece una técnica llamada Common Table Expressions (CTEs) para optimizar y ejecutar subconsultas. En este blog, exploraremos cómo construir subconsultas eficientes en PostgreSQL utilizando CTEs con ejemplos prácticos en los sectores de retail y banca.

¿Qué son las CTEs?

Las Common Table Expressions (CTEs) son una forma de definir consultas temporales que pueden ser referenciadas dentro de la consulta principal. Las CTEs mejoran la legibilidad y el mantenimiento del código SQL, especialmente cuando se trata de consultas complejas. Se definen utilizando la cláusula WITH.

Ventajas de las CTEs

• Legibilidad: Las CTEs hacen que las consultas sean más fáciles de leer y entender.
• Reutilización de código: Permiten reutilizar una subconsulta en varias partes de la consulta principal.
• Mantenimiento: Facilitan el mantenimiento del código, ya que las subconsultas se definen de forma clara y separada.

Ejemplos de CTEs en Retail y Banca

Ejemplo en Retail: Filtrar Productos por Precio Promedio

Supongamos que queremos crear una subconsulta que filtre productos con un precio mayor al promedio y luego usar esta subconsulta en la consulta principal.

WITH filtered_products AS (
  SELECT nombre_producto, precio
  FROM productos
  WHERE precio > (SELECT AVG(precio) FROM productos)
)
SELECT nombre_producto, precio
FROM filtered_products;

En este ejemplo, la CTE filtered_products selecciona los productos cuyo precio es mayor que el precio promedio de todos los productos. Luego, la consulta principal selecciona todos los productos de la CTE.

Ejemplo en Banca: Filtrar Cuentas por Saldo Promedio

Queremos crear una subconsulta que filtre cuentas con un saldo mayor al promedio y luego usar esta subconsulta en la consulta principal.

WITH filtered_accounts AS (
  SELECT numero_cuenta, saldo
  FROM cuentas_bancarias
  WHERE saldo > (SELECT AVG(saldo) FROM cuentas_bancarias)
)
SELECT numero_cuenta, saldo
FROM filtered_accounts;

En este ejemplo, la CTE filtered_accounts selecciona las cuentas cuyo saldo es mayor que el saldo promedio de todas las cuentas bancarias. Luego, la consulta principal selecciona todas las cuentas de la CTE.

Ejemplo en Retail: Productos por Categoría con Precio Promedio

Queremos obtener el precio promedio de los productos por cada categoría y mostrarlo junto con el nombre del producto y su precio.

WITH category_averages AS (
  SELECT categoria_id, AVG(precio) AS precio_promedio
  FROM productos
  GROUP BY categoria_id
)
SELECT p.nombre_producto, p.precio, ca.precio_promedio
FROM productos p
JOIN category_averages ca ON p.categoria_id = ca.categoria_id;

En este ejemplo, la CTE category_averages calcula el precio promedio de los productos por categoría. Luego, la consulta principal une los productos con los precios promedio por categoría.

Ejemplo en Banca: Saldos Promedios por Tipo de Cuenta

Queremos obtener el saldo promedio de las cuentas por cada tipo de cuenta y mostrarlo junto con el número de cuenta y su saldo.

WITH account_type_averages AS (
  SELECT tipo_cuenta, AVG(saldo) AS saldo_promedio
  FROM cuentas_bancarias
  GROUP BY tipo_cuenta
)
SELECT c.numero_cuenta, c.saldo, ata.saldo_promedio
FROM cuentas_bancarias c
JOIN account_type_averages ata ON c.tipo_cuenta = ata.tipo_cuenta;

En este ejemplo, la CTE account_type_averages calcula el saldo promedio de las cuentas por tipo de cuenta. Luego, la consulta principal une las cuentas bancarias con los saldos promedio por tipo de cuenta.

Ejemplo en Retail: Productos con Ventas Totales

Queremos calcular el total de ventas por producto y mostrarlo junto con el nombre del producto.

WITH total_sales AS (
  SELECT producto_id, SUM(cantidad * precio) AS ventas_totales
  FROM ventas
  GROUP BY producto_id
)
SELECT p.nombre_producto, ts.ventas_totales
FROM productos p
JOIN total_sales ts ON p.producto_id = ts.producto_id;

En este ejemplo, la CTE total_sales calcula el total de ventas por producto. Luego, la consulta principal une los productos con sus ventas totales.

Ejemplo en Banca: Cuentas con Transacciones Recientes

Queremos obtener las cuentas que han tenido transacciones en el último mes y mostrar el número de cuenta junto con el saldo.

WITH recent_transactions AS (
  SELECT DISTINCT numero_cuenta
  FROM transacciones
  WHERE fecha > CURRENT_DATE - INTERVAL '1 month'
)
SELECT c.numero_cuenta, c.saldo
FROM cuentas_bancarias c
JOIN recent_transactions rt ON c.numero_cuenta = rt.numero_cuenta;

En este ejemplo, la CTE recent_transactions selecciona las cuentas que han tenido transacciones en el último mes. Luego, la consulta principal une estas cuentas con su saldo.

Conclusión

Las CTEs son una herramienta esencial en PostgreSQL para realizar subconsultas de manera eficiente y legible. Al entender cómo y cuándo utilizar CTEs, podemos optimizar el rendimiento de nuestras bases de datos y obtener resultados más precisos. Ya sea en el sector retail o en la banca, las CTEs pueden ayudarnos a extraer información valiosa y tomar decisiones informadas.