wake-up-neo.com

Erhalten Sie Top-n-Datensätze für jede Gruppe gruppierter Ergebnisse

Das folgende Beispiel ist das einfachste, obwohl jede Lösung skalierbar sein sollte, unabhängig davon, wie viele n Top-Ergebnisse benötigt werden:

Wenn Sie eine Tabelle wie die folgende mit Personen-, Gruppen- und Altersspalten angeben, wie würden Sie die 2 ältesten Personen in jeder Gruppe erhalten? (Verbindungen innerhalb von Gruppen sollten nicht zu mehr Ergebnissen führen, sondern die ersten 2 in alphabetischer Reihenfolge)

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

Gewünschte Ergebnismenge:

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

ANMERKUNG: Diese Frage baut auf einer früheren Frage auf - Erhalte Datensätze mit maximalem Wert für jede Gruppe von gruppierten SQL-Ergebnissen - um eine einzelne oberste Zeile von jeder Gruppe zu erhalten, und die eine großartige MySQL- Spezifische Antwort von @Bohemian:

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

Würde gerne in der Lage sein, dies abzubauen, obwohl ich nicht verstehe, wie.

123
Yarin

Hier ist eine Möglichkeit, dies mit UNION ALL zu tun (Siehe SQL Fiddle mit Demo ). Dies funktioniert mit zwei Gruppen. Wenn Sie mehr als zwei Gruppen haben, müssen Sie die group-Nummer angeben und Abfragen für jede group hinzufügen:

(
  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
)

Es gibt verschiedene Möglichkeiten, dies zu tun. In diesem Artikel finden Sie die beste Route für Ihre Situation:

http://www.xaprb.com/blog/2006/12/07/wie-nach-wahl-der-ersteLastmax-reihe-per-gruppe-in-sql/

Bearbeiten:

Dies kann auch für Sie funktionieren, da für jeden Datensatz eine Zeilennummer generiert wird. Bei Verwendung eines Beispiels aus dem obigen Link werden nur die Datensätze mit einer Zeilennummer von weniger oder gleich 2 zurückgegeben:

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;

Siehe Demo

81
Taryn

In anderen Datenbanken können Sie dies mit ROW_NUMBER tun. MySQL unterstützt ROW_NUMBER nicht, aber Sie können Variablen verwenden, um es zu emulieren:

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

Sehen Sie es online arbeiten: sqlfiddle


Edit Ich habe gerade bemerkt, dass Bluefeet eine sehr ähnliche Antwort gepostet hat: +1 für ihn. Diese Antwort hat jedoch zwei kleine Vorteile:

  1. Es ist eine einzelne Abfrage. Die Variablen werden in der SELECT-Anweisung initialisiert.
  2. Es behandelt Krawatten wie in der Frage beschrieben (alphabetische Reihenfolge nach Name).

Also lass ich es hier, falls es jemandem helfen kann.

52
Mark Byers

Versuche dies:

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

34
snuffn

Wie wäre es mit der Selbstverbindung?

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;

gibt mir:

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

Die Antwort von Bill Karwin inspirierte mich sehr dazu, die Top-10-Datensätze für jede Kategorie auszuwählen

Ich benutze auch SQLite, aber das sollte mit MySQL funktionieren.

Eine andere Sache: Im obigen Abschnitt habe ich der Einfachheit halber die Spalte group durch eine Spalte groupname ersetzt.

Bearbeiten:

Im Anschluss an den Kommentar des OPs zu fehlenden Gleichstandergebnissen, erhöhte ich die Antwort von Snuffin, um alle Gleichstände zu zeigen. Das bedeutet, dass, wenn die Letzten Unentschieden sind, mehr als 2 Zeilen zurückgegeben werden können, wie unten gezeigt:

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

gibt mir:

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

Überprüfen Sie dies heraus:

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-Geige: http://sqlfiddle.com/#!2/cdbb6/15

10
Travesty3

Die Snuffin-Lösung scheint ziemlich langsam auszuführen, wenn Sie viele Zeilen haben und Mark Byers/Rick James- und Bluefeet-Lösungen in meiner Umgebung (MySQL 5.6) nicht funktionieren, da order by nach der Ausführung von select angewendet wird. Hier eine Variante von Marc Byers/Rick James Lösungen, um dieses Problem zu beheben (mit einer zusätzlichen Auswahl von Schablonen):

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;

Ich habe eine ähnliche Abfrage für eine Tabelle mit 5 Millionen Zeilen versucht, und das Ergebnis wird in weniger als 3 Sekunden zurückgegeben

7
Laurent PELE

Wenn die anderen Antworten nicht schnell genug sind, geben Sie diesen Code einen Versuch:

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;

Ausgabe:

+---------------------------+------+------------------+------------+
| 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 | ...
5
Rick James

Ich wollte dies teilen, weil ich lange nach einem einfachen Weg gesucht habe, dies in einem Java-Programm zu implementieren, an dem ich gerade arbeite. Die Ausgabe, die Sie suchen, ist nicht ganz genau, aber es ist nahe. Die Funktion in mysql mit dem Namen GROUP_CONCAT() funktionierte sehr gut, um anzugeben, wie viele Ergebnisse in jeder Gruppe zurückgegeben werden sollen. Die Verwendung von LIMIT oder einer der anderen Möglichkeiten, dies mit COUNT zu versuchen, hat für mich nicht funktioniert. Wenn Sie also bereit sind, eine modifizierte Ausgabe zu akzeptieren, ist dies eine großartige Lösung. Nehmen wir an, ich habe eine Tabelle namens "student" mit Studenten-IDs, deren Geschlecht und gpa. Nehmen wir an, ich möchte für jedes Geschlecht 5 GPAs anführen. Dann kann ich die Anfrage so schreiben

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

Beachten Sie, dass der Parameter '5' angibt, wie viele Einträge in jeder Zeile verkettet werden sollen

Und die Ausgabe würde ungefähr so ​​aussehen 

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

Sie können die Variable ORDER BY auch ändern und sie auf andere Weise anordnen. Wenn ich also das Alter des Schülers hätte, könnte ich 'gpa desc' durch 'age desc' ersetzen und es wird funktionieren! Sie können der group-by-Anweisung auch Variablen hinzufügen, um mehr Spalten in der Ausgabe zu erhalten. Dies ist also nur ein Weg, den ich gefunden habe, der ziemlich flexibel ist und gut funktioniert, wenn Sie nur mit den Ergebnissen zufrieden sind. 

2
Jon Bown

In SQL Server ist row_numer() eine leistungsstarke Funktion, mit der Sie das Ergebnis wie folgt abrufen können 

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