mysql - valor - Obtenga los primeros n registros para cada grupo de resultados agrupados

sql maximo valor de un grupo

Question

El siguiente es el ejemplo más simple posible, aunque cualquier solución debería ser capaz de escalar en función de la cantidad de resultados necesarios:

Dada una tabla como la siguiente, con columnas de personas, grupos y edades, ¿cómo conseguirías las 2 personas más viejas de cada grupo? (Los lazos dentro de los grupos no deberían arrojar más resultados, pero debe dar los primeros 2 en orden alfabético)

+--------+-------+-----+ | Person | Group | Age | +--------+-------+-----+ | Bob | 1 | 32 | | Jill | 1 | 34 | | Shawn | 1 | 42 | | Jake | 2 | 29 | | Paul | 2 | 36 | | Laura | 2 | 39 | +--------+-------+-----+

Conjunto de resultados deseado:

+--------+-------+-----+ | Shawn | 1 | 42 | | Jill | 1 | 34 | | Laura | 2 | 39 | | Paul | 2 | 36 | +--------+-------+-----+

NOTA: Esta pregunta se basa en una anterior: obtenga registros con el valor máximo para cada grupo de resultados de SQL agrupados , para obtener una única fila superior de cada grupo y que recibió una gran respuesta específica de MySQL de @Bohemian:

select *
from (select * from mytable order by `Group`, Age desc, Person) x
group by `Group`

Me encantaría poder construir esto, aunque no veo cómo.

Answer 1

¿Qué hay de usar la auto unión?

CREATE TABLE mytable (person, groupname, age);
INSERT INTO mytable VALUES(''Bob'',1,32);
INSERT INTO mytable VALUES(''Jill'',1,34);
INSERT INTO mytable VALUES(''Shawn'',1,42);
INSERT INTO mytable VALUES(''Jake'',2,29);
INSERT INTO mytable VALUES(''Paul'',2,36);
INSERT INTO mytable VALUES(''Laura'',2,39);
SELECT a.* FROM mytable AS a
LEFT JOIN mytable AS a2
ON a.groupname = a2.groupname AND a.age <= a2.age
GROUP BY a.person
HAVING COUNT(*) <= 2
ORDER BY a.groupname, a.age DESC;

me da:

a.person a.groupname a.age
---------- ----------- ----------
Shawn 1 42
Jill 1 34
Laura 2 39
Paul 2 36

Me inspiró mucho la respuesta de Bill Karwin para seleccionar los 10 mejores registros para cada categoría

Además, estoy usando SQLite, pero esto debería funcionar en MySQL.

Otra cosa: en lo anterior, reemplacé la columna de group con una columna de nombre de grupo para mayor comodidad.

Editar :

Siguiendo con el comentario del OP sobre los resultados del empate faltante, incrementé la respuesta de snuffin para mostrar todos los vínculos. Esto significa que si los últimos son vínculos, se pueden devolver más de 2 filas, como se muestra a continuación:

.headers on
.mode column
CREATE TABLE foo (person, groupname, age);
INSERT INTO foo VALUES(''Paul'',2,36);
INSERT INTO foo VALUES(''Laura'',2,39);
INSERT INTO foo VALUES(''Joe'',2,36);
INSERT INTO foo VALUES(''Bob'',1,32);
INSERT INTO foo VALUES(''Jill'',1,34);
INSERT INTO foo VALUES(''Shawn'',1,42);
INSERT INTO foo VALUES(''Jake'',2,29);
INSERT INTO foo VALUES(''James'',2,15);
INSERT INTO foo VALUES(''Fred'',1,12);
INSERT INTO foo VALUES(''Chuck'',3,112);
SELECT a.person, a.groupname, a.age
FROM foo AS a
WHERE a.age >= (SELECT MIN(b.age)
FROM foo AS b
WHERE (SELECT COUNT(*)
FROM foo AS c
WHERE c.groupname = b.groupname AND c.age >= b.age) <= 2
GROUP BY b.groupname)
ORDER BY a.groupname ASC, a.age DESC;

me da:

person groupname age
---------- ---------- ----------
Shawn 1 42
Jill 1 34
Laura 2 39
Paul 2 36
Joe 2 36
Chuck 3 112

Answer 2

Aquí hay una forma de hacerlo, utilizando UNION ALL (Ver SQL Fiddle con demostración ). Esto funciona con dos grupos, si tiene más de dos grupos, entonces deberá especificar el número de group y agregar consultas para cada group :

(
select *
from mytable
where `group` = 1
order by age desc
LIMIT 2
)
UNION ALL
(
select *
from mytable
where `group` = 2
order by age desc
LIMIT 2
)

Hay una variedad de formas de hacerlo, consulte este artículo para determinar la mejor ruta para su situación:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Editar:

Esto también podría funcionar para usted, genera un número de fila para cada registro. Usando un ejemplo del enlace de arriba, esto devolverá solo aquellos registros con un número de fila inferior o igual a 2:

select person, `group`, age
from
(
select person, `group`, age,
(@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number
from test t
CROSS JOIN (select @num:=0, @group:=null) c
order by `Group`, Age desc, person
) as x
where x.row_number <= 2;

Ver Demo

Answer 3

En SQL Server row_numer() es una poderosa función que puede obtener resultados fácilmente de la siguiente manera

select Person,[group],age
from
(
select * ,row_number() over(partition by [group] order by age desc) rn
from mytable
) t
where rn <= 2

Answer 4

En otras bases de datos puede hacer esto usando ROW_NUMBER . MySQL no admite ROW_NUMBER pero puede usar variables para emularlo:

SELECT
person,
groupname,
age
FROM
(
SELECT
person,
groupname,
age,
@rn := IF(@prev = groupname, @rn + 1, 1) AS rn,
@prev := groupname
FROM mytable
JOIN (SELECT @prev := NULL, @rn := 0) AS vars
ORDER BY groupname, age DESC, person
) AS T1
WHERE rn <= 2

sqlfiddle trabajar en línea: sqlfiddle

Editar. Me di cuenta de que Bluefeet publicó una respuesta muy similar: +1 a él. Sin embargo, esta respuesta tiene dos pequeñas ventajas:

1. Es una consulta única. Las variables se inicializan dentro de la instrucción SELECT.
2. Maneja vínculos como se describe en la pregunta (orden alfabético por nombre).

Así que lo dejaré aquí en caso de que pueda ayudar a alguien.

Answer 5

Hay una muy buena respuesta a este problema en MySQL - Cómo obtener las N mejores filas por cada grupo

En función de la solución en el enlace al que se hace referencia, su consulta sería como:

SELECT Person, Group, Age
FROM
(SELECT Person, Group, Age,
@group_rank := IF(@group = Group, @group_rank + 1, 1) AS group_rank,
@current_group := Group
FROM `your_table`
ORDER BY Group, Age DESC
) ranked
WHERE group_rank <= `n`
ORDER BY Group, Age DESC;

donde n es la top n tu tabla es el nombre de tu tabla.

Creo que la explicación en la referencia es muy clara. Para una referencia rápida, lo copiaré y pegaré aquí:

Actualmente, MySQL no admite la función ROW_NUMBER () que puede asignar un número de secuencia dentro de un grupo, pero como solución temporal podemos usar variables de sesión de MySQL.

Estas variables no requieren declaración, y se pueden usar en una consulta para hacer cálculos y almacenar resultados intermedios.

@current_country: = país Este código se ejecuta para cada fila y almacena el valor de la columna del país en la variable @current_country.

@country_rank: = IF (@current_country = country, @country_rank + 1, 1) En este código, si @current_country es el mismo, incrementamos el rango; de lo contrario, establézcalo en 1. Para la primera fila, @current_country es NULL, por lo tanto, el rango es también establecido en 1.

Para una clasificación correcta, necesitamos tener ORDEN POR país, población DESC

Answer 6

La solución Snuffin parece bastante lenta de ejecutar cuando tienes muchas filas y las soluciones Mark Byers / Rick James y Bluefeet no funcionan en mi entorno (MySQL 5.6) porque se aplica order by después de la ejecución de select, por lo que aquí hay una variante de soluciones de Marc Byers / Rick James para solucionar este problema (con una selección imbricada adicional):

select person, groupname, age
from
(
select person, groupname, age,
(@rn:=if(@prev = groupname, @rn +1, 1)) as rownumb,
@prev:= groupname
from
(
select person, groupname, age
from persons
order by groupname , age desc, person
) as sortedlist
JOIN (select @prev:=NULL, @rn :=0) as vars
) as groupedlist
where rownumb<=2
order by groupname , age desc, person;

Intenté una consulta similar en una tabla con 5 millones de filas y devuelve el resultado en menos de 3 segundos

Answer 7

Mira esto:

SELECT
p.Person,
p.`Group`,
p.Age
FROM
people p
INNER JOIN
(
SELECT MAX(Age) AS Age, `Group` FROM people GROUP BY `Group`
UNION
SELECT MAX(p3.Age) AS Age, p3.`Group` FROM people p3 INNER JOIN (SELECT MAX(Age) AS Age, `Group` FROM people GROUP BY `Group`) p4 ON p3.Age < p4.Age AND p3.`Group` = p4.`Group` GROUP BY `Group`
) p2 ON p.Age = p2.Age AND p.`Group` = p2.`Group`
ORDER BY
`Group`,
Age DESC,
Person;

SQL Fiddle: http://sqlfiddle.com/#!2/cdbb6/15

Answer 8

Prueba esto:

SELECT a.person, a.group, a.age FROM person AS a WHERE
(SELECT COUNT(*) FROM person AS b
WHERE b.group = a.group AND b.age >= a.age) <= 2
ORDER BY a.group ASC, a.age DESC

DEMO

Answer 9

Quería compartir esto porque pasé mucho tiempo buscando una forma fácil de implementar esto en un programa Java en el que estoy trabajando. Esto no da el resultado que está buscando, pero está cerca. La función en mysql llamada GROUP_CONCAT() funcionó muy bien para especificar cuántos resultados se devolverán en cada grupo. Usar LIMIT o cualquiera de las otras formas sofisticadas de tratar de hacer esto con COUNT no funcionó para mí. Entonces, si está dispuesto a aceptar un resultado modificado, es una gran solución. Digamos que tengo una tabla llamada ''estudiante'' con identificaciones de estudiantes, su género y gpa. Digamos que quiero obtener 5 gpas por cada género. Entonces puedo escribir la consulta de esta manera

SELECT sex, SUBSTRING_INDEX(GROUP_CONCAT(cast(gpa AS char ) ORDER BY gpa desc), '','',5)
AS subcategories FROM student GROUP BY sex;

Tenga en cuenta que el parámetro ''5'' le dice cuántas entradas concatenar en cada fila

Y la salida se vería como

+--------+----------------+
| Male | 4,4,4,4,3.9 |
| Female | 4,4,3.9,3.9,3.8|
+--------+----------------+

También puede cambiar la variable ORDER BY y ordenarlos de otra manera. Entonces, si tuviera la edad del alumno, podría reemplazar el ''gpa desc'' por ''age desc'' y ¡funcionará! También puede agregar variables al grupo por instrucción para obtener más columnas en la salida. Así que esta es solo una forma que encontré que es bastante flexible y funciona bien si estás de acuerdo con los resultados de la lista.

Answer 10

Si las otras respuestas no son lo suficientemente rápidas Prueba este código :

SELECT
province, n, city, population
FROM
( SELECT @prev := '''', @n := 0 ) init
JOIN
( SELECT @n := if(province != @prev, 1, @n + 1) AS n,
@prev := province,
province, city, population
FROM Canada
ORDER BY
province ASC,
population DESC
) x
WHERE n <= 3
ORDER BY province, n;

Salida:

+---------------------------+------+------------------+------------+
| province | n | city | population |
+---------------------------+------+------------------+------------+
| Alberta | 1 | Calgary | 968475 |
| Alberta | 2 | Edmonton | 822319 |
| Alberta | 3 | Red Deer | 73595 |
| British Columbia | 1 | Vancouver | 1837970 |
| British Columbia | 2 | Victoria | 289625 |
| British Columbia | 3 | Abbotsford | 151685 |
| Manitoba | 1 | ...