SQL-Problem_Group und Ranking

Für Themen zu Datenbanken und Zugriff auf diese. Auch für Datenbankkomponenten.
Antworten
charlytango
Beiträge: 843
Registriert: Sa 12. Sep 2015, 12:10
OS, Lazarus, FPC: Laz stable (2.2.6, 3.x)
CPU-Target: Win 32/64, Linux64
Wohnort: Wien

SQL-Problem_Group und Ranking

Beitrag von 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);

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6198
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: SQL-Problem_Group und Ranking

Beitrag von 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.
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

Epcop
Beiträge: 140
Registriert: Di 29. Mai 2012, 09:36

Re: SQL-Problem_Group und Ranking

Beitrag von 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
Beiträge: 843
Registriert: Sa 12. Sep 2015, 12:10
OS, Lazarus, FPC: Laz stable (2.2.6, 3.x)
CPU-Target: Win 32/64, Linux64
Wohnort: Wien

Re: SQL-Problem_Group und Ranking

Beitrag von charlytango »

Epcop hat geschrieben:
Sa 6. Aug 2022, 10:54
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
Beiträge: 843
Registriert: Sa 12. Sep 2015, 12:10
OS, Lazarus, FPC: Laz stable (2.2.6, 3.x)
CPU-Target: Win 32/64, Linux64
Wohnort: Wien

Re: SQL-Problem_Group und Ranking

Beitrag von 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
Lazarusforum e. V.
Beiträge: 238
Registriert: Di 5. Feb 2008, 15:32
OS, Lazarus, FPC: Linux Mint - Laz 2.2.0
CPU-Target: 64Bit
Wohnort: Stuttgart

Re: SQL-Problem_Group und Ranking

Beitrag von Eb »

Coole Lösung.

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

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

Antworten