Summe eines Subqueries

[basementmedia]

Hi zusammen,

ich möchte aus den einzelnen Zeilen eines Querys eine Summe bilden:

SELECT
ROUND(((sum(stunden.stunden))*(ROUND(((ROUND((projekte.hmax * projekte.hsatz),2))/((SELECT sum(stunden) FROM stunden WHERE projekt_id=projekte.ID))),2) )),2) as peuro_h_u
FROM stunden
LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
WHERE stunden.user_id = 27 AND (stunden.datum between '2018-01-01' AND '2018-01-31')
GROUP BY stunden.projekt_id
ORDER BY sum(stunden.stunden) ASC

Folgendes klappt zwar:
SELECT SUM(peuro_h_u) as peuro_h_u_summe FROM (SELECT
ROUND(((sum(stunden.stunden))*(ROUND(((ROUND((projekte.hmax * projekte.hsatz),2))/((SELECT sum(stunden) FROM stunden WHERE projekt_id=projekte.ID))),2) )),2) as peuro_h_u
FROM stunden
LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
WHERE stunden.user_id = 27 AND (stunden.datum between '2018-01-01' AND '2018-01-31')
GROUP BY stunden.projekt_id
ORDER BY sum(stunden.stunden) ASC) as peuro_h_u_summe

Das Problem ist nur, dass ich diesen Query bereits in einen anderen Subquery verschachteln will, um im Anschluss daran die user.ID (im Beispiel oben fix auf 27 gesetzt) vom Parent-Query zu beziehen, wie nachfolgend dargestellt:

SELECT
u.ID as dataset_id,
u.name,
(SELECT SUM(peuro_h_u) as peuro_h_u_summe FROM (SELECT
ROUND(((sum(stunden.stunden))*(ROUND(((ROUND((projekte.hmax * projekte.hsatz),2))/((SELECT sum(stunden) FROM stunden WHERE projekt_id=projekte.ID))),2) )),2) as peuro_h_u
FROM stunden
LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
WHERE stunden.user_id = u.ID AND (stunden.datum between '2018-01-01' AND '2018-01-31')
GROUP BY stunden.projekt_id
ORDER BY sum(stunden.stunden) ASC) p) as peuro_summe
FROM user u ORDER BY u.name

Da ich hier dann eine doppelte Verschachtelung habe, kennt er u.ID im Unter-Unter-Query nicht.
Und ich komm einfach nicht drauf, wie ich die doppelte Verschachtelung umgehen kann.

Kann mir irgendjemand einen Tipp geben.

Grüßle
Daniel

 

[akretschmer]

kannst Du das an einem minimalisierten Beispiel mit Tabellendefinition,Beispieldaten und Wunschresultat nachvollziehbar darstellen?

 

[basementmedia]

Hi,

ich versuchs mal:

Ich hab eine Tabelle "Stunden" in der erfasst ist, welcher User wann wieviele Stunden auf ein Projekt gebucht hat.
Die Tabelle ist (vereinfacht) so aufgebaut:

ID datum user_id projekt_id stunden
___________________________________________
1 2018-01-01 27 1 6
2 2018-01-05 13 2 3
usw.

Dann hab ich noch ne andere Tabelle Projekte, in der alle Projekte erfasst werden. Zu jedem Projekt ist erfasst, wieviele Stunden man maximal benötigen darf (hmax) und welchen Stundensatz das Projekt hat (hsatz).

Nun will ich eine Auswertung fahren, die berechnet, wieviel Euro jeder User innerhalb eines bestimmten Zeitraums erwirtschaftet hat. Dazu sollen die folgenden Berechnungen (möglichst in einem einzelnen Query) gefahren werden:

Mache für jeden User folgendes:
1. Ermittle die Stundensumme pro Projekt innerhalb des betrachteten Zeitraums (h_user)
2. Ermittle die GESAMTE Stundensumme (=hist), die auf das Projekt bereits gebucht wurde (also auch von anderen Usern).
3. Ermittle den Ertrag des Projekts pro Stunden (h_pro_stunde=(hmax)*(hsatz)/hist).
3. Ermittle den Anteil des Users am Ertrag im betrachteten Zeitraum (=h_pro_stunde * h_user.

Soviel zur Erklärung des vorhabens.
Innerhalb der Auswertung werden aber noch ganz viele andere Berechnungen gefahren, d.h. die oben beschriebene Teilauswertung bezieht ihre user_id jeweils von einem übergeordneten Query, der in einer Tabelle für jeden User einen Zeile erzeugt und dazu dann in verschiedenen Spalten verschiedene Auswertungen ausgibt.

Vereinfacht sieht mein Problem von der rein technischen Seite also so aus:

ich habe einen Hauptquery (den hab ich nun mal vereinfach):

SELECT
u.ID as dataset_id,
u.name
FROM user u ORDER BY u.name

D.h. es wird erstmal nur der Name aller User ausgegeben.

Außerdem hab ich einen weiteren Query, der mir zu jedem User eine Teilauswertung ausgibt (hier stark vereifacht, nur die Summe der gebuchten Projektstunden des Users pro Projekt werden ausgegeben. Außerdem ist die User_ID fix auf 27 gesetzt):

SELECT sum(stunden.stunden)
FROM stunden
LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
WHERE stunden.user_id = 27 AND (stunden.datum between '2018-01-01' AND '2018-01-31')
GROUP BY stunden.projekt_id

Im ersten Schritt möchte ich nun die Summe aus den einzelnen Zeilen, die der Query erzeugt, d.h. quasi

SELECT SUM(stunden) as summe_stunden FROM (
SELECT sum(stunden.stunden) as stunden
FROM stunden
LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
WHERE stunden.user_id = 27 AND (stunden.datum between '2018-01-01' AND '2018-01-31')
GROUP BY stunden.projekt_id
)

Und im letzten Schritt will ich diese Summe nun nicht nur vom User mit der ID 27 sondern von jedem User im Parent-Query.
Also quasi:

SELECT
u.ID as dataset_id,
u.name,
SELECT SUM(stunden) FROM (
SELECT sum(stunden.stunden)
FROM stunden
LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
WHERE stunden.user_id = u.ID AND (stunden.datum between '2018-01-01' AND '2018-01-31')
GROUP BY stunden.projekt_id
) es summe_stunden
FROM user u ORDER BY u.name

Und das funktioniert eben nicht mehr, weil u.ID zu tief verschachtelt ist.

Mir fällt aber kein Weg ein, diese Umschachtelung zu umgehen.

Puh, sorry, wegen dem vielen Text aber ich hoffe es ist nun etwas verständlicher.

Viele Grüße
Daniel

 

[basementmedia]

statt "es summe_stunden" meinte ich natürlich "as summe_stunden".

 

[akretschmer]

Ich werde nicht ganz schlau draus, vermute aber, daß Du gruppenweise summieren willst, also die Stunden per User und per Projekt und so, oder?

test=*# select * from stunden ;
 id |  datum  | user_id | projekt_id | stunden
----+------------+---------+------------+---------
  1 | 2018-01-01 |  27 |  1 |  6
  2 | 2018-01-05 |  13 |  2 |  3
  3 | 2018-01-07 |  27 |  2 |  12
(3 Zeilen)

test=*# select * from projekte;
 id |  name  | hmax | hsatz
----+-----------+------+-------
  1 | projekt 1 |  10 |  5
  2 | projekt 2 |  20 |  10
(2 Zeilen)

test=*# select stunden.user_id, stunden.projekt_id, sum(stunden.stunden) over (partition by user_id) as stunden_per_user, sum(stunden) over (partition by projekt_id) as stunden_per_projekt from stunden left join projekte on stunden.projekt_id=projekte.id where stunden.user_id in(13, 27) and stunden.datum between '2018-01-01' and '2018-01-31' order by user_id, projekt_id;
 user_id | projekt_id | stunden_per_user | stunden_per_projekt
---------+------------+------------------+---------------------
  13 |  2 |  3 |  15
  27 |  1 |  18 |  6
  27 |  2 |  18 |  15
(3 Zeilen)

kommt das hin?

 

[basementmedia]

Puh: sum() over () und (partition by) kenn ich bisher noch garnicht.
Da muss ich ich mal kurz mit befassen um beantworten zu können, ob das mein Problem löst.
Denn im oberen Beispiel hab ich nun ja alles stark vereinfacht.

 

[basementmedia]

By the way:
Dein Query erzeugt bei mir erstmal einen Fehler.
Kann es sein, dass partition by mit mysql nicht funktioniert?

 

[akretschmer]

Ja, die schlechte Nachricht für Dich: MySQL kann das (und ganz viele andere Dinge) NICHT.

 

[basementmedia]

Gibt es denn eine andere Möglichkeit, um von einem doppelt verschachtelten Query auf einen Alias eines äußersten Queries zu zugreifen? Oder wie ich meinen Query vereinfach könnte?

 

[akretschmer]

Es gibt einen LATERAL JOIN, mit dem man quasi auf die 'äußere' Abfrage zugreifen kann. Mal als Demo.

Du hast eine Tabelle wie diese:

test=*# select * from lateral_demo ;
 id | c
----+---
  1 | 5
  2 | 7
  3 | 2
(3 Zeilen)

Du willst jetzt jede dieser Einträge um die Anzahl in der Spalte c vervielfältigen. Also id=1 5 mal, ID=2 7 mal und so weiter.

Die einfach Methode geht nicht:

test=*# select * from lateral_demo l left join (select * from generate_series(1, l.c)) foo on true;
ERROR:  invalid reference to FROM-clause entry for table "l"
ZEILE 1: ...emo l left join (select * from generate_series(1, l.c)) foo ...
  ^
TIP:  There is an entry for table "l", but it cannot be referenced from this part of the query.

Aber mit dm LATERAL JOIN geht es:

test=*# select * from lateral_demo l left join lateral (select * from generate_series(1, l.c)) foo on true;
 id | c | generate_series
----+---+-----------------
  1 | 5 |  1
  1 | 5 |  2
  1 | 5 |  3
  1 | 5 |  4
  1 | 5 |  5
  2 | 7 |  1
  2 | 7 |  2
  2 | 7 |  3
  2 | 7 |  4
  2 | 7 |  5
  2 | 7 |  6
  2 | 7 |  7
  3 | 2 |  1
  3 | 2 |  2
(14 Zeilen)

test=*#

Ach, und bevor ich es vergesse: MySQL kann diesen JOIN nicht. Wie so vieles nicht. Ja, ich wiederhole mich.

 

[basementmedia]

Haha....danke ;-)
Ich hab aber eben in meinem Fall leider keine Wahlmöglichkeit.

Ich versuchs mal weiter:
Gibt es ne Möglichkeit, die Summe aus den Einzelergebnissen zu bilden, die dieser Query liefert, OHNE dazu eine "Ummantelung" mit einem weiteren SELECT Statement zu machen?

SELECT sum(stunden.stunden)
FROM stunden
LEFT JOIN projekte ON stunden.projekt_id = projekte.ID
WHERE stunden.user_id = 27 AND (stunden.datum between '2018-01-01' AND '2018-01-31')
GROUP BY stunden.projekt_id

Also quasi sowas "ähnliches" wie:

SELECT sum(sum(stunden.stunden)) ....

Was natürlich an dieser Stelle völliger Quatsch ist und ur zur Demonstration dienen soll....

 

[basementmedia]

Gibts nicht ne Möglichkeit, mit Variablen zu arbeiten, also sum=sum+(sum.stunden) oder was ähnliches.

 

[akretschmer]

probier es ;-)