Gruppieren: So einfach nicht löstbar?

M

mrGreen

Benutzer
Beiträge
5
Servus zusammen, ich bin neu hier und kann etwas SQL. Die Beträge hier und das Forums ingesamt gefallen mir sehr gut.
Ich habe ein Problem und bin gespannt, ob mir die Forumsgemeinde helfen kann.
Ich speichere in einer DB Spielstände von mehreren Spielern, die mehrere verschiedene Spiele spielen können. Das rel. DB-Modell ist im Bild zu sehen.
Nun möchte ich von allen Spielen nur (!) die drei besten Spieler angezeigt bekommen. Daran scheitere ich leider. Kann mir jemand helfen?

Meine bisherigen Ansätze bringen ALLE Spieler je Spiel (nicht nur die drei besten je Spiel):
a)
SELECT g.name, p.nickname, erg.punkte
FROM tblSpiel AS g, tblSpieler AS p, tblSpielerSpiel AS erg
WHERE g.id = erg.idSpiel AND p.id = erg.idSpieler
ORDER BY UPPER(g.name) ASC, punkte DESC

b)
SELECT g.name, s.nickname, erg.punkte
FROM tblSpiel AS g
JOIN tblSpielerSpiel AS erg ON g.id = erg.idSpiel
JOIN tblSpieler AS s ON s.id = erg.idSpieler
ORDER BY UPPER(g.name) ASC, erg.punkte DESC

db_Aufgabe10_Bild.jpg
 
Werbung:
Dazu kannst Du die RANK() - Frnktion nutzen, Demo:

Code: 
test=# create table spieler(id serial primary key, name text);
CREATE TABLE
test=*# create table spiel(id serial primary key, spieler int references spieler, punkte int);
CREATE TABLE
test=*# insert into spieler (name) select 'Spieler ' || s::text from generate_series(1,10) s;
INSERT 0 10
test=*# insert into spiel(spieler, punkte) select 1+(random()*9), (random()*20)::int from generate_series(1,30) s;
INSERT 0 30
test=*# select * from spieler;
 id |    name   
----+------------
  1 | Spieler 1
  2 | Spieler 2
  3 | Spieler 3
  4 | Spieler 4
  5 | Spieler 5
  6 | Spieler 6
  7 | Spieler 7
  8 | Spieler 8
  9 | Spieler 9
 10 | Spieler 10
(10 rows)

test=*# select * from spiel;
 id | spieler | punkte
----+---------+--------
  1 |       7 |     19
  2 |       8 |     13
  3 |       9 |     10
  4 |       3 |     10
  5 |       4 |     20
  6 |       9 |     17
  7 |       4 |     13
  8 |       8 |      5
  9 |       1 |      7
 10 |       7 |     13
 11 |       4 |     11
 12 |       4 |     13
 13 |       2 |     19
 14 |       4 |     14
 15 |       1 |      2
 16 |       2 |      8
 17 |       2 |      8
 18 |       2 |     17
 19 |       9 |      2
 20 |       1 |     11
 21 |       5 |      4
 22 |       4 |      1
 23 |       1 |     20
 24 |       8 |     16
 25 |       3 |     15
 26 |       7 |      1
 27 |       3 |     16
 28 |       7 |      1
 29 |       4 |      3
 30 |       9 |     20
(30 rows)
test=*# with t as (select spieler, punkte, rank() over (partition by spieler order by punkte desc) from spiel) select * from t;
 spieler | punkte | rank
---------+--------+------
       1 |     20 |    1
       1 |     11 |    2
       1 |      7 |    3
       1 |      2 |    4
       2 |     19 |    1
       2 |     17 |    2
       2 |      8 |    3
       2 |      8 |    3
       3 |     16 |    1
       3 |     15 |    2
       3 |     10 |    3
       4 |     20 |    1
       4 |     14 |    2
       4 |     13 |    3
       4 |     13 |    3
       4 |     11 |    5
       4 |      3 |    6
       4 |      1 |    7
       5 |      4 |    1
       7 |     19 |    1
       7 |     13 |    2
       7 |      1 |    3
       7 |      1 |    3
       8 |     16 |    1
       8 |     13 |    2
       8 |      5 |    3
       9 |     20 |    1
       9 |     17 |    2
       9 |     10 |    3
       9 |      2 |    4
(30 rows)

test=*# with t as (select spieler, punkte, rank() over (partition by spieler order by punkte desc) from spiel) select * from t where rank <= 3;
 spieler | punkte | rank
---------+--------+------
       1 |     20 |    1
       1 |     11 |    2
       1 |      7 |    3
       2 |     19 |    1
       2 |     17 |    2
       2 |      8 |    3
       2 |      8 |    3
       3 |     16 |    1
       3 |     15 |    2
       3 |     10 |    3
       4 |     20 |    1
       4 |     14 |    2
       4 |     13 |    3
       4 |     13 |    3
       5 |      4 |    1
       7 |     19 |    1
       7 |     13 |    2
       7 |      1 |    3
       7 |      1 |    3
       8 |     16 |    1
       8 |     13 |    2
       8 |      5 |    3
       9 |     20 |    1
       9 |     17 |    2
       9 |     10 |    3
(25 rows)

test=*#
 
Ich würde dafür Window Functions verwenden:

Code: 
select sp.name as spiel, s.nickname, erg.punkte
from tblspiel sp
  join (
      select idspiel, idspieler, punkte, dense_rank() over (partition by idspiel, idspieler order by punkte desc) as rnk
      from tblspielerspiel
  ) erg on erg.idspieler = sp.id and erg.rnk <= 3
  join tblspieler s on s.id = erg.idspieler;

Übrigens: die Namenskonvention allen Tabellen ein "tbl" Präfix zu geben ist ein Anachronismus der überhaupt keine Vorteile hat. Wenn Du Dateinamen vergibst, fangen die auch alle mit "file" an? Oder Klassen beim Programmieren mit "Class"? Kannst Dir ja mal das hier durchlesen:
Is adding the ‘tbl’ prefix to table names really a problem?
 
Alternative Lösung mittels LATERAL JOIN:

Code: 
test=*# select s.name, x.punkte from spieler s left join lateral (select id, punkte from spiel where spieler = s.id order by punkte desc limit 3) x on true;
    name    | punkte
------------+--------
 Spieler 1  |     20
 Spieler 1  |     11
 Spieler 1  |      7
 Spieler 2  |     19
 Spieler 2  |     17
 Spieler 2  |      8
 Spieler 3  |     16
 Spieler 3  |     15
 Spieler 3  |     10
 Spieler 4  |     20
 Spieler 4  |     14
 Spieler 4  |     13
 Spieler 5  |      4
 Spieler 6  |       
 Spieler 7  |     19
 Spieler 7  |     13
 Spieler 7  |      1
 Spieler 8  |     16
 Spieler 8  |     13
 Spieler 8  |      5
 Spieler 9  |     20
 Spieler 9  |     17
 Spieler 9  |     10
 Spieler 10 |       
(24 rows)

test=*#

Möglicherweise kann MySQL keine dieser Lösungen.
 
So, nun hast 2 Lösungen von 2 Leuten. Die Lösung mittels rank() bzw. dense_rank() hat den kleinen Nachteil, daß erst einmal für alle Datensätze diese Funktion aufgerufen wird. Stell Dir vor, Du hast hundertausende Datensätze je Spieler. Das ist dann ineffektiv, die Lösung via LATERAL JOIN ist da besser, die kann auch Indexe auf der Punkte-Spalte nutzen.
 
Zuletzt bearbeitet:
Wow, zwei Stunden offline und einige Antworten. Ist ein prima Forum :)

In der Tat hat akretschmer. Ich habe im falschen Bereich gepostet. Sorry, bin ja noch grün hinter den Ohren.

Es soll auf eine SQLite-DB zugegriffen werden. Kann ein Admin dieses Thema entsprechend verschieben?

Ansonsten Danke für die Antworten und Hinweise. Ich werde mich gleich mal durcharbeiten...
 
Ich glaube, ich habe mich bei meiner Fragestellung zu ungeschickt ausgedrückt. Es gibt verschiedene Spiele, die jeder Spieler spielen kann. Eine Abfrage soll mit dem oben genannten Tabellen beispielsweise ergeben:

Code: 
Spiel1 bigJim      1300
Spiel1 Darth Vader 100
Spiel2 Darth Vader 223
Spiel2 bigJim      200
Spiel2 Joe Cool    123
Spiel3 Joe Cool    223
Spiel3 littleJoe   2
Spiel3 bigJIm      1
Spiel4 bigJIm      12

Dabei ist auch ersichtlich, dass Spiel1 bisher nun von 2 Spielern gespielt wurde und Spiel4 von nur einem.
Mal sehen, was ich mit dem obigen Hinweisen daraus machen kann, wohl gemerkt aber für SQLite.
 
Zuletzt bearbeitet:
Werbung:
Zurück
Oben