Abfrage über 3 Tabellen, Zeile mit höchstem Wert als Condition bestimmen

hustbaer

Ich bin nicht sicher ob ich die Sache mit den "classifications" richtig vertstanden habe. Falls ja, könnte das so aussehen:

SELECT
	p.id AS parameter_id,
	p.name AS parameter_name,
	(SELECT TOP 1 h.value FROM history AS h WHERE h.parameter_id = p.id AND h.revision_id <= @revision ORDER BY h.revision_id DESC) AS parameter_value
FROM
	parameters AS p
WHERE
	p.classification_id = @classification_id

TOP 1 ist T-SQL (MS SQL Server). In anderen SQL Dialekten geht das oft indem man statt dem TOP 1 ganz hinten ein LIMIT 1 anhängt.

DocShoe

Danke für eure Antworten, ich hab´s inzwischen hinbekommen.
Nach den erfolglosen "Direktversuchen" bin ich über einen View gegangen und hab´ den später durch eine CTE ersetzt:

WITH params AS 
(
   SELECT
     p.id                AS parameter_id,
     p.name              AS name,
     p.classification_id AS classification_id,
     r.id 		  AS revision_id,
     r.revision 	  AS revision,
     h.value 	          AS value,
  FROM
     parameters p,
     revisions r,
     history h
  WHERE
     p.id = h.parameter_id                    AND
     r.id = h.revision_id                     AND
     p.classification_id = @classification_id AND
     r.revision <= @revision
)   
SELECT
  p.parameter_id,
  p.classification_id,
  p.name,
  p.value
FROM 
  params p
JOIN
(
  SELECT 
     p.parameter_id,   
     max( p.revision ) AS revision
  FROM
    params p
  WHERE
    p.classification_id = @classification_id AND
    p.revision <= @revision
  GROUP BY
    p.parameter_id
) r
ON 
  p.revision     = r.revision AND
  p.parameter_id = r.parameter_id

Die CTE wird an zwei Stellen benutzt und ist in der echten Abfrage um einige Parameter länger, da könnte ich mir noch überlegen, ob ich die durch zwei SQL Subqueries ersetze. Bei einem Vergleich (2x Subquery vs. CTE) hat die CTE laut Postgres Query Planer deutlich besser abgeschnitten, ich muss da jetzt ein paar Benchmarks machen.

Swordfish

@DocShoe sagte in Abfrage über 3 Tabellen, Zeile mit höchstem Wert als Condition bestimmen:

ich muss da jetzt ein paar Benchmarks machen.

ja, miss den Scheiß. Ich weiß (bei allem Respekt dafür daß Du beruflich damit zu tun hast) nicht, warum Du hier blöd fragst. Miss den Scheiß!

hustbaer

Ja messen ist immer leicht gesagt. Dafür muss man sich erstmal ein Script schreiben das einem die Tabellen schön randvoll mit Daten knallt. In der Grössenordnung die man im Realbetrieb nach ein paar Jahren erwartet. Und idealerweise realistisch verteilte Daten.

Das ist natürlich keine Rocket-Science, aber es kostet Zeit.

Wenn man seine Datenbank halbwegs kennt und Query-Pläne gut lesen kann, kann man oft gut schätzen. Bei MS SQL-Server z.B. kann ich mit an Sicherheit grenzender Wahrscheinlichkeit sagen dass man bei so einer Abfrage nicht Strafe zahlt wenn man einzelne Sub-Selects für 2, 3, 4 zusätzliche Spalten macht. So lange die Sub-Selects identisch sind bis auf die selektierten Werte wird das zuverlässig optimiert und ist nicht teurer als ein entsprechender JOIN.

Wie das bei Postgres aussieht weiss ich nicht, damit hab ich nie gespielt.

hustbaer

@DocShoe Für mich ist die implizite JOIN Syntax die du verwendest immer furchtbar verwirrend.
Also das

   SELECT
     ...
  FROM
     parameters p,
     revisions r,
     history h
  WHERE
     p.id = h.parameter_id                    AND
     r.id = h.revision_id                     AND
     p.classification_id = @classification_id AND
     r.revision <= @revision

schreibe ich als

SELECT
    ...
FROM       parameters p
INNER JOIN history h     ON h.parameter_id = p.id
INNER JOIN revisions r   ON r.id = h.revision_id
WHERE
        p.classification_id = @classification_id
    AND r.revision <= @revision

DocShoe

@hustbaer
Geschmack, denke ich. Hab´ das damals so kennengelernt und finde das einfacher zu lesen. Der Vorteil bei den JOINs ist, dass man sofort erkennt, wie die Beziehungen der Tabellen sind und in der WHERE-Klausel die Bedingungen stehen.
Ich muss mich alle Jubeljahre mal mit SQL beschäftigen und muss mich da jedes Mal wieder reinarbeiten, da finde ich die Verknüpfung in der WHERE-Klausel einfacher zu lesen.

@Swordfish
Keine Ahnung, was du mir sagen willst. Schlechte Laune? Bei Postgres galten CTEs als Optimization Fence, kA, ob das immer noch so ist. Und deshalb muss ich benchmarken, ob die CTE Lösung langsamer ist als die Lösung mit zwei identischen Subqueries. Weiß jetzt echt nicht, wo dein Problem liegt.

hustbaer

@DocShoe sagte in Abfrage über 3 Tabellen, Zeile mit höchstem Wert als Condition bestimmen:

Der Vorteil bei den JOINs ist, dass man sofort erkennt, wie die Beziehungen der Tabellen sind und in der WHERE-Klausel die Bedingungen stehen.

Genau. Es erlaubt mir auch die Query Stück für Stück zu lesen, anstatt alles auf einmal. Und für mich macht es von Verständnis her einen Unterschied ob etwas eine Join Bedingung ist oder eine Filter Bedingung.

Geschmack, denke ich.

Ich hätte jetzt gesagt Gewohnheit. Läuft aber quasi auf das selbe raus.

Und deshalb muss ich benchmarken, ob die CTE Lösung langsamer ist als die Lösung mit zwei identischen Subqueries.

Du kannst die CTE Variante ja auch zu nested Queries umschreiben. Aber schau zuerst ob es einen Unterschied macht. Bei MS SQL Server zahlst du z.B. nur in bestimmten Fällen CTE-Strafe, z.B. wenn diese rekursiv sind. Nicht aber bei ganz normalen Joins und ich glaube auch nicht bei GROUP BY/PARTITION BY.

BTW: PARTITION BY + ROW_NUMBER() + HAVING wäre auch eine Möglichkeit.

DocShoe

@hustbaer sagte in Abfrage über 3 Tabellen, Zeile mit höchstem Wert als Condition bestimmen:

Genau. Es erlaubt mir auch die Query Stück für Stück zu lesen, anstatt alles auf einmal. Und für mich macht es von Verständnis her einen Unterschied ob etwas eine Join Bedingung ist oder eine Filter Bedingung.

Hm, macht Sinn. Sollte ich mir dann doch angewöhnen.

hustbaer

Wenn du bloss so selten SQL machst, ist es vermutlich nicht nötig.

Ich glaube dass es "besser" ist, aber wenn der Code ansonsten sauber ist, ist das sicher nicht das grosse Problem. Ist ja nicht so dass es falsch wäre und es hat auch keine "nicht-lokalen" Auswirkungen.

Also wenns dich interessiert, sicher. Aber ich will dir jetzt hier nix einreden was dir nur Arbeit macht aber vermutlich nicht so viel bringt.

DocShoe

@hustbaer
Ist ja "nur" eine Angewohnheit, die man sich antrainieren muss. Und wenn man beim Schreiben schon drauf achtet kein großer Aufwand. Ich werd´ mit Sicherheit jetzt nicht alle Skripte durchgehen und das umbauen, aber in Zukunft drauf achten.

hustbaer

Wenns für dich beim Lesen keinen grossen Unterschied macht, ist es keine grosse Sache, ja.

codegod70

Sehr wertvolle Informationen hier, die kann ich gleich direkt umsetzen in meine Uniprojekte. Danke

Swordfish

@DocShoe sagte in Abfrage über 3 Tabellen, Zeile mit höchstem Wert als Condition bestimmen:

@Swordfish
Keine Ahnung, was du mir sagen willst. Schlechte Laune? Bei Postgres galten CTEs als Optimization Fence, kA, ob das immer noch so ist. Und deshalb muss ich benchmarken, ob die CTE Lösung langsamer ist als die Lösung mit zwei identischen Subqueries. Weiß jetzt echt nicht, wo dein Problem liegt.

No problem at all. Du bestätigst nur das was ich geschrieben habe. Miss die Kacke.

@hustbaer sagte in Abfrage über 3 Tabellen, Zeile mit höchstem Wert als Condition bestimmen:

Das ist natürlich keine Rocket-Science, aber es kostet Zeit.

Ja, das kostet Zeit. Wenn man das so in ein Forum knallt kostet es jedem der es liest Zeit. Ich würde mich sowas nicht trauen bzw. es würde meiner Nächstenliebe zuwider sein.