Agregaciones por grupo sin GROUP BY
Use una subconsulta correlacionada para calcular el máximo de un grupo junto con las filas de detalle
Agregaciones por grupo sin GROUP BY es una lección gratuita de Coding Interview Prep en CoddyKit. Esta es la lección 2 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de Coding Interview Prep, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de Coding Interview Prep incluye 4 lecciones en total.
El problema del detalle y la agregación
Una pregunta habitual en entrevistas: "Muestre cada fila junto con una función de agregación de su grupo." Por ejemplo, enumerar cada empleado con el salario máximo de su departamento en la misma línea.
Un GROUP BY normal agrupa las filas, por lo que no puede conservar el detalle de cada empleado. Necesita reunir las filas detalladas y un valor a nivel de grupo.
Una subconsulta correlacionada resuelve esto con elegancia: calcula la agregación del grupo para cada fila detallada sin agruparlas.
Por qué GROUP BY no funciona aquí
Si escribe SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id, obtiene una fila por departamento y pierde los nombres individuales.
Si añade name a SELECT sin añadirlo a GROUP BY, se produce el error clásico "column must appear in GROUP BY".
El entrevistador está comprobando si entiende que GROUP BY reduce la cardinalidad. Para conservar las filas detalladas, debe calcular la agregación de otra forma.
La subconsulta correlacionada al rescate
Coloque la agregación del grupo en la lista SELECT como una subconsulta correlacionada. Cada fila de empleado activa una función MAX interna limitada al departamento de ese empleado.
La correlación e2.dept_id = e1.dept_id vincula la agregación con el grupo correcto, mientras que la consulta externa sigue devolviendo una fila por empleado.
SELECT e1.name,
e1.dept_id,
e1.salary,
(SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS dept_max_salary
FROM employees e1;Comparar cada fila con su grupo
Una vez que la agregación del grupo forma parte de la consulta, puede comparar cada fila con ella. Una pregunta frecuente es: "Encuentre los empleados que ganan más que el promedio de su departamento."
Aquí, el AVG correlacionado está en WHERE, por lo que cada empleado se compara con el promedio de su propio departamento.
SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Calcular la diferencia con respecto al grupo
También puede mostrar cuánto se aleja cada fila de la agregación de su grupo. Restar el promedio correlacionado proporciona la diferencia de cada fila.
Observe que la misma subconsulta correlacionada puede reutilizarse en varias expresiones SELECT; el motor la evalúa por fila cada vez que aparece.
SELECT e1.name,
e1.salary,
e1.salary - (SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS gap_from_dept_avg
FROM employees e1;Encontrar al empleado con mayor salario de cada grupo
Para devolver únicamente a la persona con el salario más alto de cada departamento, compare cada salario con el MAX correlacionado y conserve las coincidencias.
Este patrón devuelve los empates: si dos empleados comparten el salario máximo del departamento, aparecen ambos. El tratamiento de los empates suele ser una pregunta de seguimiento del entrevistador.
SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);La alternativa de las funciones de ventana
SQL moderno ofrece una herramienta más limpia: las funciones de ventana. MAX(salary) OVER (PARTITION BY dept_id) calcula la agregación del grupo sin agrupar las filas y sin volver a recorrer la tabla mediante una correlación.
A los entrevistadores les encanta que pueda proporcionar ambas soluciones y explicar que la versión con ventana suele ofrecer un mejor rendimiento porque recorre la tabla una sola vez.
SELECT name,
dept_id,
salary,
MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_salary
FROM employees;Diferencias entre correlación y funciones de ventana
Ambos enfoques devuelven la misma estructura, pero se diferencian en lo siguiente:
- Subconsulta correlacionada: es portable y funciona en motores muy antiguos, pero se reevalúa para cada fila.
- Función de ventana: realiza una sola pasada, es mucho más rápida en tablas grandes y requiere compatibilidad con funciones de ventana de SQL.
Diga cuál elegiría y por qué. Para una consulta puntual sobre una tabla pequeña, cualquiera de las dos está bien; para análisis a gran escala, prefiera la función de ventana.
Ejemplo resuelto: pedidos superiores al promedio del cliente
Aplique el patrón a los pedidos. Muestre los pedidos cuyo importe supera el valor promedio de los pedidos realizados por su propio cliente.
El AVG correlacionado está limitado mediante o2.customer_id = o1.customer_id, lo que proporciona a cada pedido la referencia personal de su cliente.
SELECT o1.order_id, o1.customer_id, o1.amount
FROM orders o1
WHERE o1.amount > (
SELECT AVG(o2.amount)
FROM orders o2
WHERE o2.customer_id = o1.customer_id
);Preste atención a los casos NULL y de grupos vacíos
Si un grupo tiene una sola fila, su promedio es igual a esa fila, por lo que salary > avg es falso y la fila se excluye. Mencione este caso límite de forma proactiva.
Además, AVG y MAX ignoran los salarios NULL, de acuerdo con la semántica de las funciones de agregación de SQL. Si todos los valores de un grupo son NULL, la agregación es NULL y las comparaciones pasan a ser UNKNOWN, por lo que la fila se excluye. Anticipar estos casos es lo que distingue una respuesta exhaustiva.
Calcular el rango dentro de un grupo
Puede expresar la posición de una fila dentro de su grupo mediante un COUNT correlacionado. Para encontrar la posición del salario de cada empleado dentro de su departamento, cuente cuántos compañeros ganan más.
La posición 1 corresponde al salario más alto. Sumar 1 convierte el recuento de empleados con un salario superior en una posición basada en 1, y la correlación mantiene el cálculo limitado al departamento.
SELECT e1.name,
e1.dept_id,
e1.salary,
(SELECT COUNT(*) + 1
FROM employees e2
WHERE e2.dept_id = e1.dept_id
AND e2.salary > e1.salary) AS salary_rank_in_dept
FROM employees e1;Comprobación rápida
Elija la razón por la que una subconsulta correlacionada es mejor que un GROUP BY normal para esta tarea.
Recapitulación: agregaciones por grupo sin GROUP BY
Conclusiones clave:
- Una subconsulta correlacionada coloca una agregación a nivel de grupo en cada fila detallada sin agruparlas.
- Úsela en SELECT para mostrar la agregación o en WHERE para comparar cada fila con su grupo.
- El patrón
= MAX(...)devuelve todas las filas superiores que están empatadas. - Una función de ventana con
PARTITION BYhace lo mismo en una sola pasada y normalmente escala mejor.
Ofrezca ambas soluciones y justifique su elección en la entrevista.
Preguntas frecuentes
¿La lección «Agregaciones por grupo sin GROUP BY» es gratis?
Sí — el texto completo de «Agregaciones por grupo sin GROUP BY» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de Coding Interview Prep, actualiza a CoddyKit PRO. El curso de Coding Interview Prep incluye 4 lecciones en total.
¿Qué aprenderé en «Agregaciones por grupo sin GROUP BY»?
Use una subconsulta correlacionada para calcular el máximo de un grupo junto con las filas de detalle Practicas Coding Interview Prep con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.
¿Necesito experiencia previa para empezar Coding Interview Prep?
No se requiere experiencia previa. Coding Interview Prep en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 2 de 4.
¿Cuánto tiempo toma la lección «Agregaciones por grupo sin GROUP BY»?
La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.
¿Puedo escribir y ejecutar código en esta lección de Coding Interview Prep?
Sí. Cada lección de Coding Interview Prep incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.
Todas las lecciones de este curso
- Anatomía de una subconsulta correlacionada
- Agregaciones por grupo sin GROUP BY
- EXISTS y NOT EXISTS correlacionados
- Reescribir subconsultas correlacionadas como joins