=====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 ===


{{:prog:ana:oracle_analytic_functions_moving_window_v01.png?500|Übersicht Window Function Oracle ANA}}


=== 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:

<code sql>
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
/

</code>

=== Quellen ===

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