prog:oracle_analytic_functions_window_function
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
prog:oracle_analytic_functions_window_function [2015/07/26 16:16] – [Oracle Analytic Function - Beispiel 5 - Gleitende Summe und Kumulative Werte berechnen] gpipperr | prog:oracle_analytic_functions_window_function [2015/07/26 16:17] (aktuell) – [Beispiel Cumlative aufsummieren] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | =====Oracle Analytic Function - Beispiel 5 - " | ||
+ | |||
+ | Mit Hilfe einer Fensterfunktion soll, ausgehende vom aktuellen Datensatz, die Summe der letzten Monate ( mit "ROWS BETWEEN start_point AND end_point" | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | === Übersicht === | ||
+ | |||
+ | |||
+ | {{: | ||
+ | |||
+ | |||
+ | === Beispiel Cumlative aufsummieren=== | ||
+ | |||
+ | Mit **" | ||
+ | |||
+ | Zum Beispiel für die Darstellung von S Kurven in Primavera: | ||
+ | |||
+ | <code sql> | ||
+ | select prj.id | ||
+ | , trunc(actsp.startdate) | ||
+ | , to_char(trunc(actsp.startdate),' | ||
+ | -- 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 (: | ||
+ | and prj.objectid = actsp.projectobjectid | ||
+ | and actsp.startdate between to_date(: | ||
+ | group by trunc(actsp.startdate), | ||
+ | order by actsp_startdate | ||
+ | / | ||
+ | |||
+ | </ | ||
+ | |||
+ | === Quellen === | ||
+ | |||
+ | siehe auch : https:// | ||
prog/oracle_analytic_functions_window_function.txt · Zuletzt geändert: 2015/07/26 16:17 von gpipperr