Elegir entre una Subconsulta, una CTE o una Window Function no es solo una cuestión de preferencia sintáctica, sino de estrategia de ejecución y mantenibilidad del código.

A continuación, se muestra un análisis técnico comparativo para determinar el escenario de uso ideal para cada herramienta en PostgreSQL.

1. Subconsultas: El Recurso de “Propósito Específico”

Las subconsultas son ideales cuando el cálculo es pequeño, puntual y no se requiere reutilizar el resultado en otras partes de la consulta principal.

• Cuándo usarlas:
  • Predicados de Existencia: Usar WHERE EXISTS (SELECT 1 ...) es extremadamente eficiente para verificar presencia de datos sin cargar columnas innecesarias.
  • Filtros de valor único: Cuando necesitas comparar una columna contra un valor escalar dinámico (ej. WHERE precio > (SELECT AVG...)).
• Desventaja: Las subconsultas correlacionadas pueden ser una pesadilla de rendimiento, ya que el motor podría intentar ejecutarlas por cada fila de la consulta externa (complejidad $O(n^2)$).

2. CTE (Common Table Expressions): El Arquitecto del Orden

Las CTEs (WITH) son la mejor opción para la legibilidad humana y para estructurar procesos de transformación de datos en etapas (ETL dentro de SQL).

• Cuándo usarlas:
  • Lógica Modular: Cuando el problema se resuelve mejor dividiéndolo en “pasos” (Paso A: Limpiar, Paso B: Agrupar, Paso C: Unir).
  • Reutilización: Si necesitas llamar al mismo conjunto de resultados temporales dos o tres veces en diferentes JOINs de la misma consulta.
  • Recursividad: Es la única opción para manejar datos jerárquicos (organigramas, categorías padre-hijo).
• Nota Técnica en PostgreSQL: Desde la versión 12, PostgreSQL puede decidir “incluir” la CTE en la consulta principal para optimizarla, o puede forzarse esta operación con WITH nombre AS MATERIALIZED (...) si el cálculo es muy costoso y quieres que se guarde en memoria temporal.

3. Window Functions: Los Escalpelos de la Analítica

Son la herramienta más sofisticada para analítica. Su gran ventaja es que permiten realizar cálculos agregados sin perder la granularidad de la fila individual.

• Cuándo usarlas:
  • Comparativas Fila vs. Grupo: Ver cuánto representa la venta actual respecto al total del mes.
  • Ránkings y Posiciones: ROW_NUMBER, RANK o DENSE_RANK.
  • Series de Tiempo: Calcular promedios móviles, diferencias contra el día anterior (LAG/LEAD) o acumulados (SUM(...) OVER(ORDER BY...)).
• Ventaja: Generalmente requieren un solo escaneo de la tabla (y un ordenamiento), lo que suele ser mucho más rápido que hacer un JOIN contra una subconsulta agrupada.

Cuadro Comparativo de Decisión Profesional

Criterio	Subconsulta	CTE (WITH)	Window Function
Legibilidad	Baja (Anidada)	Muy Alta (Secuencial)	Alta (Declarativa)
Rendimiento	Variable (Riesgo en Correlacionadas)	Media/Alta (Postgres 12+)	Excelente (Analítica)
Cálculo Recursivo	No	Sí	No
Mantiene filas originales	No (si es en el FROM)	No (si es en el FROM)	Sí (Siempre)
Uso en WHERE	Sí	No (Requiere filtro externo)	No (Requiere envolver)

Matriz de Casos de Uso

1. ¿Necesitas comparar una fila con el promedio de su grupo? Usa Window Function (PARTITION BY).
2. ¿Necesitas filtrar datos basándote en si existen en otra tabla? Usa Subconsulta (EXISTS).
3. ¿Tu query tiene más de 50 líneas y es difícil de entender? Refactoriza usando CTEs.
4. ¿Necesitas calcular un acumulado (Running Total)? Usa Window Function (SUM OVER ORDER BY).
5. ¿Necesitas los 5 productos más vendidos por cada categoría? Usa Window Function (DENSE_RANK).

De manera profesional, el flujo de decisión debería ser:

1. Intenta resolverlo con una Window Function si es analítico (por eficiencia).
2. Si el cálculo es complejo o requiere pasos intermedios, usa una CTE (por mantenibilidad).
3. Usa Subconsultas solo para verificaciones rápidas (IN, EXISTS) o filtros escalares muy simples.