Gruppierungs-Abfragen mit WHERE-Klausel

BenBE · Verfasst: So 22.01.06 15:19

Hi,

ich verzweifel grad an einem MySQL 3.23-Server (also dem Teil, was keine Subselects und andere sinnvollen Erweiterungen kennt) ...

Für eine Webanwendung (in PHP geschrieben, ist hier aber nebensächlich), benötige ich eine Abfrage, die mir folgendes liefert:

1. Alle Datensätze aus Tabelle cvs_revisions, die die neuste Revision (MAX(RevDate) in GROUP BY RevFID, RevBranch) darstellen
2. Wobei Datensätze aus 1. Ausgeschlossen werden sollen, wenn diese im Datensatz den Quelltext (RevSize <> 0) bereits enthalten

Das Problem besteht nun darin, dass die WHERE-Clause VOR der Gruppierung angewendet wird und somit eine Abfrage der Form

markieren

SQL-Anweisung

1:
2:
3:
4:
5:

SELECT *
FROM `cvs_revisions` AS `R`
WHERE `R`.`RevSize` = 0
GROUP BY `R`.`RevFID` , `R`.`RevBranch`
ORDER BY `R`.`RevDate` DESC

Mir immer die aktuellste Revision einer jeden Datei liefert, die noch keinen Source hat. Wenn die aktuellste Revision einer Datei aber bereits vorhanden ist, benötige ich KEINEN Datensatz dieser RevFID\RevBranch-Gruppe mehr ...

Vereinfacht:

markieren

Quelltext

1:
2:
3:
4:
5:
6:
7:
8:
9:

RevID     RevFID    RevBranch RevVersion RevDate   RevSize
-------------------------------------------------------------
1         1         MAIN      1.2        113780000 0         //Benötigt, weil aktuellste Revision UND ohne Source
2         1         MAIN      1.1        113770000 0         //Nicht benötigt, nicht aktuellste Revision
3         1         Branch2   1.1.2.1    113775000 0         //Benötigt, weil aktuellste Revision im Branch UND ohne Source
4         2         MAIN      1.2        113780000 1337      //Nicht benötigt, Source bereits vorhanden
5         2         MAIN      1.1        113770000 0         //Nicht benötigt, nicht aktuellste Revision
6         2         Branch2   1.1.2.1    113775000 0         //Benötigt, weil aktuellste Revision im Branch UND ohne Source
...       ...       ...       ...        ...       ...       ...

Ich hoffe, jemand kann mir da einen möglichst effektiven Weg sagen, wie man das (wenn möglich mit einer Einzelabfrage) lösen kann.

Zum Vergleich: Die obige Abfrage liefert neben den 3 gewünschten Datensätzen zusätzlich auch Datensatz 5, der mich nicht interessiert ...

TIA,
BenBE.

_________________

Anyone who is capable of being elected president should on no account be allowed to do the job.
Ich code EdgeMonkey - In dubio pro Setting.

jasocul · Verfasst: So 22.01.06 17:13

Ich habe mir das nicht im Detail angesehen, aber du machst einen grundsätzlichen SQL-Fehler beim Group by.
Ein Select * funktioniert nicht mit einem group by. Du musst mindestens die Aggregat-Funktion (in diesem Fall "Max") in das Select mit aufnehmen. Bei Bedarf auch noch die Felder, nach denen du gruppierst.
In etwa so:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:

SELECT `R`.`RevFID` , `R`.`RevBranch`, max(`R`.`RevDate`) as `MaxRev`
FROM `cvs_revisions` AS `R`
WHERE `R`.`RevSize` = 0
GROUP BY `R`.`RevFID` , `R`.`RevBranch`
HAVING max(`R`.`RevDate`) > `1.2`
ORDER BY `R`.`RevDate` DESC

k.A., ob mySQL Having kennt. Theoretisch sollte es aber.

BenBE · Verfasst: So 22.01.06 19:30

Das mit dem * im Select funzt auf 3.23, werd ich aber gern ändern, war auch erstmal nur zum Testen.

Having kennt er, jedoch kommt er dabei immer noch auf das gleiche wie oben raus ...

Wie bekomme ich es hin, diese Abfrage als eine Art "Zwischenergebnis" zu speichern, um die Filterung auf WHERE `R`.`RevSize` = 0 im nächsten Schritt auf das Ergebnis von

markieren

SQL-Anweisung

1:
2:
3:
4:

SELECT `R`.`RevID` , `R`.`RevFID` , `R`.`RevBranch`, MAX(`R`.`RevDate`) AS `MaxRev`, `R`.`RevSize`
FROM `cvs_revisions` AS `R`
WHERE `R`.`RevSize` = 0
GROUP BY `R`.`RevFID` , `R`.`RevBranch`

anzuwenden (so bringt die Abfrage das richtige Ergebnis, nur eben mit jenen Datensätzen zuviel, die ich mit der zweiten mühelos filtern könnte).

D.h. sowas in der Art:

markieren

Aktuellste Revisionen aus jedem Branch suchen:

1:
2:
3:
4:

SELECT `R`.`RevID` , `R`.`RevFID` , `R`.`RevBranch`, MAX(`R`.`RevDate`) AS `MaxRev`, `R`.`RevSize`
FROM `cvs_revisions` AS `R`
GROUP BY `R`.`RevFID` , `R`.`RevBranch`
ORDER BY `R`.`RevDate` DESC

markieren

Datensätze wegfiltern, die eine RevSize <> 0 haben:

1:
2:
3:

SELECT *
FROM PreviousResult
WHERE `R`.`RevSize` = 0

_________________

Anyone who is capable of being elected president should on no account be allowed to do the job.
Ich code EdgeMonkey - In dubio pro Setting.

jasocul · Verfasst: Mo 23.01.06 09:07

Da, wie du im ersten Beitrag schriebst, deine Version keine Sub-Selects kann, wirst du mit einer temporären Tabelle arbeiten müssen. Dort trägst du das Zwischenergebnis ein und machst dann ein Select auf die temporäre Tabelle.
Alternativen:
- Views: kann aber afair mySQL in deiner Version auch nicht.
- Komponenten: Mit der Filter-Funktion arbeiten. Es gibt aber auch eine Komponente (k.A. wie die heißt), die Ergebnisse im Speicher wie eine Tabelle behandelt. Habe ich aber noch nie verwendet.
- Andere DB, die Sub-Select beherscht.

BenBE · Verfasst: Mo 23.01.06 11:35

jasocul hat folgendes geschrieben:

Da, wie du im ersten Beitrag schriebst, deine Version keine Sub-Selects kann, wirst du mit einer temporären Tabelle arbeiten müssen. Dort trägst du das Zwischenergebnis ein und machst dann ein Select auf die temporäre Tabelle.

Hmmm, wollt ich eigentlich vermeiden, da die Abfragen Multithreaded laufen ... Da wäre sowas relativ riskant ...

jasocul hat folgendes geschrieben:

Alternativen:
- Views: kann aber afair mySQL in deiner Version auch nicht.

jasocul hat folgendes geschrieben:

- Komponenten: Mit der Filter-Funktion arbeiten. Es gibt aber auch eine Komponente (k.A. wie die heißt), die Ergebnisse im Speicher wie eine Tabelle behandelt. Habe ich aber noch nie verwendet.

Im Source eines Wiki-Plugins hab ich sowas mal gesehen ... Aber k.A. in welchem. Kannst Du mir mal einen Link auf die entsprechende Seite im MySQL-Manual geben, wo diese Filter-Kompo näher erläutert wird (ich arbeite nicht mit Delphi, daher bringt mir BDE, ADO, ... relativ wenig).

jasocul hat folgendes geschrieben:

- Andere DB, die Sub-Select beherscht.

Was??? Den Support meines Webservers nerven "Was habt ihr für ne Asbach Uralt-Version eines MySQL-Servers drauf! Könnten Sie updaten, ich muss eine Abfrage durchführen, die er jetzt nicht kann."???

Ich glaub, das machen die nicht mit :mrgreen:

_________________

Anyone who is capable of being elected president should on no account be allowed to do the job.
Ich code EdgeMonkey - In dubio pro Setting.

jasocul · Verfasst: Mo 23.01.06 11:55

BenBE hat folgendes geschrieben:

jasocul hat folgendes geschrieben:

- Komponenten: Mit der Filter-Funktion arbeiten. Es gibt aber auch eine Komponente (k.A. wie die heißt), die Ergebnisse im Speicher wie eine Tabelle behandelt. Habe ich aber noch nie verwendet.

Im Source eines Wiki-Plugins hab ich sowas mal gesehen ... Aber k.A. in welchem. Kannst Du mir mal einen Link auf die entsprechende Seite im MySQL-Manual geben, wo diese Filter-Kompo näher erläutert wird (ich arbeite nicht mit Delphi, daher bringt mir BDE, ADO, ... relativ wenig).

mySQL ist nicht mein Spezialgebiet. Mir geht es da wie dir: Ich habs mal gesehen, mehr aber auch nicht. Aber es gibt doch hier auch genug mySQL-Spezis. Vielleicht wissen die näheres.

BenBE hat folgendes geschrieben:

jasocul hat folgendes geschrieben:

- Andere DB, die Sub-Select beherscht.

Was??? Den Support meines Webservers nerven "Was habt ihr für ne Asbach Uralt-Version eines MySQL-Servers drauf! Könnten Sie updaten, ich muss eine Abfrage durchführen, die er jetzt nicht kann."???

Ich glaub, das machen die nicht mit :mrgreen:

Versuchen würde ich es trotzdem. Wenn keiner meckert, wird sich auch nichts ändern. Das solltest du als Programmierer doch wissen. Nur wenn dir jemand sagt, was er will, änderst du was. Ansonsten gehtst du doch davon aus, das alles zur Zufriedenheit ist, oder?

BenBE · Verfasst: So 29.01.06 21:23

Hab's jetzt hinbekommen:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

SELECT 
    `R2`.`RevID`, `R2`.`RevFID`, `R2`.`RevVersion`, `R2`.`RevBranch`
FROM
    `cvs_revisions` AS `R1`
INNER JOIN `cvs_revisions` AS `R2`
    ON `R1`.`RevFID` = `R2`.`RevFID`
    AND `R1`.`RevBranch` = `R2`.`RevBranch`
INNER JOIN `cvs_revisions` AS `R3`
    ON `R1`.`RevFID` = `R3`.`RevFID`
    AND `R1`.`RevBranch` = `R3`.`RevBranch`
WHERE 
    `R2`.`RevSize` = 0
AND
    `R1`.`RevID` = `R3`.`RevID`
GROUP BY 
    `R2`.`RevFID`, `R2`.`RevBranch`, 
    `R3`.`RevFID`, `R3`.`RevBranch`
HAVING
    MAX(`R3`.`RevDate`) = MAX(`R2`.`RevDate`)
ORDER BY
    `R2`.`RevFID` ASC, `R2`.`RevBranch` ASC
LIMIT 0, 100000

Kann man das noch optimieren?

Weil folgende Meldung schreckt doch etwas ab (Auch wenn's nicht mehr ~3 Min sind, siehe unten):

phpMyAdmin meldete:

Zeige Datensätze 0 - 232 (233 insgesamt, die Abfrage dauerte 3.4765 sek)

Die Ausgangstabelle cvs_revisions enthält lediglich 2100 Datensätze.

//edit: Nochmal etwas optimiert, brauch jetzt nicht mehr 163.4960 Sekunden, wie es die vorherige Abfrage noch benötigt hatte, die ich zwischenzeitlich hier stehen hatte ...

_________________

Anyone who is capable of being elected president should on no account be allowed to do the job.
Ich code EdgeMonkey - In dubio pro Setting.

	Mitgliederliste
	Gruppen
	Das Team
	Richtlinien
	Synonyme