Uzyskaj n najlepszych rekordów dla każdej grupy zgrupowanych wyników


140

Poniżej przedstawiono najprostszy możliwy przykład, chociaż każde rozwiązanie powinno być w stanie skalować do dowolnej liczby n najlepszych wyników:

Biorąc pod uwagę poniższą tabelę z kolumnami osoby, grupy i wieku, w jaki sposób można uzyskać 2 najstarsze osoby w każdej grupie? (Remisy w grupach nie powinny dawać więcej wyników, ale należy podać pierwsze 2 w kolejności alfabetycznej)

+ -------- + ------- + ----- +
| Osoba | Grupa | Wiek |
+ -------- + ------- + ----- +
| Bob | 1 | 32 |
| Jill | 1 | 34 |
| Shawn | 1 | 42 |
| Jake | 2 | 29 |
| Paul | 2 | 36 |
| Laura | 2 | 39 |
+ -------- + ------- + ----- +

Pożądany zestaw wyników:

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

UWAGA: To pytanie opiera się na poprzednim - Uzyskaj rekordy z maksymalną wartością dla każdej grupy zgrupowanych wyników SQL - aby uzyskać pojedynczy górny wiersz z każdej grupy i który otrzymał świetną odpowiedź specyficzną dla MySQL od @Bohemian:

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

Chciałbym móc to zbudować, chociaż nie wiem jak.



2
Sprawdź ten przykład. Jest prawie zbliżony do tego, o co prosisz: stackoverflow.com/questions/1537606/ ...
Savas Vedova

Używasz LIMIT w GROUP BY, aby uzyskać N wyników na grupę? stackoverflow.com/questions/2129693/…
Edye Chan

Odpowiedzi:


88

Oto jeden ze sposobów, aby to zrobić, używając UNION ALL(Zobacz SQL Fiddle with Demo ). Działa to z dwiema grupami, jeśli masz więcej niż dwie grupy, musisz określić groupliczbę i dodać zapytania dla każdej 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
)

Można to zrobić na wiele sposobów, zapoznaj się z tym artykułem, aby określić najlepszą trasę w swojej sytuacji:

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

Edytować:

To może zadziałać również dla Ciebie, generuje numer wiersza dla każdego rekordu. Korzystając z przykładu z linku powyżej, zwróci to tylko te rekordy z numerem wiersza mniejszym lub równym 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;

Zobacz Demo


52
gdyby miał ponad 1000 grup, czy nie byłoby to trochę przerażające?
Charles Forest

1
@CharlesForest tak, byłoby i dlatego stwierdziłem, że musisz określić to dla więcej niż dwóch grup. Stanie się brzydki.
Taryn

1
@CharlesForest Myślę, że znalazłem lepsze rozwiązanie, zobacz moją edycję
Taryn

1
Uwaga dla każdego, kto to czyta: Wersja zawierająca zmienne jest bliska poprawności. Jednak MySQL nie gwarantuje kolejności oceny wyrażeń w SELECT(i, w rzeczywistości, czasami ocenia je poza kolejnością). Kluczem do rozwiązania jest umieszczenie wszystkich przypisań zmiennych w jednym wyrażeniu; oto przykład: stackoverflow.com/questions/38535020/… .
Gordon Linoff,

1
@GordonLinoff Zaktualizowałem moją odpowiedź, dziękuję za wskazanie. Aktualizacja zajęła mi też zbyt dużo czasu.
Taryn

63

W innych bazach danych możesz to zrobić za pomocą ROW_NUMBER. MySQL nie obsługuje, ROW_NUMBERale możesz użyć zmiennych, aby go emulować:

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

Zobacz, jak działa online: sqlfiddle


Edytuj Właśnie zauważyłem, że bluefeet opublikował bardzo podobną odpowiedź: +1 do niego. Jednak ta odpowiedź ma dwie małe zalety:

  1. To jest pojedyncze zapytanie. Zmienne są inicjowane wewnątrz instrukcji SELECT.
  2. Obsługuje remisy zgodnie z opisem w pytaniu (kolejność alfabetyczna według nazwy).

Więc zostawię to tutaj na wypadek, gdyby mogło to komuś pomóc.


1
Mark: To działa dobrze dla nas. Dzięki za udostępnienie kolejnej dobrej alternatywy dla komplementu @ bluefeet's - bardzo docenione.
Yarin

+1. To zadziałało dla mnie. Naprawdę czysty i na temat odpowiedź. Czy możesz wyjaśnić, jak dokładnie to działa? Jaka logika się za tym kryje?
Aditya Hajare

3
Niezłe rozwiązanie, ale wygląda na to, że nie działa w moim środowisku (MySQL 5.6), ponieważ klauzula order by jest stosowana po wybraniu, więc nie zwraca najwyższego wyniku, zobacz moje alternatywne rozwiązanie, aby rozwiązać ten problem
Laurent PELE

Podczas uruchamiania udało mi się usunąć JOIN (SELECT @prev := NULL, @rn := 0) AS vars. Rozumiem, że chodzi o zadeklarowanie pustych zmiennych, ale wydaje się to obce dla MySql.
Joseph Cho

1
Działa to świetnie w MySQL 5.7, ale byłoby wspaniale, gdyby ktoś mógł wyjaśnić, jak to działa
George B

41

Spróbuj tego:

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

PRÓBNY


6
tabaka pojawiająca się znikąd z najprostszym rozwiązaniem! Czy to jest bardziej eleganckie niż Ludo / Bill Karwin ? Czy mogę dostać jakiś komentarz
Yarin

Hm, nie jestem pewien, czy jest bardziej elegancki. Ale sądząc po głosowaniach, wydaje mi się, że bluefeet może mieć lepsze rozwiązanie.
snuffn

2
Jest z tym problem. Jeśli jest remis na drugim miejscu w grupie, zwracany jest tylko jeden najlepszy wynik. Zobacz zmodyfikowane demo
Yarin

2
Nie stanowi problemu, jeśli jest to pożądane. Możesz ustawić kolejność a.person.
Alberto Leal

nie, w moim przypadku nie działa, podobnie jak DEMO
Choix

31

Co powiesz na samodzielne łączenie:

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;

daje mi:

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

Bardzo zainspirowała mnie odpowiedź Billa Karwina na wybór 10 najlepszych rekordów w każdej kategorii

Używam również SQLite, ale to powinno działać na MySQL.

Inna sprawa: w powyższym dla wygody wymieniłem groupkolumnę na groupnamekolumnę.

Edycja :

Kontynuując komentarz OP dotyczący brakujących wyników remisu, zwiększyłem odpowiedź snuffina, aby pokazać wszystkie remisy. Oznacza to, że jeśli ostatnie są remisami, można zwrócić więcej niż 2 wiersze, jak pokazano poniżej:

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

daje mi:

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

@ Ludo - Właśnie zobaczyłem tę odpowiedź od Billa Karwina - dzięki za zastosowanie jej tutaj
Yarin

Co myślisz o odpowiedzi Snuffina? Próbuję porównać oba
Yarin

2
Jest z tym problem. Jeśli jest remis na drugim miejscu w grupie, zwracany jest tylko jeden najlepszy wynik - Zobacz demo
Yarin

1
@ Ludo - pierwotnym wymaganiem było, aby każda grupa zwracała dokładnie n wyników, a wszelkie remisy były rozstrzygane alfabetycznie
Yarin

Edycja uwzględniająca krawaty nie działa dla mnie. Dostaję ERROR 1242 (21000): Subquery returns more than 1 row, prawdopodobnie z powodu GROUP BY. Kiedy SELECT MINwykonuję tylko podzapytanie, generuje ono trzy wiersze: 34, 39, 112i tam okazuje się, że druga wartość powinna wynosić 36, a nie 39.
verbamour

12

Rozwiązanie Snuffin wydaje się dość powolne do wykonania, gdy masz wiele wierszy, a rozwiązania Mark Byers / Rick James i Bluefeet nie działają w moim środowisku (MySQL 5.6), ponieważ polecenie zlecenie jest stosowane po wykonaniu polecenia select, więc oto wariant rozwiązań Marc Byers / Rick James w celu rozwiązania tego problemu (z dodatkową zawartością wyboru):

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;

Próbowałem podobnego zapytania na tabeli mającej 5 milionów wierszy i zwraca wynik w mniej niż 3 sekundy


3
To jedyne zapytanie, które działa w moim środowisku. Dzięki!
herrherr

3
Dodaj LIMIT 9999999do dowolnej tabeli pochodnej z rozszerzeniem ORDER BY. Może to zapobiec ORDER BYignorowaniu.
Rick James

Wykonałem podobne zapytanie na tabeli zawierającej kilka tysięcy wierszy, a zwrócenie jednego wyniku zajęło 60 sekund, więc ... dzięki za post, to dla mnie początek. (ETA: do 5 sekund. Dobrze!)
Evan

10

Sprawdź to:

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


5
Człowieku, inni znaleźli znacznie prostsze rozwiązania ... Właśnie spędziłem nad tym jakieś 15 minut i byłem z siebie niesamowicie dumny, że wymyśliłem tak skomplikowane rozwiązanie. To jest do bani.
Travesty3

Musiałem znaleźć wewnętrzny numer wersji, który był o 1 mniejszy niż obecny - to dało mi odpowiedź, aby to zrobić: max(internal_version - 1)- więc mniej stresu :)
Jamie Strauss

8

Jeśli inne odpowiedzi nie są wystarczająco szybkie Wypróbuj ten kod :

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;

Wynik:

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

Spojrzałem na twoją witrynę - skąd mam uzyskać źródło danych dla populacji miast? TIA i rgs.
Vérace

maxmind.com/en/worldcities - przydaje mi się do eksperymentowania z wyszukiwaniem lat / lng , zapytaniami, partycjonowaniem itp. Jest wystarczająco duży, aby był interesujący, ale wystarczająco czytelny, aby rozpoznać odpowiedzi. Podzbiór kanadyjski jest przydatny w przypadku tego rodzaju pytań. (Mniej prowincji niż miast w USA).
Rick James,

2

Chciałem się tym podzielić, ponieważ spędziłem dużo czasu szukając łatwego sposobu na zaimplementowanie tego w programie Java, nad którym pracuję. To nie do końca daje wynik, którego szukasz, ale jest blisko. Funkcja o nazwie mysql GROUP_CONCAT()działała bardzo dobrze przy określaniu liczby wyników zwracanych w każdej grupie. Używanie LIMITlub inne fantazyjne sposoby próby zrobienia tego COUNTnie zadziałały dla mnie. Więc jeśli chcesz zaakceptować zmodyfikowane wyjście, jest to świetne rozwiązanie. Powiedzmy, że mam tabelę o nazwie „student” z identyfikatorami uczniów, ich płcią i gpa. Powiedzmy, że chcę uzyskać najwyższe 5 gpa dla każdej płci. Wtedy mogę napisać zapytanie w ten sposób

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

Zauważ, że parametr „5” mówi mu, ile wpisów ma zostać połączonych w każdym wierszu

A wynik wyglądałby jakoś

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

Możesz także zmienić ORDER BYzmienną i zamówić je w inny sposób. Więc gdybym miał wiek ucznia, mógłbym zamienić „gpa desc” na „age desc” i to zadziała! Możesz także dodać zmienne do grupy według instrukcji, aby uzyskać więcej kolumn w wynikach. To jest po prostu sposób, który uważam, że jest dość elastyczny i działa dobrze, jeśli nie przeszkadza ci tylko wyświetlanie wyników.


0

W SQL Server row_numer()jest potężna funkcja, którą można łatwo uzyskać, jak poniżej

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

Ponieważ 8.0 i 10.2 to GA, ta odpowiedź staje się rozsądna.
Rick James,

@RickJames co oznacza „bycie GA”? Funkcje okna ( dev.mysql.com/doc/refman/8.0/en/window-functions.html ) bardzo dobrze rozwiązały mój problem.
iedmrc

1
@iedmrc - „GA” oznacza „ogólnie dostępne”. Jest to mowa techniczna, która oznacza „gotowy na najlepszy czas” lub „wydany”. Są w trakcie opracowywania wersji i będą się skupiać na błędzie, który przegapili. To łącze omawia implementację MySQL 8.0, która może być inna niż implementacja MariaDB 10.2.
Rick James

-1

W MySQL jest naprawdę fajna odpowiedź na ten problem - jak uzyskać N wierszy w każdej grupie

Na podstawie rozwiązania w odnośniku, do którego się odwołuje, Twoje zapytanie wyglądałoby tak:

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;

gdzie njest top ni your_tableto nazwa twojego stołu.

Myślę, że wyjaśnienie w dokumencie jest naprawdę jasne. Dla szybkiego odniesienia skopiuję i wkleję tutaj:

Obecnie MySQL nie obsługuje funkcji ROW_NUMBER (), która może przypisać numer kolejny w grupie, ale jako obejście możemy użyć zmiennych sesji MySQL.

Te zmienne nie wymagają deklaracji i mogą być używane w zapytaniu do wykonywania obliczeń i przechowywania wyników pośrednich.

@current_country: = country Ten kod jest wykonywany dla każdego wiersza i przechowuje wartość kolumny country w zmiennej @current_country.

@country_rank: = IF (@current_country = country, @current_rank + 1, 1) W tym kodzie, jeśli @current_country jest takie samo, zwiększamy rangę, w przeciwnym razie ustawiamy ją na 1. Dla pierwszego wiersza @current_country ma wartość NULL, więc ranga jest również ustawiony na 1.

Aby uzyskać poprawny ranking, potrzebujemy ORDER BY kraj, ludność DESC


Cóż, jest to zasada stosowana w rozwiązaniach Marca Byersa, Ricka Jamesa i moich.
Laurent PELE

Trudno powiedzieć, który post (przepełnienie stosu lub SQLlines) był pierwszy
Laurent PELE

@LaurentPELE - Mój został opublikowany w lutym 2015 r. Nie widzę znacznika czasu ani nazwy na SQLlines. Blogi MySQL istnieją wystarczająco długo, że niektóre z nich są nieaktualne i powinny zostać usunięte - ludzie cytują błędne informacje.
Rick James,
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.