1 / 108

Oracle University SQL Masterclass

Oracle University SQL Masterclass. Rob van Wijk . June 9 & 10, 2011, Tallinn, Estonia. About me. Work with Oracle and SQL since 1995 From: Blog: Forums:. Utrecht, Netherlands. Agenda. Day 1 Part One: Do More With SQL and Joining Part Two: Analytic Functions

farrell
Download Presentation

Oracle University SQL Masterclass

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. Oracle University SQL Masterclass Rob van Wijk June 9 & 10, 2011, Tallinn, Estonia

  2. About me Work with Oracle and SQL since 1995 From: Blog: Forums: Utrecht, Netherlands

  3. Agenda Day 1 Part One: Do More With SQL and Joining Part Two: Analytic Functions Part Three: Grouping & Aggregating Part Four: SQL Model Clause Day 2 Part Five: Recursive Subquery Factoring Part Six: Regular Expressions Part Seven: XML in SQL Part Eight: Frequently Occuring SQL Problems

  4. Part 1a: Do More With SQL

  5. Goals • As practical as possible • As less regurgitating of documentation as possible • Lots of example scripts • Recognizable problems • Do as much as possible in SQL and avoid shipping records for processing to PL/SQL or even Java at a middle tier.

  6. If you want to build a ship, don't drum up the men togather wood, divide the work and give orders.Instead, teach them to yearn for the vast and endless sea.– Antoine de Saint Exupéry

  7. One SQL engine versus …

  8. … two engines. context swtiches procedural engine SQL engine dmws1.sql

  9. You risk wrong results because of different start times of queries with default READ COMMITTED isolation level dmws2.sql

  10. Part 1b: ANSI joins and partitioned outer join

  11. ANSI joins: Comparison with Oracle-syntax CROSS JOIN INNER JOIN OUTER JOIN NATURAL JOIN aj1.sql aj2.sql aj3.sql aj4.sql

  12. ANSI joins: Full Outer Join a FULL OUTER JOIN b ≡ a LEFT OUTER JOIN b UNION ALL b WHERE NOT EXISTS a 11g: Native full outer join _optimizer_native_full_outer_join /*+ NATIVE_FULL_OUTER_JOIN */ /*+ NO_NATIVE_FULL_OUTER_JOIN */ aj5.sql aj6.sql

  13. ANSI joins: Partitioned Outer Join Outer join: NULL rows for missing values Partitioned outer join: NULL rows for missing values per <column1>, …, <columnN> aj7.sql

  14. ANSI joins 15 • Title of presentation

  15. Part 2: Analytic Functions

  16. Analytic Functions: Topics Introduction Mind set Evaluation order Main syntax Examples Window clause

  17. Analytic Functions: Introduction Of every employee please show me: His name The department he’s working in His salary The cumulative salary per department Percentage of salary within the department Percentage of salary within the company where employees are sorted by department and salary af1a.sql af1b.sql af1c.sql

  18. Analytic Functions: Introduction Since 8.1.6 Enterprise Edition Look like well known aggregate functions like SUM, COUNT and AVG … but they don’t aggregate Prevents self joins Have been extended with new functions and new options in more recent versions af2.sql

  19. Analytic Functions: Mind set Don’t think “rows” … EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 09-06-81 2450 10 7839 KING PRESIDENT 17-11-81 5000 10 7934 MILLER CLERK 7782 23-01-82 1300 10 7876 ADAMS CLERK 7788 23-05-87 1100 20 7902 FORD ANALYST 7566 03-12-81 3000 20 7566 JONES MANAGER 7839 02-04-81 2975 20 7788 SCOTT ANALYST 7566 19-04-87 3000 20 7369 SMITH CLERK 7902 17-12-80 800 20 7499 ALLEN SALESMAN 7698 20-02-81 1600 300 30 7698 BLAKE MANAGER 7839 01-05-81 2850 30 7900 JAMES CLERK 7698 03-12-81 950 30 7654 MARTIN SALESMAN 7698 28-09-81 1250 1400 30 7844 TURNER SALESMAN 7698 08-09-81 1500 0 30 7521 WARD SALESMAN 7698 22-02-81 1250 500 30 7902 FORD ANALYST 7566 03-12-81 3000 20

  20. Analytic Functions: Mind set … but think “sets” 7499 ALLEN SALESMAN 7698 20-02-81 1600 300 30 7698 BLAKE MANAGER 7839 01-05-81 2850 30 7900 JAMES CLERK 7698 03-12-81 950 30 7654 MARTIN SALESMAN 7698 28-09-81 1250 1400 30 7844 TURNER SALESMAN 7698 08-09-81 1500 0 30 7521 WARD SALESMAN 7698 22-02-81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 09-06-81 2450 10 7839 KING PRESIDENT 17-11-81 5000 10 7934 MILLER CLERK 7782 23-01-82 1300 10 7876 ADAMS CLERK 7788 23-05-87 1100 20 7902 FORD ANALYST 7566 03-12-81 3000 20 7566 JONES MANAGER 7839 02-04-81 2975 20 7788 SCOTT ANALYST 7566 19-04-87 3000 20 7369 SMITH CLERK 7902 17-12-80 800 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 09-06-81 2450 10 7839 KING PRESIDENT 17-11-81 5000 10 7934 MILLER CLERK 7782 23-01-82 1300 10 7876 ADAMS CLERK 7788 23-05-87 1100 20 7902 FORD ANALYST 7566 03-12-81 3000 20 7566 JONES MANAGER 7839 02-04-81 2975 20 7788 SCOTT ANALYST 7566 19-04-87 3000 20 7369 SMITH CLERK 7902 17-12-80 800 20 7499 ALLEN SALESMAN 7698 20-02-81 1600 300 30 7698 BLAKE MANAGER 7839 01-05-81 2850 30 7900 JAMES CLERK 7698 03-12-81 950 30 7654 MARTIN SALESMAN 7698 28-09-81 1250 1400 30 7844 TURNER SALESMAN 7698 08-09-81 1500 0 30 7521 WARD SALESMAN 7698 22-02-81 1250 500 30

  21. Analytic Functions: Evaluation order Last Even after evaluating HAVING clause And after ROWNUM has been assigned But before ORDER BY clause Filtering on outcome of analytic function: nest the query using an inline view or use subquery factoring af3a.sql af3b.sql

  22. Analytic Functions: Main syntax <function> (<argument>, <argument>, …) OVER (<partition clause> <order by clause> <window clause> )

  23. Analytic Functions: The functions

  24. Analytic Functions: Partition clause PARTITION BY <expression> [,<expression>]* to let the analytic function operate on a subset of the rows with the same values for the partition by expression values. af4.sql

  25. Analytic Functions: Order By clause ORDER BY <expression> [ASC|DESC] [NULLS FIRST|NULLS LAST], … Its presence changes the default window of an analytic function from the total set to a running total. af5.sql

  26. Analytic Functions: Example 1 Top N queries What do I mean exactly with: “Show me the top 3 earning employees per department” RANK DENSE_RANK ROW_NUMBER af6.sql

  27. Analytic Functions: Example 2 1. David Zabriskie (USA) 0.58:31 2. Ivan Basso (ITA) + 0:17 3. Paolo Savoldelli (ITA) + 0:44 4. Marzio Bruseghin (ITA) + 0:48 5. Serguei Gonchar (UKR) z.t. 6. Vladimir Karpets (RUS) + 1:07 7. Markus Fothen (GER) + 1:15 8. Thomas Dekker (NLD) + 1:23 9. Jan Hruska (CZE) + 1:34 10. Danilo di Luca (ITA)z.t. af7.sql

  28. Analytic Functions: Example 3 Requirement: non-overlapping & consecutive periods Columns Startdate and maybe Enddate Optimize to retrieve current period Options: No Enddate column and use correlated subquery Enddate column and database trigger code to check requirement No Enddate column and use analytic function af8.sql

  29. Analytic Functions: Example 4 Bills can be of type “Prepayment” or “Settlement” Bill lines have an amount. Each customer pays a prepayment each month. The bill contains one bill line with the amount. Each customer receives once a year a settlement bill. How to calculate the previous prepayment amount? This is the amount before the last settlement bill. af9.sql

  30. Analytic Functions: Example 5 TIME QUANTITY -------- ----------- 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(TIME) MAX(TIME) QUANTITY • --------- --------- ----------- • 12:22:01 12:22:06 800 • 12:22:45 12:22:46 300 • 12:23:12 12:23:12 300 af10.sql

  31. Analytic Functions: Window clause Total set: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING Anchored set / running aggregate: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ROW / RANGE af11.sql af12.sql af13.sql

  32. Analytic Functions 33 • Title of presentation

  33. Part 3: Grouping & Aggregating

  34. aog1.sql

  35. Grouping & Aggregating: Topics Introduction GROUPING SETS ROLLUP CUBE Combining and calculating Supporting functions Inner workings MIN/MAX … KEEP … (DENSE_RANK FIRST/LAST … )

  36. Grouping & Aggregating: Grouping Sets (1) GROUP BY expr1, …, exprn ≡ GROUP BY GROUPING SETS ( (expr1, …, exprn) ) aog2.sql

  37. Grouping & Aggregating: Grouping Sets (2) GROUP BY GROUPING SETS ( (expr11, …, expr1n), …, (exprx1, …, exprxm) ) ≡ GROUP BY expr11, … expr1n UNION ALL … UNION ALL GROUP BY exprx1, …, exprxm aog3.sql

  38. Grouping & Aggregating: ROLLUP (1) GROUP BY ROLLUP ( set1, …, setn ) ≡ GROUP BY GROUPING SETS ( (set1, …, setn), (set1, …, setn-1), …, set1, () )

  39. Grouping & Aggregating: ROLLUP (2) ROLLUP (set1, …, setN) with N ≥ 1 leads to N+1 GROUPING SETS

  40. Grouping & Aggregating: ROLLUP (3) Example: GROUP BY ROLLUP ( (deptno), (job,mgr), (empno) ) ≡ GROUP BY GROUPING SETS ( (deptno,job,mgr,empno) , (deptno,job,mgr) , (deptno) , () ) aog4.sql

  41. Grouping & Aggregating: CUBE (1) GROUP BY CUBE ( set1, …, setn ) ≡ GROUP BY GROUPING SETS (all possible combinations between () and (set1, …, setn) )

  42. Grouping & Aggregating: CUBE (2) CUBE (set1, …, setN) with N ≥ 1 leads to 2N GROUPING SETS

  43. Grouping & Aggregating: CUBE (3) • Follows Pascal’s triangle 0 setsX 1 set 2 sets 3 sets 4 sets

  44. Grouping & Aggregating: CUBE (4) Example: GROUP BY CUBE ( (deptno), (job,mgr), (empno) ) ≡ GROUP BY GROUPING SETS ( (deptno,job,mgr,empno) , (deptno,job,mgr), (deptno,empno), (job,mgr,empno) , (deptno), (job,mgr), (empno) , () ) aog5.sql

  45. Grouping & Aggregating: Calculating (1) GROUP BY deptno, ROLLUP(empno) ?

  46. Grouping & Aggregating: Calculating (2) GROUP BY deptno, ROLLUP(empno) ≡ GROUP BY GROUPING SETS (deptno) , GROUPING SETS ( empno, () )

  47. Grouping & Aggregating: Calculating (3) Cartesian product ! GROUP BY deptno, ROLLUP(empno) ≡ GROUP BY GROUPING SETS (deptno) , GROUPING SETS ( (empno), () ) ≡ GROUP BY GROUPING SETS ( (deptno,empno), (deptno) ) aog6.sql

  48. Grouping & Aggregating: Calculating (4) Question: How many grouping sets does the clause below yield? GROUP BY ROLLUP(deptno,job) , CUBE(mgr,hiredate) aog7.sql

  49. Grouping & Aggregating: Functions GROUPING GROUPING_ID GROUP_ID aog8.sql

More Related