Hallo (und noch schönesneues Jahr )
ich habe mir ein recht komplexes SQL-Statement gebastelt und brauche noch einen guten Tip, um das was ich damit bewerkstelligen möchte zu erreichen.
Zum Verständnis: ein Projekt kann viele Aufgaben haben. Diese Aufgaben haben so etwas wie einen geplanten Zeitwert in Tagen, der mittels nicht ganz untrivialem SQL ermittelt wird. Im Grunde funktioniert es bis auf einen kleinen Teil schon ganz gut und zwar meine ich den Teil, wo ich ein sogenanntes “running total” fabriziere, dass ich gerne bei einem Wechsel auf ein neues “Projekt” zurück auf 0 setzen möchte.
Mein Ergebnis sieht zur Zeit mit dem Beispiel von 2 Projekten wie folgt aus:
Mein Ziel ist es das Anfangsdatum als auch das Enddatum einer Aufgabe in die Ausgabe zu bringen. Irgendwie brauche ich Denkanstöße,da ich nicht genau weiß wie ich es gestalten muss.
Hier ist mein aktuelles Statement:
select ordnungszahl
, zonk.projekt_id
, start_datum
, aufgaben_id
, aufwand_geschaetzt
, aufgabenzeit_geplant
,sum(aufgabenzeit_geplant) over (order by zonk.projekt_id,ordnungszahl
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
, start_datum + sum(aufgabenzeit_geplant) over (order by zonk.projekt_id, ordnungszahl
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )::integer aufgaben_enddatum
from (
select
ordnungszahl, projekt_id, aufgaben_id, aufwand_geschaetzt,
case when aufwand_geschaetzt > (select greatest(0, sum(aufwand_geplant))
from "projekt-skill-verwalter".aufgabe_mitarbeiter
where aufgaben_id = a1.aufgaben_id)
then CEILING (aufwand_geschaetzt::float / (select greatest(1, count(*))
from "projekt-skill-verwalter".aufgabe_mitarbeiter
where aufgaben_id = a1.aufgaben_id))
else (select max(aufwand_geplant)
from "projekt-skill-verwalter".aufgabe_mitarbeiter
where aufgaben_id = a1.aufgaben_id)
end ::integer as aufgabenzeit_geplant
from "projekt-skill-verwalter".aufgabe a1
where aufgaben_id in (select aufgaben_id
from "projekt-skill-verwalter".aufgabe a2
where a2.projekt_id in (select projekt_id
from "projekt-skill-verwalter".projekt)
and a2.ordnungszahl <= (select ordnungszahl
from "projekt-skill-verwalter".aufgabe
where projekt_id = a1.projekt_id
and aufgaben_id
in (select aufgaben_id
from "projekt-skill-verwalter".projekt
where ordnungszahl =
(select max(ordnungszahl)
from "projekt-skill-verwalter".aufgabe
where projekt_id = a1.projekt_id
)
)
)
)
order by projekt_id, ordnungszahl
) as zonk
inner join "projekt-skill-verwalter".projekt
on zonk.projekt_id = projekt.projekt_id
order by projekt_id, ordnungszahl
Nebenbei habe ich bei einigen Experimenten festgestellt, dass ich offenbar die Spalten, mit denen ich diese Additionen mache trotz Aliass-Benennungen nicht weiter im Selektstatement refferenzieren kann. Bekomme dann immer den Fehler er würde die Spalte nicht kennen.. Gibt es da ein Mittel für?
Aber wichtiger ist natürlich eine Idee für Zurücksetzen der Summe bei wechsel des Projekts in der Ergebnistabelle.
für das ‘zurücksetzen’ gibt es bei Window-Funktionen das PARTITION BY, hier nach Deiner Projekt_id. Auf Aliase, die Du im Select vergibst, kannst Du NICHT im selben Level der Abfrage zugreifen. Das ist so per SQL-Spec. Nutze Subselects oder CTEs - oder wiederhole den Ausdruck einfach.
Ja, danke für den Hinweis. Habe eben auch nochmal gegoogelt und bin auf das zauberwort PARTITION gestoßen, und siehe da ich bin ein Schritt weiter.
Aktuell ist mein Statement nun so aus:
select ordnungszahl
, zonk.projekt_id
, start_datum
, aufgaben_id
, aufwand_geschaetzt
, aufgabenzeit_geplant
,sum(aufgabenzeit_geplant) over (partition by zonk.projekt_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
, start_datum + sum(aufgabenzeit_geplant) over (partition by zonk.projekt_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )::integer aufgaben_enddatum
from (
select
ordnungszahl, projekt_id, aufgaben_id, aufwand_geschaetzt,
case when aufwand_geschaetzt > (select greatest(0, sum(aufwand_geplant))
from "projekt-skill-verwalter".aufgabe_mitarbeiter
where aufgaben_id = a1.aufgaben_id)
then CEILING (aufwand_geschaetzt::float / (select greatest(1, count(*))
from "projekt-skill-verwalter".aufgabe_mitarbeiter
where aufgaben_id = a1.aufgaben_id))
else (select max(aufwand_geplant)
from "projekt-skill-verwalter".aufgabe_mitarbeiter
where aufgaben_id = a1.aufgaben_id)
end ::integer as aufgabenzeit_geplant
from "projekt-skill-verwalter".aufgabe a1
where aufgaben_id in (select aufgaben_id
from "projekt-skill-verwalter".aufgabe a2
where a2.projekt_id in (select projekt_id
from "projekt-skill-verwalter".projekt)
and a2.ordnungszahl <= (select ordnungszahl
from "projekt-skill-verwalter".aufgabe
where projekt_id = a1.projekt_id
and aufgaben_id
in (select aufgaben_id
from "projekt-skill-verwalter".projekt
where ordnungszahl =
(select max(ordnungszahl)
from "projekt-skill-verwalter".aufgabe
where projekt_id = a1.projekt_id
)
)
)
)
order by projekt_id, ordnungszahl
) as zonk
inner join "projekt-skill-verwalter".projekt
on zonk.projekt_id = projekt.projekt_id
order by projekt_id, ordnungszahl
Nun frage ich mich allerdings noch wie ich es erreichen kann, dass ich anstelle des Anfangsdatums der nächsten Aufgabe, das Startdatum der aktuellen Aufgabe bekomme. Irgendwie müsste ich dann in der Window-Funktion sagen, dass eine Zeile später der Wert zu addieren sei.
Bin bei weiterem Stöbern auf LAG() gestoßen. Aber müsste man dann nicht LAG mit SUM kombinieren? Wie ginge das syntaktisch?
Hier ist mein Stand mit Start- und Enddatum. Wochenenden werden hier z.Z. noch nicht berücksichtigt, das wäre dann ein späterer Schritt, dass die Aufgabenendzeitpunkte entsprechend der Wochenenden innerhalb der Aufgabenzeitspanne nach hinten verlegt werden müssten.
select ordnungszahl
, zonk2.projekt_id
, start_datum
, aufgaben_id
, aufwand_geschaetzt
, aufgabenzeit_geplant_pre
, start_datum + sum(aufgabenzeit_geplant_pre) over (partition by zonk2.projekt_id
order by ordnungszahl)::integer aufgaben_startdatum
, start_datum - 1 + sum(aufgabenzeit_geplant) over (partition by zonk2.projekt_id
order by ordnungszahl)::integer aufgaben_enddatum
from
(select ordnungszahl
, zonk1.projekt_id
, start_datum
, aufgaben_id
, aufwand_geschaetzt
, aufgabenzeit_geplant
,lag(aufgabenzeit_geplant,1, 0) over (partition by zonk1.projekt_id
order by ordnungszahl) aufgabenzeit_geplant_pre
from (
select
ordnungszahl, projekt_id, aufgaben_id, aufwand_geschaetzt,
case when aufwand_geschaetzt > (select greatest(0, sum(aufwand_geplant))
from "projekt-skill-verwalter".aufgabe_mitarbeiter
where aufgaben_id = a1.aufgaben_id)
then CEILING (aufwand_geschaetzt::float / (select greatest(1, count(*))
from "projekt-skill-verwalter".aufgabe_mitarbeiter
where aufgaben_id = a1.aufgaben_id))
else (select max(aufwand_geplant)
from "projekt-skill-verwalter".aufgabe_mitarbeiter
where aufgaben_id = a1.aufgaben_id)
end ::integer as aufgabenzeit_geplant
from "projekt-skill-verwalter".aufgabe a1
where aufgaben_id in (select aufgaben_id
from "projekt-skill-verwalter".aufgabe a2
where a2.projekt_id in (select projekt_id
from "projekt-skill-verwalter".projekt)
and a2.ordnungszahl <= (select ordnungszahl
from "projekt-skill-verwalter".aufgabe
where projekt_id = a1.projekt_id
and aufgaben_id
in (select aufgaben_id
from "projekt-skill-verwalter".projekt
where ordnungszahl =
(select max(ordnungszahl)
from "projekt-skill-verwalter".aufgabe
where projekt_id = a1.projekt_id
)
)
)
)
order by projekt_id, ordnungszahl
) as zonk1
inner join "projekt-skill-verwalter".projekt
on zonk1.projekt_id = projekt.projekt_id
order by projekt_id, ordnungszahl
) as zonk2