Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Gruppieren: So einfach nicht löstbar?

Dieses Thema im Forum "SQLite" wurde erstellt von mrGreen, 14 Februar 2020.

  1. mrGreen

    mrGreen Benutzer

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

    Dukel Datenbank-Guru

  3. akretschmer

    akretschmer Datenbank-Guru

    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=*#
    
     
  4. castorp

    castorp Datenbank-Guru

    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?
     
  5. akretschmer

    akretschmer Datenbank-Guru

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

    akretschmer Datenbank-Guru

    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: 14 Februar 2020
  7. mrGreen

    mrGreen Benutzer

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

    mrGreen Benutzer

    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: 14 Februar 2020
  9. akretschmer

    akretschmer Datenbank-Guru

    Wohl noch nicht gemerkt hast Du wohl, daß es ein Unterforum für SQLite gibt.
     
  10. mrGreen

    mrGreen Benutzer

    Doch, schau mal zwei Posts hoch?

    Verschieben ist doch besser als neu schreiben. Kann aber nur ein Admin, oder?
     
  11. mrGreen

    mrGreen Benutzer

    Wollte mal vorsichtig fragen, ob jemand eine Lösung mit SQLite weiß... :confused:
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden