SQL Abfrage auf gleiche Tabelle?

Hallo,

ich steht vor einem 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 "SUM(WERT)" sortiert und gegroupt 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
=3D> 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) mit zwei SQL Abfragen
1) SELECT SUM(WERT) FROM Tabelle WHERE Spalte=3D1 GROUP BY FACH ORDER BY
FACH LIMIT 0,20
2) SELECT SUM(WERT) FROM Tabelle WHERE Spalte=3D2 GROUP BY FACH ORDER BY
FACH LIMIT 0,20
=3D> 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
Sport, 1, 1, 10
Deutsch, 1, 2, 30
Mathe, 1, 2, 30
Mathe, 1, 2, 30
Englisch, 1, 2, 30


Ergebnis:
Fach, SUM(Wert) (Spalte 1), SUM(Wert) (Spalte 2)
Deutsch, 20, 30
Mathe, 10, 60
Sport, 10, 0
(Englisch ist nicht im Ergebnis, da Englisch nicht in Spalte 1 ist)

Re: SQL Abfrage auf gleiche Tabelle?

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: SQL Abfrage auf gleiche Tabelle?

Danke Andreas,

mein Realname ist Maik ;-)

Jetzt habe ich dazu nochmal eine grundsätzliche Frage nach der
Performance. Ich habe den Datenbankaufbau stark vereinfacht. Anstelle
der einen Spalte Wert gibt es etwa 20 Spalte die Werte beinhalten.
Würde bedeutet ich müsste für jedes Feld "Wert" entsprechend der
Anzahl der Spalten immer einen CASE erstellen. Richtig?

Ich dachte es gibt ggf. eine Lösung mittels JOIN etwa so:

SELECT SUM(SP1.WERT) as SP1_WERT, SUM(SP2.WERT) as SP2_WERT FROM
tabelle as SP1 join .... tabelle as SP2 group by ....

Wenn ja, was wäre performanter?

Wenn ich nach dem Feld FACH sortiere und gruppiere ist es dann
sinnvoll einen Index auf das Feld zu legen? Ich frage deshalb weil es
auch mehrere Felder "Fach1" bis "Fach10" gibt und ich grundsätzlich
auf jedem sortieren / gruppieren möchte.

Re: SQL Abfrage auf gleiche Tabelle?

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: SQL Abfrage auf gleiche Tabelle?

Hallo Andreas,

nein für einen Nachnamen hatten meine Eltern kein Geld mehr ;-)

Aber Scherz beiseite.

Du hast ein besseres Tabellendesign erwähnt, da ich noch recht neu mit
SQL hantiere kann es natürlich sein das man da auch noch was dran
drehen kann.
Ich gehe mal davon aus, das du darauf anspielst die Werte in einer
Tabelle als zwei Felder zu führen. Etwa so:

fach | zeit | spalte | wert1 | wert2
----------+------+--------+-------+--------

Ist mir auch schon in den Sinn gekommen. Damit ergeben sich nur zwei
Probleme.

1) Theoretisch gibt es unendlich viele Spalten. Praktisch vermutlich
an die 20 bis 30. Da es je Spalte 10 Wertefelder gibt bedeutet das
eine Tabellenbreite von 300 Spalten (praktisch). Ist eigentlich auch
handelbar.

2) Und das ist wichtiger. Jeder Wert hat in jeder Spalte eine
unterschiedliche Ausprägung. Um das deutlich zu machen nehm ich mal
ein anderes Beispiel:

wert_umsatz_filiale_1 wert_umsatz_filiale_2 plz_filiale_1
plz_filiale_2
-------------------------------+---------------------------- -
+-----------------+---------------

Ein Group nach PLZ der Filiale wäre in diesem Tabellendesign nicht
Möglich.

Oder hast du ein anderes Tabelledesign im Hinterkopf?

Bin für jeden Tipp dankbar.

Ciao

Maik

Re: SQL Abfrage auf gleiche Tabelle?

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: SQL Abfrage auf gleiche Tabelle?

Am 10.10.2007 14:06 schrieb Maik20:

> 1) Theoretisch gibt es unendlich viele Spalten.

Sicherlich nicht.


>Praktisch vermutlich
> an die 20 bis 30. Da es je Spalte 10 Wertefelder gibt bedeutet das
> eine Tabellenbreite von 300 Spalten (praktisch). Ist eigentlich auch
> handelbar.

Sicherlich nicht.
>
> 2) Und das ist wichtiger. Jeder Wert hat in jeder Spalte eine
> unterschiedliche Ausprägung. Um das deutlich zu machen nehm ich mal
> ein anderes Beispiel:
>
> wert_umsatz_filiale_1 wert_umsatz_filiale_2 plz_filiale_1
> plz_filiale_2

Spätestens wenn Du sowas siehst, weißt Du, dass Du Dein "Design"
wegwerfen und erstmal ein Buch oder wenigstens ein paar Seiten im Web
über "Normalisierung" lesen musst.


> -------------------------------+---------------------------- -
> +-----------------+---------------
>
> Ein Group nach PLZ der Filiale wäre in diesem Tabellendesign nicht
> Möglich.

Natürlich nicht.

>
> Oder hast du ein anderes Tabelledesign im Hinterkopf?

Andreas? Der dürfte die 3. NF im Kopf haben - und nicht nur hinten.

>
> Bin für jeden Tipp dankbar.
>

Von mir war das, solange Du auf einen Realname verzichtest, jedenfalls
der letzte.

--
Christian

Re: SQL Abfrage auf gleiche Tabelle?

Hallo Christian, Hallo Andreas,

tut mir leid, war mir nicht bewusst, das hier soviel Wert auf den
Realname gelegt wird. In anderen Foren geht es etwas unförmlicher zu.
Da ich über Google Groups poste habe ich leider keine Möglichkeit
(zumindest nicht gefunden) die "Von" Zeile anzupassen.

Aber für alle die es interessiert: Maik Thomson.

Zurück zu meinem Problem, wenn mir noch jemand helfen möchte:

>> Ein Group nach PLZ der Filiale wäre in diesem Tabellendesign nicht
>> Möglich.

>Natürlich nicht.

Bleiben wir einfach bei dem Beispiel mit den Filialen.
Dieses würde ich wie folgt aufbauen

------------------------------------------------------------ ---------------=
-------
FILIALE | ABTEILUNG | ZEIT | PLZ | UMSATZ | KOSTEN ...
------------------------------------------------------------ ---------------=
-------

Das Problem ist das bei diesem Tabellendesign mit ca. 300.000 Einträge
die folgende Abfrage ca. 20 Sekunden dauert:

SELECT ABTEILUNG, ZEIT, SUM(UMSATZ) AS SU, SUM(KOSTEN) AS SK, ....
FROM tabelle WHERE ZEIT>0 AND ZEIT<12 AND FILIALE=3D12 GROUP BY
ABTEILUNG ORDER BY ABTEILUNG LIMIT 0,20

und diese NICHT das gewünschte Ergebnis liefert. Ich möchte für die
Filiale 12 max. 20 Abteilungen anzeigen lassen aber jede dieser 20
Abteilungen über alle 12 Monate. Also in Excel eine klassische
Kreuztabelle. Das Ergebnis sollte am ende wie folgt dargestellt
werden:

ABTEILUNG | Umsatz Jan. | Umsatz Feb | Umsatz März | Umsatz April |
Umsatz Mai .....
1 | 10 | 10 | 10 | ...
2 | 10 | 10 | 10 | ...
3 | 10 | 10 | 10 | ...
4 | 10 | 10 | 10 | ...
5 | 10 | 10 | 10 | ...
...

Um die Anfrage zu optimieren habe ich einen Index wie folgt definiert:
Index1: FILIALE, ABTEILUNG, ZEIT

Jetzt könnte ggf. von euch der Vorschlag kommen, lass einfach das
LIMIT 0,20 weg, dann hätte ich alle Ausprägungen von Abteilung und
Zeit und würde diese auslesen und z.B. mit PHP die Tabelle
zusammenbauen. Das Problem ist es gibt in der Spalte Abteilung ca.
30.000 Ausprägungen (1 bis 29.500) für jede Abteilung gibt es pro
Monat einen Eintrag somit 29.500 x 12 Werte. Wenn ich also alle
Abteilungen auslesen muss um nur 20 anzuzeigen ist das nicht gerade
performant. Jetzt brauche ich aber nur die Werte der "ersten" 20
Abteilungen.

Ciao

Maik

Re: SQL Abfrage auf gleiche Tabelle?

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: SQL Abfrage auf gleiche Tabelle?

Am 10.10.2007 19:28 schrieb Maik20:
> Hallo Christian, Hallo Andreas,
>
> tut mir leid, war mir nicht bewusst, das hier soviel Wert auf den
> Realname gelegt wird. In anderen Foren geht es etwas unförmlicher zu.
> Da ich über Google Groups poste habe ich leider keine Möglichkeit
> (zumindest nicht gefunden) die "Von" Zeile anzupassen.
>
> Aber für alle die es interessiert: Maik Thomson.
>
> Zurück zu meinem Problem, wenn mir noch jemand helfen möchte:
>
>>> Ein Group nach PLZ der Filiale wäre in diesem Tabellendesign nicht
>>> Möglich.
>
>> Natürlich nicht.
>
> Bleiben wir einfach bei dem Beispiel mit den Filialen.
> Dieses würde ich wie folgt aufbauen
>
> ------------------------------------------------------------ ----------------------
> FILIALE | ABTEILUNG | ZEIT | PLZ | UMSATZ | KOSTEN ...
> ------------------------------------------------------------ ----------------------
>
> Das Problem ist das bei diesem Tabellendesign mit ca. 300.000 Einträge
> die folgende Abfrage ca. 20 Sekunden dauert:
>
> SELECT ABTEILUNG, ZEIT, SUM(UMSATZ) AS SU, SUM(KOSTEN) AS SK, ....
> FROM tabelle WHERE ZEIT>0 AND ZEIT<12 AND FILIALE=12 GROUP BY
> ABTEILUNG ORDER BY ABTEILUNG LIMIT 0,20
>
> und diese NICHT das gewünschte Ergebnis liefert. Ich möchte für die
> Filiale 12 max. 20 Abteilungen anzeigen lassen aber jede dieser 20
> Abteilungen über alle 12 Monate. Also in Excel eine klassische
> Kreuztabelle. Das Ergebnis sollte am ende wie folgt dargestellt
> werden:
>
> ABTEILUNG | Umsatz Jan. | Umsatz Feb | Umsatz März | Umsatz April |
> Umsatz Mai .....

Vielleicht Group by with Rollup?

> 1 | 10 | 10 | 10 | ...
> 2 | 10 | 10 | 10 | ...
> 3 | 10 | 10 | 10 | ...
> 4 | 10 | 10 | 10 | ...
> 5 | 10 | 10 | 10 | ...
> ...
>
> Um die Anfrage zu optimieren habe ich einen Index wie folgt definiert:
> Index1: FILIALE, ABTEILUNG, ZEIT
>
Und was sagt Explain zu Deiner Query? Benutzt es den Index?
> Jetzt könnte ggf. von euch der Vorschlag kommen, lass einfach das
> LIMIT 0,20 weg, dann hätte ich alle Ausprägungen von Abteilung und
> Zeit und würde diese auslesen und z.B. mit PHP die Tabelle
> zusammenbauen. Das Problem ist es gibt in der Spalte Abteilung ca.
> 30.000 Ausprägungen (1 bis 29.500)

Du meinst Du hast eine Filiale mit 29500 Abteilungen? Da würde ich ja
zu gerne wissen, wie das die schon vorhandenen Programme (es gibt doch
welche, oder wird diese Firma erst gegründet?) bewältigen und wie das
Management mit den 29500 Abteilungsleitern ... ah ich ahne es - das
ist Dussmann. Da gibt's fast für jedes Buch einen leitenden Angestellten.

> für jede Abteilung gibt es pro
> Monat einen Eintrag somit 29.500 x 12 Werte. Wenn ich also alle
> Abteilungen auslesen muss um nur 20 anzuzeigen ist das nicht gerade
> performant.

Mag sein - aber es ist auch nicht nachvollziehbar, warum Dich nur die
Umsätze der zufällig mit 1 bis 20 nummerierten Abteilungen interessieren.


--
Christian