SQL-Problem_Group und Ranking

[charlytango]

Hi,

ich habe ein SQL-Problem bei einer Abfrage.

Ansatz: Zwei Gruppen von Teilnehmern erzielen bei einem Spiel Punkte.
Ich möchte eine Rangliste ausgeben welche pro Gruppe die Punkte summiert und die erzielten Plätze numeriert.

Falls das jemand ausprobieren mag habe ich am Ende des Posts den Code zur Erzeugung der Tabelle samt Daten reingestellt.

folgendes SELECT erzeugt schonmal eine richtige Reihung

Code: Alles auswählen

SELECT 
gruppe,
teilnehmer,
sum(punkte)
FROM temp
GROUP BY teilnehmer,Gruppe
ORDER BY gruppe,punkte

nämlich
gruppe;teilnehmer;sum(punkte)
A;Müller;20
A;Meier;100
A;Huber;200
B;Wank;30
B;Weinert;300
B;Hauer;750
B;Roland;900

und dieses SELECT

Code: Alles auswählen

SELECT 
RANK() OVER (PARTITION BY gruppe ORDER BY punkte asc) AS rank,
gruppe,
teilnehmer,
sum(punkte)
FROM temp
GROUP BY teilnehmer,Gruppe
ORDER BY gruppe,punkte

erzeugt das gewünschte Ergebnis

rank;gruppe;teilnehmer;sum(punkte)
1;A;Müller;20
2;A;Meier;100
3;A;Huber;200
1;B;Wank;30
2;B;Weinert;300
3;B;Hauer;750
4;B;Roland;900

Leider ist das Zielsystem etwas älter und nicht 10.4.18-MariaDB wie auf meiner Entwicklungsmaschine. Die RANK Funktion wurde erst 10.2 eingeführt.
https://mariadb.com/kb/en/rank/

Frage: Wie löst man das ranking-Problem mit einer DB VOR Version 10.2 ?

PS: Bitte keine Empfehlungen bezüglich Update am Zielsystem, hab ich schon geklärt



Code zum Erzeugen der temp-Tabelle

Code: Alles auswählen

CREATE TABLE IF NOT EXISTS `temp` (
  `tempid` int(11) NOT NULL AUTO_INCREMENT,
  `teilnehmer` varchar(50) NOT NULL DEFAULT '0',
  `gruppe` varchar(50) DEFAULT NULL,
  `punkte` int(11) DEFAULT NULL,
  PRIMARY KEY (`tempid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

-- Exportiere Daten aus Tabelle test.temp: ~7 rows (ungefähr)
DELETE FROM `temp`;
/*!40000 ALTER TABLE `temp` DISABLE KEYS */;
INSERT INTO `temp` (`tempid`, `teilnehmer`, `gruppe`, `punkte`) VALUES
	(1, 'Meier', 'A', 100),
	(2, 'Müller', 'A', 20),
	(3, 'Huber', 'A', 200),
	(4, 'Hauer', 'B', 750),
	(5, 'Weinert', 'B', 300),
	(6, 'Roland', 'B', 900),
	(7, 'Wank', 'B', 30);

[af0815]

Ich bin die nächste Zeit nicht am PC. Aber es soweit ich mich erinnere über einen sogenannten selfjoin. Daher, man macht die Abfrage ein zweites Mal und joined die beide über ein subselect. Daher einmal für das Ranking und ein zweites mal für die richtigen Daten.

[Epcop]

Ist es notwendig, das über die DB lösen zu wollen?

Wenn es nur um das Ranking geht, würde ich das bei der Ausgabe mache. Sinngemäß in etwa so:

Code: Alles auswählen

aktueller_db_datensatz.prior;
letzter_datensatz := GRUPPE;
aktueller_db_datensatz.next;
z := 1;

if GRUPPE = letzter_datensatz then begin 
  z := z+1;
end else begin 
  z := 1;
end;

z ist dann jeweils das Ranking

[charlytango]

Ist es notwendig, das über die DB lösen zu wollen?

Natürlich kann ich das lösen indem ich durch Zwischenergebnisse durchsteppe, nur wozu hab ich eine SQL-DB?

Und diese Beispiel-Abfrage ist nur ein Zwischenergebnis um sie mittels CASE

Code: Alles auswählen

CASE WHEN rank=1 THEN ......... ELSE NULL END AS nameWX
CASE WHEN rank=2 THEN ......... ELSE NULL END AS nameWX

etc weiter zu verarbeiten.

Nachdem mich nur das Endergebnis interessiert wäre eine Lösung per SQL um Klassen eleganter als ich das per Code machen könnte.
Also JA - die Herausforderung ist eine SQL-Variante zu finden.
Jetzt hab ich schon einen Ferrari (=DB) und dann mit angezogener Handbremse im ersten Gang zu fahren macht wenig Sinn

[charlytango]

Nur falls jmd etwas ähnliches sucht, hier eine mögliche Lösung

Code: Alles auswählen

SELECT t1.gruppe, t1.teilnehmer, sum(t1.punkte), COUNT(*) AS row_number
FROM temp t1
JOIN temp t2 ON t2.gruppe = t1.gruppe AND t2.punkte <= t1.punkte
GROUP BY t1.gruppe, t1.punkte, t1.teilnehmer
ORDER BY t1.gruppe, row_number

[Eb]

Coole Lösung.

Und mit 'max' statt 'sum' würden auch noch die Punkte stimmen:

Code: Alles auswählen

SELECT t1.gruppe, t1.teilnehmer, max(t1.punkte), COUNT(*) AS row_number
FROM temp t1
JOIN temp t2 ON t2.gruppe = t1.gruppe AND t2.punkte <= t1.punkte
GROUP BY t1.gruppe, t1.punkte, t1.teilnehmer
ORDER BY t1.gruppe, row_number