Berechnung zwischen mehreren SQL-Statements

[spaniac]

Hallo,

ich bin durch einen Tipp auf das DP-Forum gestossen und erhoffe mir ein paar Lösungsansätze für folgende Problemstellung:


Vorgeschichte

Ich nutze eclipse BIRT zur Auswertung einer MySQL-DB. Hierzu lassen sich in BIRT sog. Data Sets erstellen, die einzelne SQL-SELECT-Statements kapseln.


Ziel

Ziel soll es sein, innerhalb eines einzelnen SLEECT-Statements mehrere SELECT-Statements zu nutzen und zwischen den Ergebnistabellen der einzelnen SELECT-Statements Berechnungen anzustellen (*,+,-,/).


DB-Struktur

SQL-Code:

Schema: element

SQL-Code:

id | datum | intervall | wert | typ_id | bereich_id
1 | 2009-01-01 | 8:00 | 3.0 | 2 | 2
2 | 2009-01-01 | 8:30 | 2.0 | 3 | 1
...

Textuelle Beschreibung

(Alle Werte vom Typ 3 und Bereich 2,3,4) + (Alle Werte vom Typ 2 und Bereich 2,3,4) * (Alle Werte vom Typ 14 und Bereich 2,3,4) in Abhängigkeit des Datums und des Intervalls.


Pseudo-SQL

SQL-Code:

(SELECT e.wert FROM element e WHERE typ_id = 3 AND bereich_id IN (2,3,4)) + (SELECT e.wert FROM element e WHERE typ_id = 2 AND bereich_id IN (2,3,4)) * (SELECT e.wert FROM element e WHERE typ_id = 14 AND bereich_id IN (2,3,4))

Wobei hier eine Bedingung wie bei einem JOIN a la

SQL-Code:

abfrage1.datum=abfrage2.datum AND abfrage2.datum=abfrage3.datum AND abfrage1.intervall=abfrage2.intervall AND abfrage2.intervall=abfrage3.intervall

mit eingebaut werden müsste. Dies geht meines Erachtens jedoch nicht, oder?


Frage
Wie kann ich eine solche Abfrage

a) überhaupt mit SQL realisieren?
b) in einem einzelnen SQL-SELECT-Statement realisieren?


Auch eine Stored Procedure wäre möglich, da BIRT die Möglichkeit bietet, eben solche anzusprechen.

Ich danke Euch schon im Voraus für die Hilfe, wenn es noch Fragen gibt: Immer her damit!

[mkinzler]

Zitat:

a) überhaupt mit SQL realisieren?

Ja mit einer SP

Zitat:

b) in einem einzelnen SQL-SELECT-Statement realisieren?

Aus der SP kannst du dann die Ergebnise lesen

[spaniac]

Wow, das war schnell :)

Bekomme ich die Ausgabe aus der SP dann so heraus, dass ich quasi eine "Temporäre" SQL-Table habe, die in etwa so aussieht:

SQL-Code:

id | datum | intervall | wert | typ_id | bereich_id 
1 | 2009-01-01 | 8:00 | 3.0+2.0*3.0 | 2 | 2 
2 | 2009-01-01 | 8:30 | 2.0+1.5*3.3 | 3 | 1

und wenn ja, wie könnte so eine beispielhafte SP aussehen?

[mkinzler]

Aus einer (selectable) SP kannst du wie aus einer Tabelle lesen, diese Joinen usw.

SQL-Code:

select <Feldliste> From <SPName>(<Parameter>) where ...

[TBx]

Ich bin in mySQL nicht sonderlich firm, aber prinzipiell sollte es so gehen:

SQL-Code:

select e.datum,
       e.interval,
       sum (e.wert) * (
                       select sum (f.wert) 
                         from element f
                         where f.typ = 14
                           and f.bereich in (2,3,4)
                           and f.datum = e.datum
                           and f.interval = e.interval
                      ) ergebnis
  from element e
  where e.typ in (2,3)
    and e.bereich in (2,3,4)
  group by e.datum,
           e.interval

ungetestet, hoffe es hilft weiter

PS: Wenn Du das auch noch nach typ und bereich aufgeschlüsselt brauchst, kann man das auch noch mit verbasteln

[spaniac]

@TBx: Das sieht schonmal gut aus.

Macht es nicht trotzdem Sinn, das ganze als Stored Procedure zu kapseln und dieser als Parameter Bereich, Typ und Datum als Parameter mitzugeben?

Weiterhin ist mir noch nicht klar, wenn einer der Select-Befehle für eine Datum/Intervall-Kombination keinen Eintrag enthält. Wird dies ignoriert oder als 0 gewertet?

[mkinzler]

Ich würde es mit einer SP versuchen, da ein solcher Subselect nicht besonderst performant ist.

[omata]

SQL-Code:

SELECT datum, interval, SUM(typ2) + SUM(typ3) * SUM(typ14) AS ergebnis
FROM (SELECT datum, interval,
             CASE WHEN typ =  2 THEN wert ELSE 0 END typ2,
             CASE WHEN typ =  3 THEN wert ELSE 0 END typ3,
             CASE WHEN typ = 14 THEN wert ELSE 0 END typ14
      FROM element
      WHERE bereich in (2, 3, 4)) x
GROUP BY datum, interval

[spaniac]

@omata: super, das sieht schonmal gut aus!

@mkinzler: mein wissen von einem crashkurs SP in Oracle ist leider schon zu alt und ich arbeite mich gerade erst wieder ein. hättest du einen vorschlag für eine SP, gerne auch nur ansätze, die ich mir dann weiter erarbeiten muss.

[spaniac]

So, ich habe mich etwas weiter eingelesen. m.E. macht es Sinn, die SELECT-Statements einzeln in einer SP auszuführen, das Resultset in eine Variable zu speichern (geht das?) und dann innerhalb der SP die Berechnung auszuführen.

Passt das soweit von der Idee? Vorschläge sind willkommen :)

[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!