Analytische Funktionen Fortgeschrittene Anwendungen Roger Troller Principal Consultant

Analytische Funktionen Fortgeschrittene Anwendungen Roger Troller Principal Consultant Trivadis AG BASEL BERN BRUGG LAUSANNE ZÜRICH DÜSSELDORF FRANKFURT A. M. 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015 FREIBURG I. BR. HAMBURG MÜNCHEN STUTTGART WIEN

AGENDA 1. Gruppenswitches § LAST_VALUE, „Vererbung“ von Werten 2. Ressourcenzuteilung zu einem Projekt § ROW_NUMBER, Running Summaries , Window Definitionen 3. Vorhersagen § Lineare Regression, Geglätteter Mittelwert (Moving Average), Saisonale Bereinigung, Trend Ermittlung, Prognose unter Berücksichtigung saisonaler Schwankungen 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Problemstellung Hallo Trivadis-World War weiss wie ich die folgende Abfrage richtig umsetzen kann: SELECT LOG_ID, analytische_funktion(FLAG) basierend auf LOG_ID FROM tabelle WHERE. . . Immer wenn ein LOG_ID = 15 wird Flag zu 'E '. . . und sobald ein LOG_ID = 16 kommt wird Flag wieder zu 'A' Beispiel mit folgenden Daten: 12 A <- implizit A, weil das erste Vorkommen weiter unten eine 15 ist 15 E <- Switch 18 E <- bleibt 16 A <- Switch 05 A <- bleibt 15 E <- Switch 32 E <- bleibt 15 E <- bleibt 16 A <- Switch usw. Danke im voraus für das Feedback. . . Lieber Gruss _______________ Antonio 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Problemstellung § § Abhängig von einem Wert einer bestimmten Spalte sollen die nachfolgenden Datensätze einer bestimmten Gruppe zugeordnet werden… § Gruppe „E“ alle Zeilen die einem Datensatz mit Wert „ 15“ in der Spalte val folgen § Gruppe „A“ alle Zeilen die einem Datensatz mit Wert „ 16“ in der Spalte val folgen § Führende Zeilen sollen abhängig vom ersten Auftreten von „ 15“ oder „ 16“ der anderen Gruppe zugefügt werden… Oder bildlich: 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Datenbasis SELECT ID, val, switch_result FROM DATA order by id; ID VAL SWITCH_RESULT --------------------------1 01 Gruppe "A". . . nächste relevante Zeile enthält 15 2 03 Gruppe "A". . . nächste relevante Zeile enthält 15 3 12 Gruppe "A". . . nächste relevante Zeile enthält 15 4 15 Wechsel auf Gruppe "E" 5 18 "E" 6 22 "E" 7 16 Wechsel auf Gruppe "A" 8 12 "A" 9 05 "A" 10 15 Wechsel auf Gruppe "E" 11 32 "E" 12 15 Auf Gruppe "E" bleiben 13 28 "E" 14 16 Wechsel auf Gruppe "A" 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Feststellen der „Switches“ SELECT ID, val, DECODE(val, '15', 'E', '16', 'A', NULL) SWITCH, switch_result FROM DATA; ID -----1 2 3 4 5 6 7 8 9 10 11 12 13 14 VAL --01 03 12 15 18 22 16 12 05 15 32 15 28 16 SWITCH_RESULT ---------------------------Gruppe "A". . . nächste relevante Zeile enthält 15 E Wechsel auf Gruppe "E" "E" Und jetzt müssten wir A Wechsel auf Gruppe "A" eigentlich nur noch "A" «erben» …von "A" vorhergehenden oder E Wechsel auf Gruppe "E" nachfolgenden Zeilen "E" E "E" A Wechsel auf Gruppe "A" 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Wer hat sich schon über LAST_VALUE gewundert? SELECT ENAME, SAL, FIRST_VALUE(SAL) OVER (ORDER BY SAL DESC) AS FIRST_SAL FROM EMP WHERE DEPTNO = 10 / ENAME SAL FIRST_SAL ----------KING 5000 CLARK 2450 5000 MILLER 1300 5000 § Und was bringt LAST_VALUE zurück? 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Wer hat sich schon über LAST_VALUE gewundert? SELECT ENAME, SAL, FIRST_VALUE(SAL) OVER (ORDER BY SAL DESC) AS FIRST_SAL , LAST_VALUE(SAL) OVER (ORDER BY SAL DESC) AS LAST_SAL FROM EMP WHERE DEPTNO = 10 / ENAME SAL FIRST_SAL LAST_SAL ---------- -----KING 5000 CLARK 2450 5000 2450 MILLER 1300 5000 1300 § Wieso? § Wie nutzen wir dies? 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

„Erben“ von oben mit IGNORE NULLS WITH DATA 2 AS (SELECT id, val, DECODE(val, '15', 'E', '16', 'A', NULL) AS switch , switch_result FROM DATA) SELECT id, val, switch, LAST_VALUE(switch IGNORE NULLS) OVER (ORDER BY id) AS switch 2 FROM data 2 / ID VAL SWITCH 2 ------ ------1 01 2 03 3 12 4 15 E E 5 18 E 6 22 E 7 16 A A 8 12 A 9 05 A 10 15 E E 11 32 E 12 15 E E 13 28 E 14 16 A A 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

„Erben“ von unten (falls notwendig) und „drehen“ WITH DATA 2 AS (SELECT id, val, DECODE(val, '15', 'E', '16', 'A', NULL) AS switch , switch_result FROM DATA) , data 3 AS (SELECT id, val, switch , LAST_VALUE(switch IGNORE NULLS) OVER (ORDER BY id) AS switch 2 FROM data 2) SELECT ID, val, switch 2 , COALESCE(switch 2 , DECODE(FIRST_VALUE(switch 2 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) , 'E', 'A', 'E')) AS switch 3 FROM data 3 / 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

„Erben“ von unten (falls notwendig) und „drehen“ ID VAL SWITCH ----- --1 01 2 03 3 12 4 15 5 18 6 22 7 16 8 12 9 05 10 15 11 32 12 15 13 28 14 16 SWITCH 2 SWITCH 3 -------A A A E E E E A A A A E E E E E A A A 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Fazit § § LAST_VALUE ist cool, wenn man es am richtigen Ort einsetzt IGNORE NULLS kann hilfreich sein § Ist auch in anderen analytischen Funktionen möglich 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

AGENDA 1. Gruppenswitches § LAST_VALUE, „Vererbung“ von Werten 2. Ressourcenzuteilung zu einem Projekt § ROW_NUMBER, Running Summaries , Window Definitionen 3. Vorhersagen § Lineare Regression, Geglätteter Mittelwert (Moving Average), Saisonale Bereinigung, Trend Ermittlung, Prognose unter Berücksichtigung saisonaler Schwankungen 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Mitarbeiter SELECT e. first_name , e. hourly_rate , e. skill , LISTAGG(TO_CHAR(month_year, 'MON') || ': ' || FLOOR(ea. percentage * 20 / 100), ', ') WITHIN GROUP (ORDER BY ea. month_year) AS available_days_month FROM employees e INNER JOIN employee_availabilities ea ON (e. first_name = ea. first_name) GROUP BY e. first_name , e. hourly_rate , e. skill / 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Mitarbeiter FIRST_NAME HOURLY_RATE SKILL AVAILABLE_DAYS ---------- -------------------------Yu 100 UI Design JAN: 16, FEB: 16, MAR: 0, APR: 16, MAY: 16, JUN: 12 Bob 80 Java JAN: 20, FEB: 20, MAR: 8, APR: 20, MAY: 20, JUN: 20 Liz 60 PL/SQL JAN: 12, FEB: 12, MAR: 12, APR: 12, MAY: 12, JUN: 12 Mia 75 Java JAN: 20, FEB: 20, MAR: 20, APR: 20, MAY: 20, JUN: 20 Bill 150 DBA JAN: 8, FEB: 8, MAR: 8, APR: 4, MAY: 8, JUN: 8 John 140 Java JAN: 16, FEB: 20, MAR: 16, APR: 12, MAY: 20, JUN: 12 Marc 85 PL/SQL JAN: 20, FEB: 20, MAR: 20, APR: 0, MAY: 0, JUN: 0 Mimi 40 PL/SQL JAN: 10, FEB: 6, MAR: 10, APR: 10, MAY: 10, JUN: 10 Fatma 90 PL/SQL JAN: 20, FEB: 20, MAR: 0, APR: 0, MAY: 10, JUN: 16 Jimmy 70 DBA JAN: 0, FEB: 8, MAR: 20, APR: 20, MAY: 20, JUN: 20 Sarah 90 UI Design JAN: 0, FEB: 0, MAR: 8, APR: 12, MAY: 12, JUN: 12 Robert 180 PL/SQL JAN: 10, FEB: 10, MAR: 10, APR: 10, MAY: 10, JUN: 10 12 rows selected 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Projekte SELECT p. project_name , p. start_date , p. end_date , pn. skill , pn. days_needed FROM projects p INNER JOIN project_needs pn ON (pn. project_name = p. project_name) / PROJECT_NAME -------HR System DWH System START_DATE -----01. 01. 2015 01. 02. 2015 END_DATE -----30. 04. 2015 30. 06. 2015 SKILL DAYS_NEEDED ----------Java 30 PL/SQL 40 UI Design 20 DBA 20 Java 10 PL/SQL 30 UI Design 30 DBA 40 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Ressourcen pro Skill und Monat SELECT e. skill, ea. month_year, e. first_name, e. hourly_rate , ea. percentage * 20 / 100 AS available_days FROM employees e INNER JOIN employee_availabilities ea ON (e. first_name = ea. first_name) where ea. percentage > 0 ORDER BY e. skill , ea. month_year / SKILL ----DBA DBA. . . MONTH_YEAR -----01. 2015 01. 02. 2015 01. 03. 2015 01. 04. 2015 FIRST_NAME HOURLY_RATE AVAILABLE_DAYS -----------Bill 150 8 Jimmy 70 8 Bill 150 8 Jimmy 70 20 Bill 150 4 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Verknüpfen der Ressourcen mit den Projekten WITH projekte AS (…) , mitarbeiter AS (…) SELECT p. project_name, p. skill, p. days_needed , e. first_name, TO_CHAR(e. month_year, 'Mon/YYYY') month_year, e. hourly_rate, e. days , SUM(e. days) OVER (PARTITION BY p. project_name, p. skill, e. first_name) AS tot_days FROM mitarbeiter e INNER JOIN projekte p ON ( p. skill = e. skill AND e. month_year BETWEEN p. start_date AND p. end_date ) ORDER BY p. skill, p. days_needed, e. first_name, e. month_year / 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Verknüpfen der Ressourcen mit den Projekten PROJECT_NAME ------HR System HR System HR System HR System. . . SKILL DAYS_NEEDED FIRST_NAME MONTH_YEAR HOURLY_RATE DAYS TOT_DAYS ----------- ---------DBA 20 Bill Jan/2015 150 8 28 DBA 20 Bill Feb/2015 150 8 28 DBA 20 Bill Mar/2015 150 8 28 DBA 20 Bill Apr/2015 150 4 28 DBA 20 Jimmy Feb/2015 70 8 48 DBA 20 Jimmy Mar/2015 70 20 48 DBA 20 Jimmy Apr/2015 70 20 48 Java 30 Bob Jan/2015 80 20 68 Java 30 Bob Feb/2015 80 20 68 Java 30 Bob Mar/2015 80 8 68 Java 30 Bob Apr/2015 80 20 68 Java 30 John Jan/2015 140 16 64 Java 30 John Feb/2015 140 20 64 Java 30 John Mar/2015 140 16 64 Java 30 John Apr/2015 140 12 64 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Definition der Zuteilungsreihenfolge § Schnellstmögliche Terminierung § Kostengünstigste Umsetzung § Kleinste Zahl involvierter Projektmitarbeiter § … 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Definition der Zuteilungsreihenfolge WITH projekte AS (…) , mitarbeiter AS (…) , mitproj_verkn AS (…) SELECT skill, days_needed , first_name, TO_CHAR(month_year, 'Mon/YYYY') month_year , hourly_rate, month_year, days, tot_days , ROW_NUMBER() OVER (PARTITION BY skill ORDER BY month_year , days , hourly_rate , first_name , tot_days) AS pick_order FROM mitproj_verkn / § Projekt wird nicht mehr gezeigt, da ja auf einzelnes Projekt (HR System) eingeschränkt wurde… 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Definition der Zuteilungsreihenfolge SKILL DAYS_NEEDED FIRST_NAME MONTH_YEAR HOURLY_RATE DAYS TOT_DAYS PICK_ORDER ----------- --------- -----DBA 20 Bill Jan/2015 150 8 28 1 DBA 20 Jimmy Feb/2015 70 8 48 2 DBA 20 Bill Feb/2015 150 8 28 3 DBA 20 Bill Mar/2015 150 8 28 4 DBA 20 Jimmy Mar/2015 70 20 48 5 DBA 20 Bill Apr/2015 150 4 28 6 DBA 20 Jimmy Apr/2015 70 20 48 7 Java 30 John Jan/2015 140 16 64 1 Java 30 Mia Jan/2015 75 20 80 2 Java 30 Bob Jan/2015 80 20 68 3 Java 30 Mia Feb/2015 75 20 80 4 Java 30 Bob Feb/2015 80 20 68 5 Java 30 John Feb/2015 140 20 64 6 Java 30 Bob Mar/2015 80 8 68 7 Java 30 John Mar/2015 140 16 64 8 Java 30 Mia Mar/2015 75 20 80 9. . . 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Ermitteln der kummulierten Ressourcen WITH projekte AS (…) , mitarbeiter AS (…) , mitproj_verkn AS (…) , zuteilung AS (…) SELECT z. skill , z. first_name , TO_CHAR(z. month_year, 'Mon/YYYY') month_year , z. hourly_rate , z. days, z. tot_days , z. pick_order , z. days_needed , NVL(SUM(days) OVER (PARTITION BY z. skill ORDER BY z. pick_order ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS previous_days FROM Zuteilung z / § Zeigt wie viele Tage wir schon konsumiert hätten, wenn wir alle vorhergehenden Picks nutzen würden. § Entscheidungsgrundlage für die Nutzung jeder Ressource 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Ermitteln der verfügbaren kummulierten Ressourcen SKILL -----DBA DBA Java Java Java … FIRST_NAME -----Bill Jimmy John Mia Bob John Mia MONTH_YEAR HOURLY_RATE DAYS TOT_DAYS PICK_ORDER DAYS_NEEDED PREVIOUS_DAYS ----------- -----------Jan/2015 150 8 28 1 20 0 Feb/2015 70 8 48 2 20 8 Feb/2015 150 8 28 3 20 16 Mar/2015 150 8 28 4 20 24 Mar/2015 70 20 48 5 20 32 Apr/2015 150 4 28 6 20 52 Apr/2015 70 20 48 7 20 56 Jan/2015 140 16 64 1 30 0 Jan/2015 75 20 80 2 30 16 Jan/2015 80 20 68 3 30 36 Feb/2015 75 20 80 4 30 56 Feb/2015 80 20 68 5 30 76 Feb/2015 140 20 64 6 30 96 Mar/2015 80 8 68 7 30 116 Mar/2015 140 16 64 8 30 124 Mar/2015 75 20 80 9 30 140 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Auswahl der Ressourcen WITH projekte AS (…) , mitarbeiter AS (…) , mitproj_verkn AS (…) , zuteilung AS (…) , aufsummiert AS (…) SELECT A. skill, A. first_name, TO_CHAR(A. month_year, 'Mon/YYYY') month_year , A. hourly_rate, A. pick_order, A. days_needed, A. tot_days, A. previous_days , CASE WHEN a. previous_days < a. days_needed THEN LEAST(a. days_needed - a. previous_days, a. days) ELSE 0 END AS days_to_pick FROM aufsummiert a / 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Auswahl der Ressourcen SKILL ----DBA DBA Java Java Java … FIRST_NAME -----Bill Jimmy John Mia Bob John Mia Bob MONTH_YEAR HOURLY_RATE PICK_ORDER DAYS_NEEDED TOT_DAYS PREVIOUS_DAYS_TO_PICK ----------- -------- ------Jan/2015 150 1 8 20 28 0 8 Feb/2015 70 2 8 20 48 8 8 Feb/2015 150 3 8 20 28 16 4 Mar/2015 150 4 8 20 28 24 0 Mar/2015 70 5 20 20 48 32 0 Apr/2015 150 6 4 20 28 52 0 Apr/2015 70 7 20 20 48 56 0 Jan/2015 140 1 16 30 64 0 16 Jan/2015 75 2 20 30 80 16 14 Jan/2015 80 3 20 30 68 36 0 Feb/2015 75 4 20 30 80 56 0 Feb/2015 80 5 20 30 68 76 0 Feb/2015 140 6 20 30 64 96 0 Mar/2015 80 7 8 30 68 116 0 Mar/2015 140 8 16 30 64 124 0 Mar/2015 75 9 20 30 80 140 0 Apr/2015 140 10 12 30 64 160 0 Apr/2015 75 11 20 30 80 172 0 Apr/2015 80 12 20 30 68 192 0 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Ermitteln der personellen Zuteilung inkl. Monat / Anzahl Tage WITH projekte AS (…) , mitarbeiter AS (…) , mitproj_verkn AS (…) , zuteilung AS (…) , aufsummiert AS (…) , auswahl AS (…) SELECT skill, days_needed , SUM(days_to_pick * 8 * hourly_rate) AS COST , LAST_DAY(MAX(month_year)) AS project_termination , LISTAGG(first_name || ' (' || TO_CHAR(month_year, 'Mon') || ': ' || days_to_pick || ')', ', ') WITHIN GROUP (ORDER BY month_year, first_name) AS personen FROM auswahl WHERE days_to_pick > 0 GROUP BY skill, days_needed / SKILL DAYS_NEEDED COST PROJECT_TERMINATION -------------DBA 20 18880 28. 02. 2015 Java 30 26320 31. 01. 2015 PL/SQL 40 28800 31. 01. 2015 UI Design 20 16000 28. 02. 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015 PERSONEN ---------------------------Bill (Jan: 8), Bill (Feb: 4), Jimmy (Feb: 8) John (Jan: 16), Mia (Jan: 14) Liz (Jan: 12), Marc (Jan: 8), Mimi (Jan: 10), Robert (Jan: 10) Yu (Jan: 16), Yu (Feb: 4)

Modifikation der Zuteilungsreihenfolge - Kostenoptimierung WITH projekte AS (…) , mitarbeiter AS (…) , mitproj_verkn AS (…) , zuteilung as (SELECT skill, days_needed , first_name, month_year, hourly_rate, days, tot_days , ROW_NUMBER() OVER (PARTITION BY skill ORDER BY hourly_rate , days , month_year , first_name) AS pick_order FROM mitproj_verkn) , aufsummiert AS (…) , auswahl AS (…) SELECT skill, days_needed, sum(days_to_pick * 8 * hourly_rate) AS COST , last_day(MAX(month_year)) AS project_termination , listagg(first_name || ' (' || to_char(month_year, 'Mon') || ': ' || days_to_pick || ')', ', ') WITHIN GROUP (ORDER BY month_year, first_name) AS personen FROM auswahl WHERE days_to_pick > 0 GROUP BY skill, days_needed / 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Optimiert nach Kosten KILL DAYS_NEEDED COST PROJECT_TERMINATION PERSONEN -----------------------------------DBA 20 11200 31. 03. 2015 Jimmy (Feb: 8), Jimmy (Mar: 12) Java 30 18000 28. 02. 2015 Mia (Jan: 20), Mia (Feb: 10) PL/SQL 40 13440 30. 04. 2015 Liz (Jan: 4), Mimi (Jan: 10), Mimi (Feb: 6) , Mimi (Mar: 10), Mimi (Apr: 10) UI Design 20 14400 30. 04. 2015 Sarah (Mar: 8), Sarah (Apr: 12) 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Kleinste Zahl involvierter Personen WITH projekte AS (…) , mitarbeiter AS (…) , mitproj_verkn AS (…) , zuteilung AS (SELECT skill, days_needed , first_name, month_year, hourly_rate, days, tot_days , ROW_NUMBER() OVER (PARTITION BY skill ORDER BY tot_days DESC , month_year ASC , hourly_rate , days , first_name) AS pick_order FROM mitproj_verkn) , aufsummiert AS (…) , auswahl AS (…) SELECT skill, days_needed, sum(days_to_pick * 8 * hourly_rate) AS COST , last_day(MAX(month_year)) AS project_termination , listagg(first_name || ' (' || to_char(month_year, 'Mon') || ': ' || days_to_pick || ')', ', ') WITHIN GROUP (ORDER BY month_year, first_name) AS personen FROM auswahl WHERE days_to_pick > 0 GROUP BY skill, days_needed / 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Kleinste Zahl involvierter Personen SKILL DAYS_NEEDED COST PROJECT_TERMINATION -------------DBA 20 11200 31. 03. 2015 Java 30 18000 28. 02. 2015 PL/SQL 40 27200 28. 02. 2015 UI Design 20 16000 28. 02. 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015 PERSONEN -------------------------Jimmy (Feb: 8), Jimmy (Mar: 12) Mia (Jan: 20), Mia (Feb: 10) Marc (Jan: 20), Marc (Feb: 20) Yu (Jan: 16), Yu (Feb: 4)

AGENDA 1. Gruppenswitches § LAST_VALUE, „Vererbung“ von Werten 2. Ressourcenzuteilung zu einem Projekt § ROW_NUMBER, Running Summaries , Window Definitionen 3. Vorhersagen § Lineare Regression, Geglätteter Mittelwert (Moving Average), Saisonale Bereinigung, Trend Ermittlung, Prognose unter Berücksichtigung saisonaler Schwankungen 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Problemstellung § Ermittlung der zu erwartenden Ticketverkäufe für verschiedene Flug-Strecken § Wird benutzt um die Kosten eines Vertrages, welcher mit Partnern abgeschlossen wird im Voraus zu berechnen… 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

«Prognosen sind eine schwierige Sache. Vor allem, wenn sie die Zukunft betreffen. » Mark Twain (1835 – 1910) 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Datenbasis ACCOUNTING_MONTH ATH_TICKETS DXB_TICKETS -------- -----01. 07. 2012 745 841 01. 08. 2012 681 643 01. 09. 2012 678 825 01. 10. 2012 689 1082 01. 11. 2012 570 1181 01. 12. 2012 514 1055 01. 2013 481 1202 01. 02. 2013 423 1203 01. 03. 2013 567 1235 01. 04. 2013 655 1233 01. 05. 2013 812 983 01. 06. 2013 803 812 01. 07. 2013 759 701 01. 08. 2013 687 841 01. 09. 2013 805 1044 01. 10. 2013 760 1261 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015 ACCOUNTING_MONTH ATH_TICKETS DXB_TICKETS -------- -----01. 11. 2013 568 1318 01. 12. 2013 535 1228 01. 2014 478 1290 01. 02. 2014 445 1192 01. 03. 2014 543 1274 01. 04. 2014 677 1268 01. 05. 2014 736 1081 01. 06. 2014 759 886 01. 07. 2014 726 584 01. 08. 2014 696 760 01. 09. 2014 844 817 01. 10. 2014 742 1161 01. 11. 2014 637 1204 29 rows selected

. 0 7 01. 20. 0 12 8 01. 20. 0 12 9 01. 20. 1 12 0 01. 20. 1 12 1 01. 20. 1 12 2 01. 20. 0 12 1 01. 20. 0 13 2 01. 20. 0 13 3 01. 20. 0 13 4 01. 20. 0 13 5 01. 20. 0 13 6 01. 20. 0 13 7 01. 20. 0 13 8 01. 20. 0 13 9 01. 20. 1 13 0 01. 20. 1 13 1 01. 20. 1 13 2 01. 20. 0 13 1 01. 20. 0 14 2 01. 20. 0 14 3 01. 20. 0 14 4 01. 20. 0 14 5 01. 20. 0 14 6 01. 20. 0 14 7 01. 20. 0 14 8 01. 20. 0 14 9 01. 20. 1 14 0 01. 20. 1 14 1. 20 14 01 Datenbasis 1400 1200 1000 800 600 ATHENS 400 DUBAI 200 0 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Einfache Prognose § REGR_SLOPE § Errechnet der Steigung pro Monat/Zielflughafen auf Grund der vorliegenden Erfahrungswerte § Umrechnen der so ermittelten Steigung in eine Prognose für das Folgejahr (aktueller Wert + 12 * Steigung) 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Einfache Prognose – REGR_SLOPE SELECT ts_order , accounting_month , ath_tickets , dxb_tickets , REGR_SLOPE(ath_tickets, ts_order) OVER (ORDER BY accounting_month RANGE BETWEEN NUMTOYMINTERVAL(23, 'MONTH') PRECEDING AND CURRENT ROW) AS ath_slope , REGR_SLOPE(dxb_tickets, ts_order) OVER (ORDER BY accounting_month RANGE BETWEEN NUMTOYMINTERVAL(23, 'MONTH') PRECEDING AND CURRENT ROW) AS dxb_slope FROM all_tickets / 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Einfache Prognose – REGR_SLOPE ACCOUNTING_MONTH ATH_TICKETS ATH_SLOPE DXB_TICKETS DXB_SLOPE -------- -----------. . . 01. 12. 2013 535 2. 8 1228 14. 25 01. 2014 478 -0. 37 1290 16. 08 01. 02. 2014 445 -3. 14 1192 15. 79 01. 03. 2014 543 -3. 88 1274 16. 44 01. 04. 2014 677 -2. 79 1268 16. 66 01. 05. 2014 736 -1. 3 1081 14. 61 01. 06. 2014 759. 09 886 10. 94 01. 07. 2014 726 2. 09 584 3. 59 01. 08. 2014 696 3. 1 760 -3. 93 01. 09. 2014 844 5. 55 817 -9 01. 10. 2014 742 6. 95 1161 -7. 83 01. 11. 2014 637 5. 96 1204 -5. 22. . . 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015 1000 900 800 700 600 500 400 300 200 100 0

Einfache Prognose – Aufrechnen auf Folgejahr SELECT ADD_MONTHS(accounting_month, 12) AS accounting_month , FLOOR(ath_tickets + 12 * ath_slope) AS ath_forecast , FLOOR(dxb_tickets + 12 * dxb_slope) AS dxb_forecast FROM slope WHERE accounting_month >= DATE '2013 -12 -01' / ACCOUNTING_MONTH ATH_TICKETS ATH_SLOPE ATH_FORECAST DXB_TICKETS DXB_SLOPE DXB_FORECAST -----------------------01. 12. 2014 535 2. 79669763 568 1228 14. 245614 1398 01. 2015 478 -0. 366666667 473 1290 16. 0842105 1483 01. 02. 2015 445 -3. 13759398 407 1192 15. 793985 1381 01. 03. 2015 543 -3. 87532468 496 1274 16. 438961 1471 01. 04. 2015 677 -2. 7944664 643 1268 16. 6572558 1467 01. 05. 2015 736 -1. 30039526 720 1081 14. 6096838 1256 01. 06. 2015 759. 0930434783 760 886 10. 9369565 1017 01. 07. 2015 726 2. 08565217 751 584 3. 58869565 627 01. 08. 2015 696 3. 09913043 733 760 -3. 92826087 712 01. 09. 2015 844 5. 54695652 910 817 -8. 99869565 709 01. 10. 2015 742 6. 95 825 1161 -7. 82869565 1067 01. 11. 2015 637 5. 96347826 708 1204 -5. 22130435 1141 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015 11. 1. 2015 9. 1. 2015 7. 1. 2015 5. 1. 2015 3. 1. 2015 11. 1. 2014 9. 1. 2014 7. 1. 2014 5. 1. 2014 3. 1. 2014 11. 1. 2013 9. 1. 2013 7. 1. 2013 5. 1. 2013 3. 1. 2013 11. 1. 2012 9. 1. 2012 7. 1. 2012 5. 1. 2012 3. 1. 2012 1. 1. 2012 Einfache Prognose – Aufrechnen auf Folgejahr 1600 1400 1200 1000 800 ATH Tickets 600 ATH Forecast DXB Tickets 400 DXB Forecast 200 0

Gut aber…was hier fehlt: § Geglätteter Mittelwert (+/- 6 Monate falls vorhanden) § Saisonale Abweichungen errechnen § Trend auf saisonalbereinigten Werten § Ermittlung der Prognose unter Berücksichtigung der Saisonalität Zum Thema Trendermittlung: http: //people. duke. edu/~rnau/411 home. htm 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Geglätteter Mittelwert über +/- 6 Monate SELECT t. * , ROUND(CASE WHEN ts_order BETWEEN 8 AND 34 -- where we have some data THEN ( NVL(AVG(ath_tickets) OVER (ORDER BY accounting_month ROWS BETWEEN 5 PRECEDING AND + NVL(AVG(ath_tickets) OVER (ORDER BY accounting_month ROWS BETWEEN 6 PRECEDING AND ELSE NULL END, 2) AS ath_tickets_MA , ROUND(CASE WHEN ts_order BETWEEN 8 AND 34 -- where we have some data THEN ( NVL(AVG(dxb_tickets) OVER (ORDER BY accounting_month ROWS BETWEEN 5 PRECEDING AND + NVL(avg(dxb_tickets) OVER (ORDER BY accounting_month ROWS BETWEEN 6 PRECEDING AND ELSE NULL END, 2) AS dxb_tickets_ma FROM all_tickets t / 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015 6 FOLLOWING), 0) 5 FOLLOWING), 0)) / 2

2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015 11. 1. 2015 9. 1. 2015 7. 1. 2015 5. 1. 2015 3. 1. 2015 11. 1. 2014 9. 1. 2014 7. 1. 2014 5. 1. 2014 3. 1. 2014 11. 1. 2013 9. 1. 2013 7. 1. 2013 5. 1. 2013 3. 1. 2013 11. 1. 2012 9. 1. 2012 7. 1. 2012 5. 1. 2012 3. 1. 2012 1. 1. 2012 Geglätteter Mittelwert über +/- 6 Monate 1400 1200 1000 800 ATH 600 ATH moving average 400 DXB moving average 200 0

Saisonalität ermitteln SELECT. . . , AVG(ath_tickets / ath_tickets_ma) OVER (PARTITION BY EXTRACT(MONTH FROM accounting_month)) AS ath_tickets_seasonality , AVG(dxb_tickets / dxb_tickets_ma) OVER (PARTITION BY EXTRACT(MONTH FROM accounting_month)) AS dxb_tickets_seasonality FROM moving_average ma / ACCOUNTING_MONTH ATH_TICKETS_SEASONALITY DXB_TICKETS_SEASONALITY -----------------------01. 2012. 749245135 1. 17077902 01. 02. 2012. 678112011 1. 12976203 01. 03. 2012. 86225277 1. 17932098 01. 04. 2012 1. 0274037 1. 17364446 01. 05. 2012 1. 18917161. 966722235 01. 06. 2012 1. 19198818. 795593601 01. 07. 2012 1. 12060922. 603113681 01. 08. 2012 1. 05605146. 722499334 01. 09. 2012 1. 18123882. 863750895 01. 10. 2012 1. 10973861 1. 13699479 01. 11. 2012. 902303424 1. 16035621 01. 12. 2012. 822681155 1. 06206559 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Saisonalität § Beschreibt den Durchschnitt der Faktoren (monatliche Tickets / geglätteter Mittelwert) für jeden Monat § Dies erlaubt uns später den ermittelten saisonbereinigten Trend für die Ermittlung der Prognose mit dem monatsspezifischen Saisonalitätsfaktoren zu multiplizieren. § Identisch für gleichen Monat verschiedener Jahre 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Saisonale Bereinigung SELECT. . . , ath_tickets / ath_tickets_seasonality AS ath_tickets_deseasonalized , dxb_tickets / dxb_tickets_seasonality AS dxb_tickets_deseasonalized. . . FROM seasonality s / ACCOUNTING_MONTH ATH_TICKETS_DESEASONALIZED DXB_TICKETS_DESEASONALIZED ---------------- ------------------. . . 01. 07. 2012 745 664. 816949 841 1394. 43031 01. 08. 2012 681 644. 854937 643 889. 966219 01. 09. 2012 678 573. 973686 825 955. 136492 01. 10. 2012 689 620. 866929 1082 951. 631447 01. 11. 2012 570 631. 716543 1181 1017. 79091 01. 12. 2012 514 624. 786403 1055 993. 347314 01. 2013 481 641. 979477 1202 1026. 66684 01. 02. 2013 423 623. 79075 1203 1064. 82601 01. 03. 2013 567 657. 579795 1235 1047. 21278 01. 04. 2013 655 637. 529335 1233 1050. 5737. . . 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015 11. 1. 2015 9. 1. 2015 7. 1. 2015 5. 1. 2015 3. 1. 2015 11. 1. 2014 9. 1. 2014 7. 1. 2014 5. 1. 2014 3. 1. 2014 11. 1. 2013 9. 1. 2013 7. 1. 2013 5. 1. 2013 3. 1. 2013 11. 1. 2012 9. 1. 2012 7. 1. 2012 5. 1. 2012 3. 1. 2012 1. 1. 2012 Saisonale Bereinigung 1600 1400 1200 1000 800 ATH 600 ATH deseasonalized DXB 400 DXB deseasonalized 200 0

Trend Ermittlung SELECT. . . , REGR_INTERCEPT(ath_tickets_deseasonalized, ts_order) OVER () + ts_order * REGR_SLOPE(ath_tickets_deseasonalized, ts_order) OVER () AS ath_tickets_trend , REGR_INTERCEPT(dxb_tickets_deseasonalized, ts_order) OVER () + ts_order * REGR_SLOPE(dxb_tickets_deseasonalized, ts_order) OVER () AS dxb_tickets_trend FROM deseasonalized ds / ACCOUNTING_MONTH ATH_TICKETS_DESEASONALIZED ATH_TICKETS_TREND DXB_TICKETS_DESEASONALIZED DXB_TICKETS_TREND -------------------------- --------01. 2012 626. 427168 1063. 49642 01. 02. 2012 627. 601229 1063. 72342 01. 03. 2012 628. 775291 1063. 95042 01. 04. 2012 629. 949353 1064. 17742 01. 05. 2012 631. 123415 1064. 40442 01. 06. 2012 632. 297476 1064. 63143 01. 07. 2012 664. 816949 633. 471538 1394. 43031 1064. 85843 01. 08. 2012 644. 854937 634. 6456 889. 966219 1065. 08543 01. 09. 2012 573. 973686 635. 819662 955. 136492 1065. 31243 01. 10. 2012 620. 866929 636. 993723 951. 631447 1065. 53944 01. 11. 2012 631. 716543 638. 167785 1017. 79091 1065. 76644. . . 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015 11. 1. 2015 9. 1. 2015 7. 1. 2015 5. 1. 2015 3. 1. 2015 11. 1. 2014 9. 1. 2014 7. 1. 2014 5. 1. 2014 3. 1. 2014 11. 1. 2013 9. 1. 2013 7. 1. 2013 5. 1. 2013 3. 1. 2013 11. 1. 2012 9. 1. 2012 7. 1. 2012 5. 1. 2012 3. 1. 2012 1. 1. 2012 Trend Ermittlung 1600 1400 1200 1000 800 ATH deseasonalized 600 ATH Trend DXB deseasonalized 400 DXB Trend 200 0

Prognose unter Berücksichtigung saisonaler Schwankungen SELECT. . . , ROUND(ath_tickets_trend * ath_tickets_seasonality) AS ath_tickes_forecast , ROUND(dxb_tickets_trend * dxb_tickets_seasonality) AS dxb_tickets_forecast FROM trend t / ACCOUNTING_MONTH ATH_TICKETS ATH_TICKES_FORECAST DXB_TICKETS_FORECAST ---------------- -----------. . . 01. 06. 2014 759 787 886 851 01. 07. 2014 726 741 584 646 01. 08. 2014 696 700 760 773 01. 09. 2014 844 784 817 925 01. 10. 2014 742 738 1161 1218 01. 11. 2014 637 601 1204 1243 01. 12. 2014 549 1138 01. 2015 501 1255 01. 02. 2015 454 1211 01. 03. 2015 579 1264 01. 04. 2015 691 1259. . . 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015

Prognose unter Berücksichtigung saisonaler Schwankungen 1400 1200 1000 800 ATH Forecast 600 DXB 400 DXB Forecast 200 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015 15 1. 20 20 15 11. 9. 1. 20 15 7. 1. 5. 3. 1. 20 15 14 1. 1. 20 20 14 11. 9. 7. 1. 20 14 5. 1. 20 14 1. 3. 1. 20 14 20 13 11. 20 13 9. 1. 20 13 7. 1. 5. 3. 1. 20 13 12 1. 1. 20 20 12 11. 1. 9. 7. 1. 20 12 0

Vergleiche einfache Prognose / erweiterte Prognose 1600 1400 1200 ATH 1000 ATH Forecast 800 ATH simple DXB 600 DXB Forecast 400 DXB simple 200 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015 15 1. 1. 20 20 15 9. 1. 20 15 7. 1. 20 15 5. 3. 1. 20 15 14 1. 1. 11. 9. 1. 20 20 14 1. 7. 5. 1. 20 14 3. 1. 20 14 13 1. 1. 11. 20 20 13 9. 1. 7. 1. 20 13 5. 3. 1. 20 13 12 1. 20 1. 1. 9. 7. 1. 20 12 0

Besten Dank Roger Troller Principal Consultant roger. troller@trivadis. com BASEL BERN BRUGG LAUSANNE ZÜRICH DÜSSELDORF FRANKFURT A. M. 2015 © Trivadis Analytische Funktionen - Fortgeschrittene Anwendung 12. 02. 2015 FREIBURG I. BR. HAMBURG MÜNCHEN STUTTGART WIEN