Analytische functies 18 mei 2005 1 Onderwerpen Introductievoorbeeld
Analytische functies 18 mei 2005 1
Onderwerpen • Introductievoorbeeld • Kenmerken / achterliggende gedachten • Syntax • Praktijkvoorbeelden 2
Introductievoorbeeld Toon mij van iedere medewerker - zijn naam - de afdeling waarin hij werkzaam is - zijn salaris - het cumulatieve salaris per afdeling - percentage van salaris binnen de afdeling - percentage van salaris binnen het bedrijf af 1 a. sql gesorteerd op afdeling en salaris af 1 b. sql af 1 c. sql 3
Kenmerken • Sinds 8. 1. 6 • Weinig gebruikt, toch erg krachtig • Lijken soms op bekende aggregatiefuncties SUM, COUNT, MAX e. d. • …maar aggregeren niet • voorkomen meerdere benaderingen van dezelfde tabel, b. v. self joins af 2. sql 4
Overzicht • Denk niet in rijen … EMPNO -----7782 7839 7934 7876 ENAME -----CLARK KING MILLER ADAMS JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------MANAGER 7839 09 -06 -81 2450 10 PRESIDENT 17 -11 -81 5000 10 CLERK 7782 23 -01 -82 1300 10 CLERK 7788 23 -05 -87 1100 20 7902 FORD ANALYST 7566 03 -12 -81 3000 20 7566 7788 7369 7499 7698 7900 7654 7844 7521 MANAGER ANALYST CLERK SALESMAN MANAGER CLERK SALESMAN 7839 7566 7902 7698 7839 7698 2975 3000 800 1600 2850 950 1250 1500 1250 20 20 20 30 30 30 JONES SCOTT SMITH ALLEN BLAKE JAMES MARTIN TURNER WARD 02 -04 -81 19 -04 -87 17 -12 -80 20 -02 -81 01 -05 -81 03 -12 -81 28 -09 -81 08 -09 -81 22 -02 -81 300 1400 0 500 5
Overzicht … maar in verzamelingen EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ----------7782 7839 7934 7876 7902 7566 7788 7369 7499 7698 7900 7654 7844 7521 CLARK KING MILLER ADAMS FORD JONES SCOTT SMITH ALLEN BLAKE JAMES MARTIN TURNER WARD MANAGER PRESIDENT CLERK ANALYST MANAGER ANALYST CLERK SALESMAN MANAGER CLERK SALESMAN 7839 09 -06 -81 17 -11 -81 7782 23 -01 -82 7788 23 -05 -87 7566 03 -12 -81 7839 02 -04 -81 7566 19 -04 -87 7902 17 -12 -80 7698 20 -02 -81 7839 01 -05 -81 7698 03 -12 -81 7698 28 -09 -81 7698 08 -09 -81 7698 22 -02 -81 2450 5000 1300 1100 3000 2975 3000 800 1600 2850 950 1250 1500 1250 300 1400 0 500 10 10 10 20 20 20 30 30 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ----------7782 CLARK 7839 KING 7934 MILLER MANAGER PRESIDENT CLERK 7839 09 -06 -81 17 -11 -81 7782 23 -01 -82 2450 5000 1300 10 10 10 7876 7902 7566 7788 7369 ADAMS FORD JONES SCOTT SMITH CLERK ANALYST MANAGER ANALYST CLERK 7788 7566 7839 7566 7902 23 -05 -87 03 -12 -81 02 -04 -81 19 -04 -87 17 -12 -80 1100 3000 2975 3000 800 20 20 20 7499 7698 7900 7654 7844 7521 ALLEN BLAKE JAMES MARTIN TURNER WARD SALESMAN MANAGER CLERK SALESMAN 7698 7839 7698 20 -02 -81 01 -05 -81 03 -12 -81 28 -09 -81 08 -09 -81 22 -02 -81 1600 2850 950 1250 1500 1250 300 1400 0 500 30 30 30 6
Evaluatievolgorde • • Als laatste Dus zelfs na HAVING, rownum … Behalve: voor de ORDER BY Wil je ze in WHERE-clausule hebben nesten (inline view) af 3 a. sql af 3 b. sql 7
Syntax <functie>(<argument>, …) OVER (<partitieclausule> <ordeningsclausule> <vensterclausule> ) 8
De functies LAG COUNT CORR LEAD SUM COVAR_POP FIRST_VALUE MAX STDDEV LAST_VALUE MIN STDDEV_POP RANK AVG STDDEV_SAMP DENSE_RANK NTILE VAR_POP RATIO_TO_REPORT CUME_DIST VAR_SAMP ROW_NUMBER VARIANCE PERCENT_RANK REGR_x (9 stuks) 9
Partitieclausule PARTITION BY <expressie>, … Beetje vergelijkbaar met SQL*Plus commando BREAK af 4. sql 10
Ordeningsclausule ORDER BY expressie <ASC|DESC> <NULLS FIRST|NULLS LAST>, … Verandert werking van functie van een totaalfunctie naar een lopend totaal af 5. sql 11
Voorbeeld 1: Top N • Met name interessant voor BI-zoekvragen • M. b. v. analytische functies COUNT, RANK of DENSE_RANK af 6. sql 12
Voorbeeld 2: etappe-uitslag 1. David Zabriskie (VSt) 2. Ivan Basso (Ita) 3. Paolo Savoldelli (Ita) 4. Marzio Bruseghin (Ita) 5. Serguei Gonchar (Oek) 6. Vladimir Karpets (Rus) 7. Markus Fothen (Dui) 8. Thomas Dekker (Ned) 9. Jan Hruska (Tsj) 10. Danilo di Luca (Ita) 0. 58: 31 + 0: 17 + 0: 44 + 0: 48 z. t. + 1: 07 + 1: 15 + 1: 23 + 1: 34 z. t. af 7. sql 13
Voorbeeld 3: perioden Eis: niet-overlappend en aansluitend • Variant 1) alleen begindatum SELECT. . . FROM a WHERE begindatum < b_peildatum AND NOT EXISTS ( SELECT ‘grotere begindatum’ FROM a a 2 WHERE “a join a 2” AND begindatum < b_peildatum AND a 2. begindatum > a. begindatum ) • Variant 2) begindatum en einddatum veel triggercode om eis af te dwingen af 8. sql 14
Voorbeeld 4: vorig voorschotbedrag • Per klant iedere maand een nota met notaregel met het voorschotbedrag • Per klant eenmaal per jaar een nota met regel voor periodieke afrekening • Vorig voorschotbedrag = het bedrag van de notaregel die qua datum voor de laatste periodieke afrekening ligt af 9. sql 15
Voorbeeld 5: Groeperen TIJD HOEVEELHEID ----------12: 22: 01 100 12: 22: 03 200 12: 22: 04 300 12: 22: 06 200 12: 22: 45 100 12: 22: 46 200 12: 23: 12 100 12: 23: 12 200 MIN(TIJD) ----12: 22: 01 12: 22: 45 12: 23: 12 MAX(TIJD) HOEVEELHEID ----------12: 22: 06 800 12: 22: 46 300 12: 23: 12 300 af 10. sql 16
Vensterclausule • Verder specificeren op welke gegevens de analytische functie moet werken • Geankerd / Zwevend • Rijen (ROWS) / Bereik (RANGE) • Bijv: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW • of: ROWS 5 PRECEDING af 8. sql 17
Einde VRAGEN? af 8. sql 18
- Slides: 18
