Frage zur SQL-Abfrage

[Maik20]

Hallo,

ich steht vor einem kleinem Problem. Ich hoffe Ihr könnte mir dazu ein paar Tipps geben.

Meine Tabellenstruktur ist wie folgt:

Tabellenname: Tabelle
----------------------------------------------
FACH | ZEIT | SPALTE | WERT
----------------------------------------------

Die Felder FACH, ZEIT und SPALTE bilden einen Index (nicht Primär!), da die Einträge mehrfach vorkommen können.
Das Feld SPALTE beinhaltet Informationen in welcher Spalte die Werte dargestellt werden sollen. Das Endgültige Ergebnis soll wie folgt aussehen:

----------------------------------------------------------------
FACH | WERT (Spalte 1) | WERT (Spalte 2)
----------------------------------------------------------------

Die Tabelle beinhaltet ca. 270.000 Einträge. Ich möchte allerdings nur die ersten 20 Einträge sortiert nach FACH anzeigen.

Folgende Ideen hatte ich, die ich leider verwerfen musste.

Variante 1)
SELECT SUM(WERT) FROM Tabelle GROUP BY FACH ORDER BY FACH LIMIT 0,20
=> Geht nicht: da ich dann nicht 20 Werte aus Spalte 1 und 20 Werte aus Spalte 2 bekomme sondern nur die ersten 20 Werte in der Tabelle

Variante 2)
1) SELECT SUM(WERT) FROM Tabelle WHERE Spalte=1 GROUP BY FACH ORDER BY FACH LIMIT 0,20
2) SELECT SUM(WERT) FROM Tabelle WHERE Spalte=2 GROUP BY FACH ORDER BY FACH LIMIT 0,20
=> Geht nicht: Da die 20 Werte aus Spalte 2 ggf. ganz andere Fächer sind die nicht in Spalte 1 sind.

Kann mir jemand helfen das Problem zu lösen? Das Problem ist auch das der Eintrag FACH für eine Spalte nicht eindeutig sind, sondern das Fach Mathe z.B. mehrmals in Spalte 1 oder 2 vorkommen kann.

Hier nochmal eine Beispieltabelle und das Ergebnis:

FACH, ZEIT, SPALTE, WERT
Mathe, 1, 1, 10
Deutsch, 1, 1, 10
Deutsch, 1, 1, 10
Deutsch, 1, 2, 30
Mathe, 1, 2, 30
Mathe, 1, 2, 30

Ergebnis:
Deutsch, 20, 30
Mathe, 10, 60

 

[ishino]

Das funktioniert über eine Pivottabelle. Ich hab kein mySQL installiert, also hier mal ein (funktionierendes) Beispiel mit Oracle's SQL:

SELECT fach, 
 max(decode(spalte, 1, w)) wert1, 
 max(decode(spalte, 2, w)) wert2
FROM
(
 SELECT fach, spalte, sum(wert) w
 FROM faecher 
 GROUP BY fach, spalte
)
GROUP BY fach;

[EDIT] Die Begrenzung auf 20 Zeilen muß nachträglich gemacht werden. Bei mir wäre das wie folgt:

SELECT * 
FROM
(
 SELECT fach, 
  max(decode(spalte, 1, w)) wert1, 
  max(decode(spalte, 2, w)) wert2
 FROM
 (
  SELECT fach, spalte, sum(wert) w
  FROM faecher 
  GROUP BY fach, spalte
 )
 GROUP BY fach
) 
WHERE ROWNUM <=20;

 

[Maik20]

Danke für den Tipp.

Meines wissens nach kann mySQL keine Pivottabellen. Ich geh aber gleich mal googeln.

 

[ishino]

Das kann Oracle auch erst ab 11g. Das Beispiel von mir ist halt eine Möglichkeit, Pivotisierung selbst zu bauen. Im Grunde genommen kannst Du das so wie es ist übernehmen, lediglich das DECODE wird in mySQL wohl nicht vorhanden sein oder anders heißen (oder eben doch da sein).

[EDIT] mySQL hat, genauso wie Oracle, eine CASE Klausel. Damit läßt sich das ganz einfach nachbauen. DECODE(spalte, 1, w) ist das gleiche wie CASE spalte WHEN 1 THEN w.

 

[Maik20]

Danke für den Tipp,

das klappt (fast) perfekt. Folgende Abfrage zeigt mir die Tabelle allerdings steht in der Spalte Wert2 nur "0" für alle FACH. Führe ich nur die innere Subquery aus sehe ich das ich in Spalte 1 und 2 Werte habe. Vielleicht hast du eine Idee?

SELECT FACH, 
(CASE WHEN BLOCK=1 THEN w ELSE 0 END) AS wert1, 
(CASE WHEN BLOCK=2 THEN w ELSE 0 END) AS wert2
FROM (
SELECT FACH, SPALTE, SUM( WERT ) AS w
FROM faecher
GROUP BY SPALTE, FACH
) AS T1
GROUP BY FACH;

 

[ishino]

Funktioniert die Query so überhaupt? BLOCK taucht doch in der Subquery gar nicht auf. Wo soll das herkommen?

 

[Maik20]

Hast recht, war ein Übertragungsfehler.

In der Subquery steht auch der Block drin.

Nachdem ich die Abfrage jetzt fertig habe stehe ich vor einem anderen Problem. Die Abfrage dauert (auf ca. 450.000 Datensätzen) etwas mehr als 20 Sekunden. Das ist mir etwas zu lang. Kann man daran noch was optimieren?

 

[ishino]

Wie gesagt, ich kenne mich mit mySQL nicht aus und weiß deshalb auch nicht, was für Ausführungszeiten man bei sowas erwarten darf. Grundsätzlich sollte das mit entsprechenden Indizes nicht allzu lange dauern. Ein Blick auf den Ausführungsplan sollte weiterhelfen.