Gunther Oracle APEX und Datenbank Wiki

Oracle Analytic Function - Beispiel 5 - "Gleitende Summe" und Kumulative Werte berechnen

Mit Hilfe einer Fensterfunktion soll, ausgehende vom aktuellen Datensatz, die Summe der letzten Monate ( mit „ROWS BETWEEN start_point AND end_point“) bzw. die gesamte kumulative Summe (mit „ROWS BETWEEN unbounded preceding AND CURRENT ROW“ ) berechnet werden.

Übersicht

Beispiel Cumlative aufsummieren

Mit „SUM(<Spalte>) OVER (ORDER BY TRUNC(<Treibendes Datum>) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)“ kann der cumulative Wert einer Spalte (über das Datum getrieben) ermittelt werden.

Zum Beispiel für die Darstellung von S Kurven in Primavera:

SELECT prj.id
    , trunc(actsp.startdate)                       AS actsp_startdate
    , to_char(trunc(actsp.startdate),'dd.mm.yyyy') AS displaydate
      -- BL planned labor units summed by date
    , SUM(actsp.baselineplannedlaborunits) AS sum_blplannedlaborunits
      -- BL planned labor units - running totals by date
    , SUM(SUM(actsp.baselineplannedlaborunits)) OVER (ORDER BY trunc(actsp.startdate) ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS cum_blplannedlaborunits
      -- Actual labor units summed by date
    , SUM(actsp.actuallaborunits) AS sum_actuallaborunits
      -- Actual labor units - running totals by date
    , SUM(SUM(actsp.actuallaborunits)) OVER(ORDER BY trunc(actsp.startdate) ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS cum_actuallaborunits
      -- EV labor units summed by date
    , SUM(actsp.earnedvaluelaborunits) AS sum_earnedvaluelaborunits
      -- EV labor units - running totals by date
    , SUM(SUM(actsp.earnedvaluelaborunits)) OVER (ORDER BY trunc(actsp.startdate) ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS cum_earnedvaluelaborunits
FROM  project prj -- for Securtiy use P6 Tables!!
    , activityspread actsp
WHERE prj.id LIKE (:p_project_id)
  AND prj.objectid = actsp.projectobjectid
  AND actsp.startdate BETWEEN to_date(:p_startdate,'dd.mm.yyyy') AND to_date(:p_enddate,'dd.mm.yyyy')
GROUP BY trunc(actsp.startdate),prj.id
ORDER BY actsp_startdate
/

Quellen

siehe auch : https://oracle-base.com/articles/misc/analytic-functions