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 | ...