Berechnung zwischen mehreren SQL-Statements

[spaniac]

so, ich bin jetzt endlich dazu gekommen, weiter zu arbeiten.

Ich fasse noch einmal zusammen:

Das Statement für die Berechnung lautet bisher:

SQL-Code:

select e.datum, e.intervall, sum(e.wert) +

( select sum(f.wert)
from element f
where f.typ_id = 18
AND f.datum = '2010-01-26'
AND f.pilot_id = 55
AND f.datum = e.datum
AND f.intervall = e.intervall
) ergebnis

from element e
where e.typ_id =18
AND e.pilot_id =55
AND e.datum = '2010-01-26'

group by e.datum, e.intervall

ich habe hierfür einen stored procedure angelegt, und deren die parameter jetzt so eingerichtet, dass die pilot_id abhängig von dem parameter "in_bereich" aus einer weiteren tabelle "config" ausgelesen wird. die parameterübergabe habe ich mit einem einfachen select bereits erfolgreich getestet:

SQL-Code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sl7030` $$
CREATE PROCEDURE `sl7030`(in_bereich VARCHAR(45), in_startdate DATE, in_enddate DATE)
    READS SQL DATA
BEGIN
  DECLARE used_pilots VARCHAR(45);

  SELECT pilots
  INTO used_pilots
  FROM config c
  WHERE c.bereich=in_bereich;


  SELECT e.datum, e.intervall, e.wert, e.pilot_id
  FROM element e
  WHERE e.typ_id =18
  AND e.pilot_id IN  (used_pilots)
  AND e.datum BETWEEN in_startdate AND in_enddate;


END $$

DELIMITER ;

soweit ist auch alles einwandfrei, nur stellt sich mir jetzt die frage, wie ich die verschiedenen tabellen zur berechnung heranziehe, da das erste statement ja nur die spalte "wert" aus beiden tabellen addiert.

Wie würde ich jedoch umsetzen, dass sum(wert) von tabelle#1 + sum(wert) von tabelle#2 / sum(wert) von tabelle#3 usw. berechnet wird?

kann ich diese einfach als "ergebnis#1", "ergebnis#2" usw. deklarieren und nachher meine berechnung durchführen?

[omata]

Leider macht deine angegebene SQL-Anweisung gar keinen Sinn (da kann man ja einfach 2*SUM(...) rechnen. Also verdeutlicht dein Beispiel irgendwie nicht so wirklich deine Problemstellung. Zumindest verstehe ich sie nicht.

Trotzdem mal ein Beispiel (vermutlich völliger Blödsinn) ...

SQL-Code:

SELECT e.datum, e.intervall, 
       CASE WHEN COALESCE(tabelle3.wert, 0) > 0 
         THEN SUM(e.wert) + tabelle2.wert / tabelle3.wert 
         ELSE 0 
       END ergebnis
FROM element e
LEFT JOIN (SELECT datum, intervall, SUM(wert) wert
           FROM element
           WHERE typ_id = 18
             AND datum = '2010-01-26'
             AND pilot_id = 55
           GROUP BY datum, intervall) tabelle2
  ON e.intervall = tabelle2.intervall
LEFT JOIN (SELECT datum, intervall, SUM(wert) wert
           FROM element
           WHERE typ_id = 18
             AND datum = '2010-01-26'
             AND pilot_id = 55
           GROUP BY datum, intervall) tabelle3
  ON e.intervall = tabelle3.intervall
WHERE typ_id = 18
  AND pilot_id = 55
  AND e.datum = '2010-01-26'
GROUP BY e.datum, e.intervall, tabelle2.wert, tabelle3.wert

Mir ist nicht klar, wie die einzelnen Tabellen verknüpft werden sollen. Nur über das Intervall? oder auch Datum, Typ, pilot_id? Sind diese immer gleich oder nur in deinem (schlechten) Beispiel?

[spaniac]

@omata: das ist richtig, das beispiel ist etwas schlecht gewählt.

ich habe das beispiel einmal angepasst, so dass ein anderer typ genommen wird (sum(wert) von typ18 + sum(wert) von typ17)


select e.datum, e.intervall, sum(e.wert) +

( select sum(f.wert)
from element f
where f.typ_id = 17
AND f.datum = '2010-01-26'
AND f.pilot_id = 55
AND f.datum = e.datum
AND f.intervall = e.intervall
) ergebnis

from element e
where e.typ_id =18
AND e.pilot_id =55
AND e.datum = '2010-01-26'

group by e.datum, e.intervall


das ganze soll mit beliebigen rechnenarten funktionieren (+,-,/,*,%), was zwischen den einzelnen selects eingesetzt wird.

ist das verständlicher?

[omata]

Ja aber das mit dem "datum" ist doch auch nicht klar. Auf der einen Seite wird es festgelegt und zusätzlich von e geholt. Was soll das?

SQL-Code:

SELECT e.datum, e.intervall,
       CASE WHEN COALESCE(tabelle3.wert, 0) > 0
         THEN SUM(e.wert) + tabelle2.wert / tabelle3.wert
         ELSE 0
       END ergebnis_plus,
       CASE WHEN COALESCE(tabelle3.wert, 0) > 0
         THEN SUM(e.wert) - tabelle2.wert / tabelle3.wert
         ELSE 0
       END ergebnis_minus,
       CASE WHEN COALESCE(tabelle3.wert, 0) > 0
         THEN (SUM(e.wert) * tabelle2.wert) / tabelle3.wert
         ELSE 0
       END ergebnis_multi,
       CASE WHEN     COALESCE(tabelle2.wert, 0) > 0
                 AND COALESCE(tabelle3.wert, 0) > 0
         THEN (SUM(e.wert) / tabelle2.wert) / tabelle3.wert
         ELSE 0
       END ergebnis_geteilt,
       CASE WHEN     COALESCE(tabelle2.wert, 0) > 0
                 AND COALESCE(tabelle3.wert, 0) > 0
         THEN (SUM(e.wert) % tabelle2.wert) / tabelle3.wert
         ELSE 0
       END ergebnis_modulo
FROM element e
LEFT JOIN (SELECT datum, intervall, SUM(wert) wert
           FROM element
           WHERE typ_id = 18
             AND datum = '2010-01-26'
             AND pilot_id = 55
           GROUP BY datum, intervall) tabelle2
  ON e.intervall = tabelle2.intervall
LEFT JOIN (SELECT datum, intervall, SUM(wert) wert
           FROM element
           WHERE typ_id = 18
             AND datum = '2010-01-26'
             AND pilot_id = 55
           GROUP BY datum, intervall) tabelle3
  ON e.intervall = tabelle3.intervall
WHERE typ_id = 18
  AND pilot_id = 55
  AND e.datum = '2010-01-26'
GROUP BY e.datum, e.intervall, tabelle2.wert, tabelle3.wert

[spaniac]

@omata: richtig, das mit dem datum ist doppelt und eigentlich auch überflüssig.

Das Statement werde ich in Ruhe durchgehen, danke schon einmal für die Hilfe!

[spaniac]

ok ok, habe gerade noch einmal meine erklärung gelesen und finde sich wenig greifbar.

ich versuche es anhand eines weiterreichenden beispiels, was ich mit pseudocode aufzeige:

SQL-Code:

/*wert von typ 17 holen*/

(select sum(f.wert)
from element f
where f.typ_id = 17
AND f.datum = '2010-01-26'
AND f.pilot_id = 55
AND f.datum = e.datum
AND f.intervall = e.intervall
) ergebnis1

/*wert von typ 18 holen*/

(select sum(g.wert)
from element g
where g.typ_id = 18
AND g.datum = '2010-01-26'
AND g.pilot_id = 55
AND g.datum = f.datum
AND g.intervall = f.intervall
) ergebnis2

/*wert von typ 19 holen*/

(select sum(h.wert)
from element h
where h.typ_id = 19
AND h.datum = '2010-01-26'
AND h.pilot_id = 55
AND h.datum = g.datum
AND h.intervall = g.intervall
) ergebnis3

group by e.datum, e.intervall 

ergebnis_gesamt = ergebnis1 * ergebnis2 - ergebnis3

letztendlich will ich nur einzelnen sql-abfragen kapseln und zwischen deren sum(x.wert) eine berechnung durchführen.

ist das etwas klarer? :)

[omata]

Es tut mir leid, aber da ist überhaupt nichts klar!

Was ist e? Wird das eingeschränkt?
Auch in diesem Beispiel sind das datum und die pivot_id wieder überall gleich und im ersten Block taucht das datum wieder zweimal im WHERE auf! Sind diese Infos immer gleich oder soll das auch unterschiedlich sein können? Das sind alles wichtige Informationen, ohne die man dir überhaupt nicht helfen kann.

Warum machst du uns das Helfen so schwer? Willst du keine Hilfe?

Also bitte erkläre doch mal deine Struktur und dann was du eigentlich haben möchtest.

Am besten mit Beispieldaten (wie liegen sie vor, was genau! möchtest du als Ergebnis haben)

Ich versuche es trotzdem nochmal...

SQL-Code:

SELECT datum, intervall, 
       SUM(CASE WHEN typ_id = 17 THEN wert ELSE 0 END) AS ergebnis1,
       SUM(CASE WHEN typ_id = 18 THEN wert ELSE 0 END) AS ergebnis2,
       SUM(CASE WHEN typ_id = 19 THEN wert ELSE 0 END) AS ergebnis3,
       SUM(CASE WHEN typ_id = 17 THEN wert ELSE 0 END) 
         * SUM(CASE WHEN typ_id = 18 THEN wert ELSE 0 END) 
         - SUM(CASE WHEN typ_id = 19 THEN wert ELSE 0 END) AS ergebnis_gesamt
FROM element
WHERE datum = '2010-01-26'
  AND pilot_id = 55
GROUP BY datum, intervall

[spaniac]

@omata: sorry wenn das ganze immer noch unverständlich ist. ich gebe dir recht, dass die zeilen

SQL-Code:

AND f.datum = e.datum
AND f.intervall = e.intervall

im ersten select blödsinn sind und weg müssen.

Zitat:

Was ist e? Wird das eingeschränkt?

mein fehler, war falsch übernommen.

Zitat:

Auch in diesem Beispiel sind das datum und die pivot_id wieder überall gleich und im ersten Block taucht das datum wieder zweimal im WHERE auf! Sind diese Infos immer gleich oder soll das auch unterschiedlich sein können? Das sind alles wichtige Informationen, ohne die man dir überhaupt nicht helfen kann.

Momentan ist die Anforderung, dass die pilot_id in jeder abfrage gleich ist. das datum und intervall ist ebenfalls gleich und soll später nur anhand des übergebenen zeitraums eingeschränkt werden (

SQL-Code:

datum BETWEEN 'parameter1' AND 'parameter2'

).

Die doppelten x.datum=y.datum und x.intervall=y.intervall werden ja in deiner abfrage durch das group-by abgefangen oder?

werde deine abfrage gleich verifizieren, vielen dank schonmal dafür, schaut gut aus!

[spaniac]

So, die letzte SELECT-Anweisung von omata hat zum gewünschten Erfolg geführt, so dass ich jetzt beliebige berechnungen zwischen einzelnen selects ausführen kann - ganz genau wie ich es haben wollte.

nun werde ich das ganze erst einmal komplett umsetzen, dann hier posten und schauen, wie ich das auf monats/quartal/jahres-basis aggregiere!

vielen dank allen nochmal, vor allem natürlich omata;)

[spaniac]

Wie zugesagt noch die Lösung, die ich mittlerweile komplett auf alle möglichen Berechnungsmöglichkeiten unser kleinen DB adaptiert habe:

SQL-Code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `calls` $$
CREATE PROCEDURE `calls`(pilot INT(10), in_startdate DATE, in_enddate DATE)
    READS SQL DATA
BEGIN


  /*Typen festlegen und Wert berechnen*/
  SELECT datum,
         SUM(CASE WHEN typ_id = 01 THEN wert ELSE 0 END)
         +SUM(CASE WHEN typ_id = 02 THEN wert ELSE 0 END)
         -SUM(CASE WHEN typ_id = 03 THEN wert ELSE 0 END)
         -SUM(CASE WHEN typ_id = 04 THEN wert ELSE 0 END)
         +SUM(CASE WHEN typ_id = 05 THEN wert ELSE 0 END)
         -SUM(CASE WHEN typ_id = 06 THEN wert ELSE 0 END)AS calls
  FROM element e
  WHERE e.datum BETWEEN in_startdate AND in_enddate
  AND e.pilot_id = pilot;
  GROUP BY datum;


END $$

DELIMITER ;

Vielen Dank noch einmal für die Hilfe!