SQL-Abfrage optimieren

Blue-Tiger

Hi! Sorry fuer den Nichtssagenden Titel, ich wusste keinen besseren Trotzdem hoff ich es findet sich wer, der mir bei meinem Problem weiterhilft

Ich hab folgende Tabelle:

sqlite> CREATE TABLE test(
   ...> id INTEGER PRIMARY KEY,
   ...> winner INTEGER,
   ...> loser INTEGER);

sqlite> SELECT * FROM test;
0|1|2
1|1|2
2|1|3
3|3|1
4|3|1
5|3|2
6|4|1

Und moechte gern eine Abfrage darauf fahren, die mir folgendes Ergebnis liefern soll:

Statistics for player 1:

ENEMY | WINS | DEFEATS
------+------+--------
  2   |   2  |   0
  3   |   1  |   2
  4   |   0  |   1

Leider weiss ich nicht, wie ich das in SQL loesen kann. Momentan mach ich das, indem ich folgende 2 Abfragen ausfuehre und die dann von meinem Programm zu einer einzigen Tabelle zusammenfasse:

sqlite> SELECT loser, COUNT(*) FROM test WHERE winner = 1 GROUP BY loser;
2|2
3|1

sqlite> SELECT winner, COUNT(*) FROM test WHERE looser = 1 GROUP BY winner
3|2
4|1

Allerdings moechte ich wenn moeglich die Arbeit von der Datenbank erledigen lassen. Deshalb meine Frage: ist es moeglich, eine SQL-Abfrage zu schreiben, welche das gewuenschte Ergebnis liefert? Wenn ja, wie?
(Benutztes Datenbanksystem ist SQLite 3)

Blue-Tiger

hmm... Mittlerweile hab ich eine Loesung gefunden. Da SQLite noch keine OUTER Joins unterstuetzt ist das Ganze ziemlich haesslich:

SELECT w.loser, w.wins, d.defeats
FROM
	(SELECT loser, COUNT(*) AS wins FROM test WHERE winner = 1 GROUP BY loser) w
	LEFT JOIN
	(SELECT winner, COUNT(*) AS defeats FROM test WHERE looser = 1 GROUP BY winner) d
	ON w.loser = d.winner
UNION
SELECT d.winner, w.wins, d.defeats
FROM
	(SELECT winner, COUNT(*) AS defeats FROM test WHERE loser = 1 GROUP BY winner) d
	LEFT JOIN
	(SELECT loser, COUNT(*) AS wins FROM test WHERE winner = 1 GROUP BY loser) w
	ON w.loser = d.winner

Gibts dafuer eine einfachere Loesung?

Blue-Tiger

Laut Profiler ist obige Abfrage eines der groessten Bottlenecks in meiner Applikation. Hat irgendwer einen Rat, wie man die Abfrage optimieren koennte?

Frank P.

So geht es beim SQL-Server. Falls MySql kein "CASE WHEN"-Konstrukt kennt, ersetze es durch ein MySql-Konstrukt (falls vorhanden) oder eine Unterabfrage. Wie es performancemäßig aussieht, musst du selber herausfinden.

SELECT enemy = CASE WHEN winner <> 1 THEN winner ELSE loser END,
       wins = ( SELECT COUNT(*) FROM test t2 WHERE winner = 1 AND loser = MAX(test.loser) ),
       defeats = ( SELECT COUNT(*) FROM test t2 WHERE winner = MAX(test.winner) AND loser = 1 )
FROM test 
WHERE winner = 1 
OR loser = 1
GROUP BY CASE WHEN winner <> 1 THEN winner ELSE loser END

Blue-Tiger

Danke fuer die Antwort. SQLite besitzt leider keine CASE-Anweisung. Und so wie ich deine Abfrage verstehe (hab kein SQL-Server zur Hand ums auszutesten) liefert deine Abfrage ja nur die Spieler mit den meisten Gewinnern/Verlieren (du verwendest ein MAX im WHERE der Unterabfrage), oder missinterpretiere ich das falsch?MAX(test.loser)

Airdamn

Weiß zwar nicht ob SQLite 3 das kann, aber kannst ja mal versuchen

select	enemy, sum(wins) as wins, sum(loses) as loses
from	(
	select	loser as enemy, count(*) as wins, 0 as loses
	from	test
	where	winner = 1
	group	by loser

	union all

	select	winner as enemy, 0 as wins, count(*) as loses
	from	test
	where	loser = 1
	group	by winner ) a
group	by enemy

Frank P.

Blue-Tiger schrieb:

Danke fuer die Antwort. SQLite besitzt leider keine CASE-Anweisung. Und so wie ich deine Abfrage verstehe (hab kein SQL-Server zur Hand ums auszutesten) liefert deine Abfrage ja nur die Spieler mit den meisten Gewinnern/Verlieren (du verwendest ein MAX im WHERE der Unterabfrage), oder missinterpretiere ich das falsch?MAX(test.loser)

Hmm..., hab das mit deinen Testdaten durchgeführt und das gewünschte Resultat erhalten.

EDIT: Stimmt, so gehts nicht...

Blue-Tiger

Airdamn schrieb:

Weiß zwar nicht ob SQLite 3 das kann, aber kannst ja mal versuchen

select	enemy, sum(wins) as wins, sum(loses) as loses
from	(
	select	loser as enemy, count(*) as wins, 0 as loses
	from	test
	where	winner = 1
	group	by loser
	
	union all
	
	select	winner as enemy, 0 as wins, count(*) as loses
	from	test
	where	loser = 1
	group	by winner ) a
group	by enemy

Thx! Supertipp Funktioniert wunderbar und brachte meinem Programm 10% mehr Geschwindigkeit Thx a lot