Daten mit GROUP BY einschränken und mit HAVING auswählen

[Peter Mairhofer]

Hi,

Ich hab über eine komplexe Abfrage mit einigen JOINs folgende Ergebnismenge:

http://img232.imageshack.us/img232/2256/withoutgroupob4.png

Im Endeffekt sagt die Liste auf: Das nächste Element von 0014 ist 06166, voriges gibt es nicht.
Oder: Das Vorgängerelement von 0014anh2 ist 0014anh1 und das Nachfolgerelement ist 0014anh4.

Nur: Wie bringe ich die restlichen Datensätze raus?

Es müsste (so wie die Liste jetzt formatiert ist) immer genau die erste Zeile einer Gruppe übrig bleiben, das wäre das für mich gewünschte Ergebnis.

Nun hab ich schon einiges mit HAVING herumgespielt (z.B. "HAVING delta = MAX(delta)") aber ich brings nicht zammen :-(

Kann mir da wer weiterhelfen?

Vielen Dank im Vorraus!
lg,
Peter

PS: RDBMS ist mySQL5, es sollte aber nach Möglichkeit auch in SQLite3 funktionieren...
PPS: Die "delta"-Spalte stellt den Abstand zwischen prev_order und next_order dar, wobei eine spezielle NULL Behandlung eingefügt wurde. In MySQL:

(IF(ISNULL(prev.level_order),0,prev.level_order) - IF(ISNULL(next.level_order),0,next.level_order)) AS delta

[Vinzenz Mai]

Hallo,

Es müsste (so wie die Liste jetzt formatiert ist) immer genau die erste Zeile einer Gruppe übrig bleiben, das wäre das für mich gewünschte Ergebnis.

das sieht nach einer korrelierten Unterabfrage aus, siehe z.B:

</archiv/2006/7/t133015/#m861544>
</archiv/2008/1/t165377/#m1078290>

Freundliche Grüße

Vinzenz

[Peter Mairhofer]

Hallo,

Es müsste (so wie die Liste jetzt formatiert ist) immer genau die erste Zeile einer Gruppe übrig bleiben, das wäre das für mich gewünschte Ergebnis.

das sieht nach einer korrelierten Unterabfrage aus, siehe z.B:

</archiv/2006/7/t133015/#m861544>
</archiv/2008/1/t165377/#m1078290>

Vielen Dank für deine Antwort damit hätte ich es theoretisch geschafft! Nur nicht praktisch, da warte ich seit 30 Minuten oder so auf das Ergebnis, wobei die Tabelle nur 300 Einträge hat!

Die von mir genannte Abfrage ist leider komplizierter als die in deinen Links genannte. Die Abfrage, die die Daten in meinem Screenshot produziert sieht so aus (abgesehen von der delta-Spalte):

SELECT
 t.topic_textID,
 prev.topic_textID AS prev,
 prev.level_order AS prev_order,
 next.topic_textID AS next,
 next.level_order AS next_order,
FROM temp_topics AS t
LEFT JOIN temp_topics AS prev ON t.level = prev.level AND prev.level_order < t.level_order
LEFT JOIN temp_topics AS next ON t.level = next.level AND next.level_order > t.level_order
WHERE t.level != ''
ORDER BY topic_textID, prev_order DESC, next_order ASC

Was diese Abfrage tut ist einfach erklärt: Die Tabelle temp_topics ist so aufgebaut:

CREATE TABLE IF NOT EXISTS temp_topics(
 topic_textID VARCHAR(20) PRIMARY KEY,
 title VARCHAR(200),
 parent VARCHAR(20),
 level VARCHAR(20),
 level_order INTEGER,
 print_group VARCHAR(20)
);

Anmerkung: Das sind Rohdaten, deswgen VARCHAR. Doch genau aus diesen mag ich ein vernünfiges verkettetes Modell machen. Es existiert eine Tabelle mit Zuordnungen zwischen topic_textID und einem Integer-Wert die ich verwenden werde wenn alles geht. Zusätzlich gibt es auch einen INDEX über level und level_order, also die Spalten die abgefragt werden.

Die Daten sind also in Gruppen ("level") aufgeteilt und die Sortierung innerhalb eines levels ist durch "level_order" bestimme. Diese Daten möchte ich nun in eine "Adjazenz"-Darstellung bringen.

Wie du erkennen kannst, muss ich hier prev_level und next_level kombiniert abfragen. D.h. in die WHERE-Klausel muss bei mir die ganze Abfrage noch einmal :-(

SELECT
 t.topic_textID,
 prev.topic_textID AS prev,
 prev.level_order AS prev_order,
 next.topic_textID AS next,
 next.level_order AS next_order
FROM temp_topics AS t
LEFT JOIN temp_topics AS prev ON t.level = prev.level AND prev.level_order < t.level_order
LEFT JOIN temp_topics AS next ON t.level = next.level AND next.level_order > t.level_order
WHERE t.level != ''
AND prev.level_order = (
 SELECT MAX(prev_sub1.level_order)
 FROM temp_topics AS t_sub1
 LEFT JOIN temp_topics AS prev_sub1 ON t_sub1.level = prev_sub1.level AND prev_sub1.level_order < t_sub1.level_order
 LEFT JOIN temp_topics AS next_sub1 ON t_sub1.level = next_sub1.level AND next_sub1.level_order > t_sub1.level_order
 WHERE t_sub1.topic_textID = t.topic_textID
 GROUP BY t_sub1.topic_textID
 )
AND next.level_order = (
 SELECT MIN(next_sub2.level_order)
 FROM temp_topics AS t_sub2
 LEFT JOIN temp_topics AS prev_sub2 ON t_sub2.level = prev_sub2.level AND prev_sub2.level_order < t_sub2.level_order
 LEFT JOIN temp_topics AS next_sub2 ON t_sub2.level = next_sub2.level AND next_sub2.level_order > t_sub2.level_order
 WHERE t_sub2.topic_textID = t.topic_textID
 GROUP BY t_sub2.topic_textID
 )
ORDER BY topic_textID, prev_order DESC, next_order ASC

Gut, das funktioniert mal - Danke :-), braucht aber dennoch 40 Sekunden für nur 600 Datensätze in temp_topics.

Jetzt besteht aber das Problem dass nur die Einträge ausgegeben werden, die *beide* Nachbarn haben. Das jeweils erste und letzte Element eines "levels" wird aber nicht ausgegeben, da hier ja der Wert NULL ist.

Und jetzt wirds komisch: Ich frage einfach zusätzlich mit OR auf einen NULL Wert ab:

SELECT
 t.topic_textID,
 prev.topic_textID AS prev,
 prev.level_order AS prev_order,
 next.topic_textID AS next,
 next.level_order AS next_order
FROM temp_topics AS t
LEFT JOIN temp_topics AS prev ON t.level = prev.level AND prev.level_order < t.level_order
LEFT JOIN temp_topics AS next ON t.level = next.level AND next.level_order > t.level_order
WHERE t.level != ''
AND (ISNULL(prev.level_order) OR prev.level_order = (
 SELECT MAX(prev_sub1.level_order)
 FROM temp_topics AS t_sub1
 LEFT JOIN temp_topics AS prev_sub1 ON t_sub1.level = prev_sub1.level AND prev_sub1.level_order < t_sub1.level_order
 LEFT JOIN temp_topics AS next_sub1 ON t_sub1.level = next_sub1.level AND next_sub1.level_order > t_sub1.level_order
 WHERE t_sub1.topic_textID = t.topic_textID
 GROUP BY t_sub1.topic_textID
 ))
AND (ISNULL(next.level_order) OR next.level_order = (
 SELECT MIN(next_sub2.level_order)
 FROM temp_topics AS t_sub2
 LEFT JOIN temp_topics AS prev_sub2 ON t_sub2.level = prev_sub2.level AND prev_sub2.level_order < t_sub2.level_order
 LEFT JOIN temp_topics AS next_sub2 ON t_sub2.level = next_sub2.level AND next_sub2.level_order > t_sub2.level_order
 WHERE t_sub2.topic_textID = t.topic_textID
 GROUP BY t_sub2.topic_textID
 ))
ORDER BY topic_textID, prev_order DESC, next_order ASC

Und nun braucht die Abfrage schon 30 Minuten natürlich mit maximaler CPU Last. Und das nur wegen der ISNULL Abfrage :-(

Hast du eine Ahnung wie man das beschleunigen könnte? Und wieso das so langsam ist?

Die Echtdaten haben dann 20000 Einträge und das ganze muss unter 5 Minuten ablaufen... :-/

Danke vielmals und lg, Peter

[Vinzenz Mai]

Hallo,

das sieht nach einer korrelierten Unterabfrage aus, siehe z.B:

</archiv/2006/7/t133015/#m861544>
</archiv/2008/1/t165377/#m1078290>

Vielen Dank für deine Antwort damit hätte ich es theoretisch geschafft! Nur nicht praktisch, da warte ich seit 30 Minuten oder so auf das Ergebnis, wobei die Tabelle nur 300 Einträge hat!

SELECT
t.topic_textID,
prev.topic_textID AS prev,
prev.level_order AS prev_order,
next.topic_textID AS next,
next.level_order AS next_order,
FROM temp_topics AS t
LEFT JOIN temp_topics AS prev ON t.level = prev.level AND prev.level_order < t.level_order
LEFT JOIN temp_topics AS next ON t.level = next.level AND next.level_order > t.level_order
WHERE t.level != ''
ORDER BY topic_textID, prev_order DESC, next_order ASC

Du hast da einen doppelten Selfjoin, auf den ein korreliertes Subselect zu setzen ist noch unperformanter als Selfjoin und korreliertes Subselect, die einzeln schon nicht zum performantesten gehören.

Und nun braucht die Abfrage schon 30 Minuten natürlich mit maximaler CPU Last. Und das nur wegen der ISNULL Abfrage :-(

Hast du eine Ahnung wie man das beschleunigen könnte? Und wieso das so langsam ist?

Die Echtdaten haben dann 20000 Einträge und das ganze muss unter 5 Minuten ablaufen... :-/

Ein Vorschlag wäre, eine temporäre Tabelle zu nutzen:
Selektiere Deine Daten (ohne korrelierte Unterabfrage) in eine temporäre Tabelle. Setze die korrelierte Unterabfrage auf die temporäre Tabelle ab.
Lösche danach die temporäre Tabelle.

Freundliche Grüße

Vinzenz