Highscore ermittlung

Fragen und Probleme rund um die Abfragespache SQL.

Highscore ermittlung

Beitragvon traxanos » Mi 26. Okt 2011, 09:10

Hallo,

ich habe folgende Tabelle mit folgenden Daten:

Code: Alles auswählen
CREATE TABLE hs(
  user_id integer NOT NULL,
  rounds integer NOT NULL,
  duration integer NOT NULL,
  CONSTRAINT pkey PRIMARY KEY (user_id, rounds, duration)
);
INSERT INTO hs (user_id, rounds, duration) VALUES (1, 4, 2000);
INSERT INTO hs (user_id, rounds, duration) VALUES (2, 4, 2200);
INSERT INTO hs (user_id, rounds, duration) VALUES (1, 5, 2100);
INSERT INTO hs (user_id, rounds, duration) VALUES (3, 6, 2500);
INSERT INTO hs (user_id, rounds, duration) VALUES (1, 5, 210);


Nun möchte ich das ganze nach round DESC, duration ASC, user_id ASC sortieren.
Und nun brauche ich immer den ersten Eintrag pro Pro Benutzer, da ja jeder Benutzer ja nur mit seinem besten Eintrag ins Ranking übernommen werden soll.
traxanos
Benutzer
 
Beiträge: 98
Registriert: Di 27. Mai 2008, 12:48

Beitragvon castorp » Mi 26. Okt 2011, 09:20

Code: Alles auswählen
select *
from (
  select user_id,
         rounds,
         duration,
         row_number() over (partition by user_id order by rounds DESC, duration ASC, user_id ASC) as rn
   from hs
) t
where rn = 1
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'
castorp
Erfahrener Benutzer
 
Beiträge: 673
Registriert: Sa 27. Jun 2009, 22:24

Beitragvon akretschmer » Mi 26. Okt 2011, 09:31

traxanos hat geschrieben:Hallo,

ich habe folgende Tabelle mit folgenden Daten:

Code: Alles auswählen
CREATE TABLE hs(
  user_id integer NOT NULL,
  rounds integer NOT NULL,
  duration integer NOT NULL,
  CONSTRAINT pkey PRIMARY KEY (user_id, rounds, duration)
);
INSERT INTO hs (user_id, rounds, duration) VALUES (1, 4, 2000);
INSERT INTO hs (user_id, rounds, duration) VALUES (2, 4, 2200);
INSERT INTO hs (user_id, rounds, duration) VALUES (1, 5, 2100);
INSERT INTO hs (user_id, rounds, duration) VALUES (3, 6, 2500);
INSERT INTO hs (user_id, rounds, duration) VALUES (1, 5, 210);


Nun möchte ich das ganze nach round DESC, duration ASC, user_id ASC sortieren.
Und nun brauche ich immer den ersten Eintrag pro Pro Benutzer, da ja jeder Benutzer ja nur mit seinem besten Eintrag ins Ranking übernommen werden soll.


Neben der Lösung von castorp geht auch in Versionen vor 8.4:

Code: Alles auswählen
select distinct on (user_id) user_id, rounds, duration from hs order by user_id, rounds desc, duration asc;


Ist möglicherweise bei großen Datenmengen schneller als die WINDOWING-Lösung.

Andreas
--
ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)

https://www.xing.com/profile/Andreas_Kretschmer7
Hosting-Provider mit PostgreSQL-Support gesucht? Mich fragen!
akretschmer
Erfahrener Benutzer
 
Beiträge: 5936
Registriert: So 29. Okt 2006, 14:35
Wohnort: Kaufbach, Sachsen, Germany, Germany

Beitragvon traxanos » Mi 26. Okt 2011, 09:41

Code: Alles auswählen
select distinct on (user_id) user_id, rounds, duration from hs order by user_id, rounds desc, duration asc;


das ist falsch, es müsste wenn so aus sehen:

Code: Alles auswählen
select distinct on (user_id) user_id, rounds, duration from hs order by rounds desc, duration asc, user_id;


das geht aber ja nicht weil das distinct das order des distinct zuerst erwartet.

das gleiche gilt für die windowing lösung.

ergebnis ist bei beiden:

Code: Alles auswählen
1;5;210;1
2;4;2200;1
3;6;2500;1


korrekt wäre:

Code: Alles auswählen
3;6;2500;1
 1;5;210;1
2;4;2200;1
traxanos
Benutzer
 
Beiträge: 98
Registriert: Di 27. Mai 2008, 12:48

Beitragvon traxanos » Mi 26. Okt 2011, 09:49

wobei man die window funktion noch sortieren kann.

Code: Alles auswählen
select *
from (
  select user_id,
         rounds,
         duration,
         row_number() over (partition by user_id order by rounds DESC, duration ASC, user_id ASC) as rn
   from hs
) t
where rn = 1 order by rounds DESC, duration ASC, user_id ASC


windowing hatte ich zu anfang auch versucht. mein fehler war aber, dass ich dannach auf der user_id distinct habe und dannach nach row_number sortiert habe in 2 subselects. damit hatte ich zwar die richtige sortierung zum user aber nicht die passende werte. aber die variante sieht jetzt sehr gut aus.
traxanos
Benutzer
 
Beiträge: 98
Registriert: Di 27. Mai 2008, 12:48


Zurück zu SQL

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 1 Gast