Analytische Funktionen
Werbung
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 SWITCH_RESULT
------ ------------------------------------------------Gruppe "A" ... nächste relevante Zeile enthält 15
Gruppe "A" ... nächste relevante Zeile enthält 15
Gruppe "A" ... nächste relevante Zeile enthält 15
E
Wechsel auf Gruppe "E"
"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"
"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
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
5000
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 DATA2 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 switch2
FROM data2
/
ID VAL SWITCH SWITCH2
---------- --- ------ ------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 DATA2 AS (SELECT id, val, DECODE(val,'15','E','16','A',NULL) AS switch
, switch_result
FROM DATA)
,data3 AS (SELECT id, val, switch
, LAST_VALUE(switch IGNORE NULLS) OVER (ORDER BY id) AS switch2
FROM data2)
SELECT ID, val, switch, switch2
, COALESCE(switch2
,DECODE(FIRST_VALUE(switch2 IGNORE NULLS)
OVER (ORDER BY ID
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)
,'E','A','A','E')) AS switch3
FROM data3
/
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
SWITCH2 SWITCH3
------ ------- ------A
A
A
E
E
E
E
E
E
E
A
A
A
A
A
A
A
E
E
E
E
E
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
HR System
HR System
HR System
DWH System
DWH System
DWH System
DWH System
START_DATE
---------01.01.2015
01.01.2015
01.01.2015
01.01.2015
01.02.2015
01.02.2015
01.02.2015
01.02.2015
END_DATE
---------30.04.2015
30.04.2015
30.04.2015
30.04.2015
30.06.2015
30.06.2015
30.06.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
DBA
DBA
DBA
DBA
...
MONTH_YEAR
---------01.01.2015
01.02.2015
01.02.2015
01.03.2015
01.03.2015
01.04.2015
FIRST_NAME HOURLY_RATE
AVAILABLE_DAYS
---------- ----------- -------------------Bill
150
8
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
HR System
HR System
HR System
HR System
HR System
HR System
HR System
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 ein 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
DBA
DBA
DBA
DBA
DBA
Java
Java
Java
Java
Java
Java
Java
Java
Java
…
FIRST_NAME
---------Bill
Jimmy
Bill
Bill
Jimmy
Bill
Jimmy
John
Mia
Bob
Mia
Bob
John
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, 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
DBA
DBA
DBA
DBA
DBA
Java
Java
Java
Java
Java
Java
Java
Java
Java
Java
Java
Java
…
FIRST_NAME
---------Bill
Jimmy
Bill
Bill
Jimmy
Bill
Jimmy
John
Mia
Bob
Mia
Bob
John
Bob
John
Mia
John
Mia
Bob
MONTH_YEAR HOURLY_RATE PICK_ORDER DAYS DAYS_NEEDED TOT_DAYS PREVIOUS_DAYS 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
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
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.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.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
2015 © Trivadis
Analytische Funktionen - Fortgeschrittene Anwendung
12.02.2015
01.11.2014
01.10.2014
01.09.2014
01.08.2014
01.07.2014
01.06.2014
01.05.2014
01.04.2014
01.03.2014
01.02.2014
01.01.2014
01.12.2013
01.11.2013
01.10.2013
01.09.2013
01.08.2013
01.07.2013
01.06.2013
01.05.2013
01.04.2013
01.03.2013
01.02.2013
01.01.2013
01.12.2012
01.11.2012
01.10.2012
01.09.2012
01.08.2012
01.07.2012
Datenbasis
1400
1200
1000
800
600
ATHENS
400
DUBAI
200
0
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.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
...
1000
900
800
700
600
500
400
300
200
100
0
2015 © Trivadis
Analytische Funktionen - Fortgeschrittene Anwendung
12.02.2015
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.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
1/1/2015
11/1/2014
9/1/2014
7/1/2014
5/1/2014
3/1/2014
1/1/2014
11/1/2013
9/1/2013
7/1/2013
5/1/2013
3/1/2013
1/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
400
DXB Tickets
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/411home.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
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
1/1/2015
11/1/2014
9/1/2014
7/1/2014
5/1/2014
3/1/2014
1/1/2014
11/1/2013
9/1/2013
7/1/2013
5/1/2013
3/1/2013
1/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
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.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 ATH_TICKETS_DESEASONALIZED DXB_TICKETS 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.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
1/1/2015
11/1/2014
9/1/2014
7/1/2014
5/1/2014
3/1/2014
1/1/2014
11/1/2013
9/1/2013
7/1/2013
5/1/2013
3/1/2013
1/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
400
DXB
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.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
1/1/2015
11/1/2014
9/1/2014
7/1/2014
5/1/2014
3/1/2014
1/1/2014
11/1/2013
9/1/2013
7/1/2013
5/1/2013
3/1/2013
1/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
400
DXB deseasonalized
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 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.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
ATH Forecast
600
DXB
400
DXB Forecast
200
0
2015 © Trivadis
Analytische Funktionen - Fortgeschrittene Anwendung
12.02.2015
Vergleiche einfache Prognose / erweiterte Prognose
1600
1400
1200
ATH
1000
ATH Forecast
800
ATH simple
600
DXB
DXB Forecast
400
DXB simple
200
0
2015 © Trivadis
Analytische Funktionen - Fortgeschrittene Anwendung
12.02.2015
Besten Dank
Roger Troller
Principal Consultant
[email protected]
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
