1 / 29

Az Oracle SQL 11.

Az Oracle SQL 11. Elemzések támogatása. A rádiótelefonokat kérem KIKAPCSOLNI!. Felhasznált irodalom. Gyári dokumentáció: Data Warehousing Guide 18. fejezet: SQL for Aggregation in Data Warehouses

allegra-hodges
Download Presentation

Az Oracle SQL 11.

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Az Oracle SQL 11. Elemzések támogatása Markó Tamás, PTE TTK

  2. A rádiótelefonokat kérem KIKAPCSOLNI! Markó Tamás, PTE TTK

  3. Felhasznált irodalom • Gyári dokumentáció:Data Warehousing Guide 18. fejezet:SQL for Aggregation in Data Warehouses • Kende Mária - Nagy István: Oracle példatár 13. fejezet: Részletező csoportosítások és analitikus függvények az SQL-ben Markó Tamás, PTE TTK

  4. Data Warehouse - adattárház • Relációs adatbázis, de • lekérdezésekre és elemzésekre tervezték (nem tranzakciók feldolgozására) • általában történelmi adatsorokat tartalmaz, amik tranzakciós rendszerekből származnak • általában több forrásból is gyűjt adatokat • OLAP-eszközök is tartoznak hozzá (lásd a multidimenziós adatmodellről tanultakat) • A cégen belül különválik a tranzakciók feldolgozása és az elemzés Markó Tamás, PTE TTK

  5. Az adattárház jellemzői • Adott célra készül (pl. az értékesítések elemzésére) • Integrált: • több forrásból gyűjt adatokat • ezeket azonos formára hozza • Nemfelejtő: • az ide bekerülő adatok többé nem módosulnak • hosszú időn át megmaradnak • Lehetővé teszi az időbeli változások tanulmányozását Markó Tamás, PTE TTK

  6. Tranzakciókezelő: kevés index sok kapcsolat 3NF származtatott adatok tárolása ritka mindig naprakész (adatmódosítás gyakran, kevés rekordon) Adattárház: sok index kevés kapcsolat nem normalizált származtatott adatok tárolása gyakori adatfrissítés naponta / hetente, sok új rekord egyszerre Eltérések a tranzakciókezelő (OLTP) rendszerektől Markó Tamás, PTE TTK

  7. Az adattárház szerkezete Markó Tamás, PTE TTK

  8. Adattárházak támogatása SQL-ben • Az Oracle-nél csak az Oracle 8i óta • Fontos kiterjesztések: • új csoportképzési eszközök • analitikus (elemző) függvények • Az analitikus függvények nem részei a szabványos SQL-nek • a szabványosítás napirenden van Markó Tamás, PTE TTK

  9. Új csoportképzési lehetőségek Markó Tamás, PTE TTK

  10. ROLLUP • Egyszerre több egymásba ágyazott szinten képez összesítő adatokat • Példa:SELECT deptno, job, SUM(sal), COUNT(*) AS letszamFROM scott.empGROUP BY ROLLUP(deptno, job); részlegenként, azon belül beosztás szerint összesít Markó Tamás, PTE TTK

  11. ROLLUP – az eredmény • DEPTNO JOB SUM(SAL) LETSZAM---------- --------- ---------- ---------- 10 CLERK 1300 1 10 MANAGER 2450 1 10 PRESIDENT 5000 1 10 8750 3 20 CLERK 1900 2 20 ANALYST 6000 2 20 MANAGER 2975 1 20 10875 5 30 CLERK 950 1 30 MANAGER 2850 1 30 SALESMAN 5600 4 30 9400 6 29025 1413 sor kijelölve. összesítés a részlegre főösszesen ezt az információt hagyományosan csak 3 SELECT tudná előállítani Markó Tamás, PTE TTK

  12. CUBE • Mindegyik ismérv szerint egyenrangúan képez részcsoportokat • Példa:SELECT deptno, job, SUM(sal), COUNT(*) AS letszamFROM scott.empGROUP BY CUBE(deptno, job); részleg szerint is és beosztás szerint is összesít a sorrend felcserélésével ugyanezeket a sorokat kapjuk (csak más sorrendben) Markó Tamás, PTE TTK

  13. CUBE - az eredmény főösszesen • DEPTNO JOB SUM(SAL) LETSZAM---------- --------- ---------- ---------- 29025 14 CLERK 4150 4 ANALYST 6000 2 MANAGER 8275 3 SALESMAN 5600 4 PRESIDENT 5000 1 10 8750 3 10 CLERK 1300 1 10 MANAGER 2450 1 10 PRESIDENT 5000 1 20 10875 5 20 CLERK 1900 2 20 ANALYST 6000 2 20 MANAGER 2975 1 30 9400 6 30 CLERK 950 1 30 MANAGER 2850 1 30 SALESMAN 5600 418 sor kijelölve. összesítések a beosztásra összesítés a részlegre azonos részleg és azonos beosztás összesítés a részlegre Markó Tamás, PTE TTK

  14. GROUPING SETS • Több csoportosítás is képezhető egy lekérdezésben ugyanazokból a rekordokból • Példa:SELECT mgr, deptno, job, SUM(sal), count(*) as letszamFROM scott.empGROUP BY GROUPING SETS ((mgr, deptno, job),(deptno, job),(mgr)); három különböző csoportosítás Markó Tamás, PTE TTK

  15. GROUPING SETS – az eredmény 1. • MGR DEPTNO JOB SUM(SAL) LETSZAM---------- ---------- --------- ---------- ---------- 7782 10 CLERK 1300 1 7839 10 MANAGER 2450 1 10 PRESIDENT 5000 1 7788 20 CLERK 1100 1 7902 20 CLERK 800 1 7566 20 ANALYST 6000 2 7839 20 MANAGER 2975 1 7698 30 CLERK 950 1 7839 30 MANAGER 2850 1 7698 30 SALESMAN 5600 4 azonos főnök, részleg és beosztás (össz. 14 fő) Markó Tamás, PTE TTK

  16. GROUPING SETS – az eredmény 2. • MGR DEPTNO JOB SUM(SAL) LETSZAM---------- ---------- --------- ---------- ----------10 CLERK 1300 1 10 MANAGER 2450 1 10 PRESIDENT 5000 1 20 CLERK 1900 2 20 ANALYST 6000 2 20 MANAGER 2975 1 30 CLERK 950 1 30 MANAGER 2850 1 30 SALESMAN 5600 4 7566 6000 2 7698 6550 5 7782 1300 1 7788 1100 1 7839 8275 3 7902 800 1 5000 126 sor kijelölve. azonos részleg és beosztás (össz. 14 fő) azonos főnök (össz. 14 fő) Markó Tamás, PTE TTK

  17. Analitikus függvények Markó Tamás, PTE TTK

  18. Analitikus rangfüggvények - példa • SELECT ename, sal,RANK() OVER (ORDER BY sal DESC) AS normal,DENSE_RANK() OVER (ORDER BY sal DESC) AS tomor,PERCENT_RANK() OVER (ORDER BY sal DESC) AS szazalekFROM scott.emp; ki hányadik a fizetési rangsorban Markó Tamás, PTE TTK

  19. Analitikus rangfüggvények - eredmény • ENAME SAL NORMAL TOMOR SZAZALEK---------- ---------- ---------- ----- --------KING 5000 1 1 0SCOTT 3000 2 2 .0769230FORD 3000 2 2 .0769230JONES 2975 4 3 .2307692BLAKE 2850 5 4 .3076923CLARK 2450 6 5 .3846153ALLEN 1600 7 6 .4615384TURNER 1500 8 7 .5384615MILLER 1300 9 8 .6153846WARD 1250 10 9 .6923076MARTIN 1250 10 9 .6923076ADAMS 1100 12 10 .8461538JAMES 950 13 11 .9230769SMITH 800 14 12 114 sor kijelölve. ugrás ugrás

  20. Aggregáló rangfüggvények - példa paraméter! OVER helyett WITHIN GROUP • SELECTRANK(4000) WITHIN GROUP (ORDER BY sal DESC) AS normal,DENSE_RANK(4000) WITHIN GROUP (ORDER BY sal DESC) AS tomor,PERCENT_RANK(4000) WITHIN GROUP (ORDER BY sal DESC) AS szazalekFROM scott.emp; • Az eredmény: NORMAL TOMOR SZAZALEK---------- ---------- ---------- 2 2 .071428571 hányadik lenne a 4000 a fizetési rangsorban Markó Tamás, PTE TTK

  21. Particionálás • A sorok részhalmazaira számítja ki az analitikus függvények értékét • Példa:SELECT deptno, ename, sal,RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS sorrendFROM scott.empORDER BY deptno, ename; a rangokat minden részlegen belül külön kezeli Markó Tamás, PTE TTK

  22. Particionálás - az eredmény • DEPTNO ENAME SAL SORREND---------- ---------- ---------- ---------- 10 CLARK 2450 2 10 KING 5000 1 10 MILLER 1300 3 20 ADAMS 1100 4 20 FORD 3000 1 20 JONES 2975 3 20 SCOTT 3000 1 20 SMITH 800 5 30 ALLEN 1600 2 30 BLAKE 2850 1 30 JAMES 950 6 30 MARTIN 1250 4 30 TURNER 1500 3 30 WARD 1250 414 sor kijelölve.

  23. Statisztikai függvények • Az ismert statisztikai függvények az OVER taggal kiegészítve (a működés más) • Példa: göngyölített (kumulált) összegSELECT ename, sal, SUM(sal) OVER (ORDER BY sal) AS kumulalt FROM scott.emp; Markó Tamás, PTE TTK

  24. Kumulált összeg - az eredmény • ENAME SAL KUMULALT---------- ---------- ----------SMITH 800 800JAMES 950 1750ADAMS 1100 2850WARD 1250 5350MARTIN 1250 5350MILLER 1300 6650TURNER 1500 8150ALLEN 1600 9750CLARK 2450 12200BLAKE 2850 15050JONES 2975 18025SCOTT 3000 24025FORD 3000 24025KING 5000 29025 az azonos értékeket egyszerre adja hozzá az azonos értékeket egyszerre adja hozzá

  25. Kumulált összeg particionálással 1. • SELECT deptno, ename, sal, SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) AS kumulaltFROM scott.emp; részlegenként külön kezdi a kumulálást Markó Tamás, PTE TTK

  26. Kumulált összeg particionálással 2. • DEPTNO ENAME SAL KUMULALT---------- ---------- ---------- ---------- 10 MILLER 1300 1300 10 CLARK 2450 3750 10 KING 5000 8750 20 SMITH 800 800 20 ADAMS 1100 1900 20 JONES 2975 4875 20 SCOTT 3000 10875 20 FORD 3000 10875 30 JAMES 950 950 30 WARD 1250 3450 30 MARTIN 1250 3450 30 TURNER 1500 4950 30 ALLEN 1600 6550 30 BLAKE 2850 9400

  27. Csúszóátlag • SELECT ename, sal, AVG(sal) OVER ( ORDER BY sal ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS csuszoatlag FROM scott.emp; az előző, az aktuális és a következő sor átlaga Markó Tamás, PTE TTK

  28. Csúszóátlag - eredmény • ENAME SAL CSUSZOATLAG---------- ---------- -----------SMITH 800 875JAMES 950 950ADAMS 1100 1100WARD 1250 1200MARTIN 1250 1266.66667MILLER 1300 1350TURNER 1500 1466.66667ALLEN 1600 1850CLARK 2450 2300BLAKE 2850 2758.33333JONES 2975 2941.66667SCOTT 3000 2991.66667FORD 3000 3666.66667KING 5000 4000

  29. Az analitikus függvények általános alakja • Függvénynév ([paraméter]) OVER (analitikus_tag) • analitikus_tag: [particionálás][rendezés [ablak]] Markó Tamás, PTE TTK

More Related