SQL refinements since SQL 92 Lennart Jonsson ICT

SQL refinements since SQL 92 Lennart Jonsson, ICT Umeå University Session Code: F 03 Monday , November 16 2015, 14. 30 – 15. 30 Platform: DB 2 for LUW

Presentation Objectives • Become familiar with some of the constructions that have been introduced in SQL since SQL 92. • Understand how these constructions can simplify your SQL and make it easier to read and maintain. • Understand how these constructions can improve efficiency and performance of your SQL. 2

SQL Standards … 6) Let WNS be the <window name or specification>. Let WDX be a window structure descriptor that describes the window defined by WNS. 7) If <ntile function>, <lead or lag function>, <rank function type> or ROW_NUMBER is specified, then: a) If <ntile function>, <lead or lag function>, RANK or DENSE_RANK is specified, then the window ordering clause WOC of WDX shall be present. b) The window framing clause of WDX shall not be present. c) Case: i) If WNS is a <window name>, then let WNS 1 be WNS. ii) Otherwise, let WNS 1 be the <window specification details> contained in WNS. … 3

Agenda • SQL 1999 • Lateral, Common Table Expressions • SQL 2003 • Window functions • SQL 2008 • Additional Window Functions • SQL 2011 • Temporal features 4

Agenda • SQL 1999 • Lateral, Common Table Expressions • SQL 2003 • Window functions • SQL 2008 • Additional Window Functions • SQL 2011 • Temporal features 5

SQL 1999, LATERAL It is not possible to refer to “siblings” in the parse tree Invalid, yes hmm SELECT * FROM A JOIN ( SELECT * FROM B WHERE B. x = A. x ) as derived_table ON … 6

SQL 1999, LATERAL SQL 92 solution, move where clause to join condition SELECT * FROM A JOIN ( SELECT * FROM B WHERE B. x = A. x ) as derived_table ON … AND derived_table. x = A. x 7

SQL 1999, LATERAL allows us to reference a previous “declared” table Valid, it now is yeesss SELECT * FROM A JOIN LATERAL ( SELECT * FROM B WHERE B. x = A. x ) as derived_table ON A. x = derived_table. x 8

SQL 1999, LATERAL If there are no “free” variables, Cartesian product. SELECT * FROM A CROSS JOIN LATERAL ( SELECT * FROM B WHERE B. x = A. x ) as derived_table 9

SQL 1999, LATERAL If there are no “free” variables, Cartesian product. Fascinating…, is this SELECT * going anywhere? FROM A CROSS JOIN LATERAL ( SELECT * FROM B WHERE B. x = A. x ) as derived_table 10

SQL 1999, LATERAL Example: The first name and last name from employee’s SELECT e. firstnme as name FROM employee e UNION ALL SELECT e. lastname as name FROM employee e NAME CHRISTINE THOMPSON KWAN GEYER STERN … 11

SQL 1999, LATERAL Transpose columns to rows SELECT t. name FROM employee e CROSS JOIN LATERAL ( VALUES (e. firstnme) , (e. lastname) ) as t(name) NAME CHRISTINE THOMPSON KWAN GEYER STERN … 12

SQL 1999, LATERAL Total Cost: Query Degree: Rows RETURN ( 1) Cost I/O | 200000 UNION ( 2) 87. 1077 2 /-----+------ 100000 TBSCAN ( 3) ( 4) 39. 35 1 1 | | 100000 TABLE: LTJN EMPLOYEE Q 1 Q 3 103. 923 1 Total Cost: Query Degree: 67. 4072 1 Rows RETURN ( 1) Cost I/O | 200000 NLJOIN ( 2) 67. 4072 1 /------+------ 100000 2 TBSCAN ( 3) ( 4) 39. 35 2. 83407 e-05 1 0 | | 100000 2 TABLE: LTJN TABFNC: SYSIBM EMPLOYEE GENROW Q 3 Q 1 13

SQL 1999, LATERAL JOIN against an inline view select x. REF_ID, x. ARRIVAL_DATE, x. CREATION_TIME, x. APPL_FEE_PAYMENT_BASE from ( select pay. REF_ID, ARRIVAL_DATE, CREATION_TIME, APPL_FEE_PAYMENT_BASE from nya. APPL_FEE_PAYMENT_BY_BANK_ADM pay where not exists ( select 1 from nya. APPL_FEE_PAYMENT_BY_BANK_ADM_ANNULLED ann where pay. REF_ID = ann. REF_ID ) union all select pay. REF_ID, ARRIVAL_DATE, CREATION_TIME, APPL_FEE_PAYMENT_BASE from nya. APPL_FEE_PAYMENT_BY_CARD pay where not exists ( select 1 from nya. APPL_FEE_PAYMENT_BY_CARD_ANNULLED ann where pay. REF_ID = ann. REF_ID ) union all select pay. REF_ID, ARRIVAL_DATE, CREATION_TIME, APPL_FEE_PAYMENT_BASE from nya. APPL_FEE_PAYMENT_BY_CARD_ADM pay where not exists ( select 1 from nya. APPL_FEE_PAYMENT_BY_CARD_ADM_ANNULLED ann where pay. REF_ID = ann. REF_ID ) ) x join nya. SEMESTER_PERSON_APPL_FEE_REFERENCE y on y. REF_ID = x. REF_ID where person_id=? and APPL_FEE_SEMESTER_ALT=? order by REF_ID Total Cost: 1989. 22 . . . 45923 UNION ( 5) 1924. 72 937. . . -----++----. . . 40690 x^HSJOIN ( 9) 1572. 94 834 /------+------ 41009 319 TBSCAN ( 10) ( 11) 1492. 07 77. 3231 828 6 | | 41009 319 TABLE: NYA APPL_FEE_PAYMENT_BY_CARD_ANNULLE Q 6 Q 5 Rows Read / Execution 49783 14

SQL 1999, LATERAL Predicate push down select x. REF_ID, x. ARRIVAL_DATE, x. CREATION_TIME, x. APPL_FEE_PAYMENT_BASE from nya. SEMESTER_PERSON_APPL_FEE_REFERENCE y cross join LATERAL ( select pay. REF_ID, ARRIVAL_DATE, CREATION_TIME, APPL_FEE_PAYMENT_BASE from nya. APPL_FEE_PAYMENT_BY_BANK_ADM pay where not exists ( select 1 from nya. APPL_FEE_PAYMENT_BY_BANK_ADM_ANNULLED ann where pay. REF_ID = ann. REF_ID ) and y. REF_ID = pay. REF_ID union all select pay. REF_ID, ARRIVAL_DATE, CREATION_TIME, APPL_FEE_PAYMENT_BASE from nya. APPL_FEE_PAYMENT_BY_CARD pay where not exists ( select 1 from nya. APPL_FEE_PAYMENT_BY_CARD_ANNULLED ann where pay. REF_ID = ann. REF_ID ) and y. REF_ID = pay. REF_ID union all select pay. REF_ID, ARRIVAL_DATE, CREATION_TIME, APPL_FEE_PAYMENT_BASE from nya. APPL_FEE_PAYMENT_BY_CARD_ADM pay where not exists ( select 1 from nya. APPL_FEE_PAYMENT_BY_CARD_ADM_ANNULLED ann where pay. REF_ID = ann. REF_ID ) and y. REF_ID = pay. REF_ID ) x where person_id=? and APPL_FEE_SEMESTER_ALT=? order by REF_ID Total Cost: 1989. 22 103. 233 0. 00638571 UNION ( 7) 51. 7731 4. 02409. . . ------++-------. . . 0. 00565817 FETCH ( 11) 26. 0148 834 2. 02263 +-------+---------+ 0. 0113163 0. 00777878 41009 IXSCAN TABLE: NYA ( 12) ( 13) APPL_FEE_PAYMENT_BY_CARD 25. 7234 12. 869 Q 7 2 1 | | 41009 319 INDEX: NYA XPK_. . . _CARD_ANNULLED Q 7 Q 5 Rows Read / Execution 49783 2 15

SQL 1999, Lateral If you are doing large scans over derived table’s, LATERAL can provide you with optimization super powers 16

SQL 1999, LATERAL Availability VERSION DB 2 LUW YEAR NOTE 9. 1 2006 - - Postgre. SQL 9. 3 2013 Oracle 12 c 2013 My. SQL Server 2005 CROSS APPLY 17

SQL 1999, Common Table Expression (CTE) Redundant. Yes, hmmm. SELECT x, y, z FROM T WHERE x > 0 AND (x, z) in (SELECT x, max(z) FROM T WHERE x > 0 GROUP BY x); 18

SQL 1999, Common Table Expression (CTE) No longer any redundancy, there is. WITH tmp as ( SELECT x, y, z FROM T WHERE x > 0 ) SELECT x, y, z FROM TMP WHERE(x, z) in (SELECT x, max(z) FROM TMP GROUP BY x); 19

SQL 1999, Common Table Expression (CTE) WITH CTE 1 (…) as ( … ) , CTE 2 (…) as ( … ) SELECT … FROM … • A CTE can refer to a previously defined CTE 2 can be expressed in terms of CTE 1, not the other way around • This can be used to force a certain order of evaluation 20

SQL 1999, Recursive Common Table Expression (CTE) “Recursive” Common Table Expressions • A CTE can refer to it self • This can be used to • Traverse hierarchical structures • Generate series of data • In a sense they make SQL “more” computational complete 21

SQL 1999, Recursive Common Table Expression (CTE) Traverse hierarchical structure with rec (projno, majproj, n) as ( select projno, majproj, 1 from project where majproj is null union all select p. projno, r. n+1 from project p, rec r where r. projno = p. majproj -- prevent infinite recursion and r. n < 100 PROJNO MAJPROJ N AD 3100 - 1 AD 3110 AD 3100 2 AD 3111 AD 3110 3 AD 3112 AD 3110 3 … … … ) select * from rec order by projno, majproj 22

SQL 1999, Recursive Common Table Expression (CTE) Project Euler. net #2 (https: //projecteuler. net/problem=2) … By considering the terms in the Fibonacci sequence whose values do not exceed four million, find the sum of the even-valued terms. Fibonacci series F(0) = 1 F(1) = 1 F(n) = F(n-1)+F(n-2) 1, 1, 2, 3, 5, 8, 13, 21, 34… with fib(n, m) as ( values (1, 1) union all select m, n+m from fib where m <= 4000000 ) select sum(m) from fib where mod(m, 2) = 0 t (s) sum(m) 0. 023 4613732 23

SQL 1999, Common Table Expression (CTE) Query rewrite, predicate push down with t(x, y) as ( select x, y from t 1 union all select x, y from t 2 ) select * from t where x = ? with t(x, y) as ( select x, y from t 1 where x = ? union all select x, y from t 2 where x = ? ) select * from t 24

SQL 1999, Recursive Common Table Expression (CTE) CREATE VIEW NYA. ELIG_MODEL_CLOSURE AS with model_closure (elig_collection_id, elig_collection_id_child, n) as ( select x. elig_collection_id, y. elig_collection_id_child, 0 from nya. elig_collection x, nya. elig_collection_connection y where x. elig_collection_id = y. elig_collection_id and elig_componenttype_id = 0 Courses union all MA 201 select mc. elig_collection_id, z. elig_collection_id_child, n+1 from model_closure mc, nya. elig_collection_connection z where mc. elig_collection_id_child = z. elig_collection_id Eligibility Model >1 and n < 100 ) & & select distinct e. EDUCATIONORG_ID, e. ELIG_COLLECTION_SHORT , u. ELIG_REQ_UNIT_ID >1 from model_closure mc join. . . Requirements MA 102 GRADE>C MA 101 GRADE>C 25

SQL 1999, Recursive Common Table Expression (CTE) select 1 from nya. course_offering co join nya. elig_collection ec on co. elig_collection_id = ec. elig_collection_id join nya. elig_model_closure x on ec. educationorg_id = x. educationorg_id and ec. elig_collection_short = x. elig_collection_short and ec. elig_type = ? join nya. elig_req_unit y on x. elig_req_unit_id = y. elig_req_unit_id where elig_req_unit_type = ? and co. admissionround_id = ? Referenced, the and co. educationorg_id = ? closure for all tree's and co. courseoffering_id = ? are determined every fetch first 1 rows only optimize for 1 rows time the view is. Herh Rows Read / Exec Inx Logical Rd / Exec Data Logical Rd / Exec 457689 382183 461 26

SQL 1999, Recursive Common Table Expression (CTE) CREATE FUNCTION NYA. ELIG_MODEL_CLOSURE (in_educationorg_id varchar(3) , in_elig_collection_short varchar(10)) returns table (elig_req_unit_id int) return with model_closure (elig_collection_id, elig_collection_id_child, n) as ( select x. elig_collection_id, y. elig_collection_id_child, 0 Courses MA 201 from nya. elig_collection x, nya. elig_collection_connection y Eligibility Model where x. elig_collection_id = y. elig_collection_id and elig_componenttype_id = 0 >1 and x. educationorg_id = in_educationorg_id and x. elig_collection_short = in_elig_collection_short & & union all select mc. elig_collection_id, z. elig_collection_id_child, n+1 >1 from model_closure mc, nya. elig_collection_connection z where mc. elig_collection_id_child = z. elig_collection_id and n < 100 ) Requirements MA 102 GRADE>C MA 101 GRADE>C select distinct e. EDUCATIONORG_ID, e. ELIG_COLLECTION_SHORT , . . . u. ELIG_REQ_UNIT_ID 27

SQL 1999, Recursive Common Table Expression (CTE) select 1 from nya. course_offering co join nya. elig_collection ec on co. elig_collection_id = ec. elig_collection_id cross join table(nya. elig_model_closure (ec. educationorg_id, ec. elig_collection_short)) x join nya. elig_req_unit y on x. elig_req_unit_id = y. elig_req_unit_id where elig_req_unit_type = ? and co. admissionround_id = ? and co. educationorg_id = ? and co. courseoffering_id = ? Manually push the and ec. elig_type = ? predicates inside the cte, using a table function we can. Yes. fetch first 1 rows only optimize for 1 rows Read / Exec Inx Logical Rd / Exec Data Logical Rd / Exec 457689 382183 461 14 389 159 28

SQL 1999, Common Table Expression (CTE) Availability VERSION DB 2 LUW YEAR NOTE 7. 1 2000 - - 8. 4 2009 Oracle 9 i. R 2 2002 SQL Server 2005 My. SQL Postgre. SQL 29

Agenda • SQL 1999 • Lateral, Common Table Expressions • SQL 2003 • Window functions • SQL 2008 • Additional Window Functions • SQL 2011 • Temporal features 30

SQL 2003, Window functions SELECT firstnme , count(*) over () FROM employee ORDER BY firstnme FIRSTNME BRUCE CHRISTINE DANIEL … FIRSTNME BRUCE 42 BRUCE CHRISTINE 42 CHRISTINE DANIEL 42 DANIEL … … … 31

SQL 2003, Window functions SELECT firstnme , count(*) over (order by firstnme) FROM employee ORDER BY firstnme FIRSTNME BRUCE CHRISTINE DANIEL … FIRSTNME BRUCE 1 BRUCE CHRISTINE 2 CHRISTINE DANIEL 3 DANIEL … … … 32

SQL 2003, Window functions SELECT firstnme , count(*) over (partition by substr(firstnme, 1, 1)) FROM employee FIRSTNME DANIEL ORDER BY firstnme DAVID DELORES FIRSTNME DIAN CHRISTINE 1 EILEEN DANIEL 4 FIRSTNME DAVID 4 DANIEL DELORES 4 DAVID DIAN 4 DELORES DIAN EILEEN 33

SQL 2003, Window functions SELECT firstnme , count(*) over (partition by substr(firstnme, 1, 1) order by firstnme) FIRSTNME FROM employee DANIEL ORDER BY firstnme DAVID DELORES FIRSTNME DIAN CHRISTINE 1 DANIEL 1 DAVID 2 DELORES 3 DAVID DIAN 4 DELORES EILEEN FIRSTNME DANIEL DIAN EILEEN 34

SQL 2003, Window functions SELECT firstnme , count(*) over (partition by substr(firstnme, 1, 1) order by firstnme FIRSTNME rows between 1 preceding DANIEL and 1 following) … DAVID DELORES FIRSTNME DIAN CHRISTINE 1 DANIEL 2 DAVID 3 DELORES 3 DAVID DIAN 2 DELORES EILEEN FIRSTNME DANIEL DIAN EILEEN 35

SQL 2003, Window functions We can use all the ordinary aggregate functions such as COUNT and SUM. In addition there a couple of specific functions: ZZZZ… • ROW_NUMBER() • 1, 2, 3 • RANK() • ties with gaps 1, 1, 3 • DENSE_RANK() • ties without gaps 1, 1, 2 36

SQL 2003, Window functions Get the employee with highest salary for each department with t as ( select empno, workdept, salary , row_number() over (partition by workdept order by salary) as rn from employee as e ) select empno, workdept, salary from t t 1 where rn = (select max(rn) from t t 2 where t 1. workdept = t 2. workdept) Metric Value RR per Exec 1848 37

SQL 2003, Window functions Get the employee with highest salary for each department: with t as ( select empno, workdept, salary , row_number() over (partition by workdept order by salary desc) as rn from employee as e ) select empno, workdept, salary from t t 1 where rn = 1 Metric Value RR per Exec 1848 RR per Exec 42 38

SQL 2003, Window functions Mean-value filtering 120 100 80 60 40 20 0 Serie 1 Avg 3 values Avg 7 values 0 10 20 30 select n, val, avg(val) over (order by n rows between 1 preceding and 1 following) from t order by n; 39

SQL 2003, Window functions 40 Normal Distribution Even 20 Even 0 0 100 200 with t(m, n) as ( values (int(100*rand()), 0) union all select int(100*rand()), n+1 from t where n<1000 ) select m, count(1) from t group by m 40

SQL 2003, Window functions 60 Normal Distribution 40 20 Even Normal 0 0 100 200 with t(m, n) as ( … ) select mm, count(1) from ( select int(avg(m) over (order by n rows between 3 preceding and 3 following) ) as mm from t ) group by mm 41

SQL 2003, Window functions Islands and Gaps problems A N A START STOP A 1 3 A 2 A 7 7 A 3 B 4 4 B 4 C 5 6 C 5 C 6 A 7 42

SQL 2003, Window functions Islands and Gaps problems, SQL 92 A START STOP A 1 3 A 7 7 B 4 4 5 6 select L. a, L. start, min(U. stop) as stop C from ( select a, n as start from t t 1 where not exists ( select 1 from t t 2 where t 1. a = t 2. a and t 1. n = t 2. n + 1 ) ) as L, ( select a, n as stop from t t 3 where not exists ( select 1 from t t 4 where t 3. a = t 4. a and t 3. n = t 4. n - 1 ) ) as U where L. a = U. a and U. stop >= L. start group by L. a, start 43

SQL 2003, Window functions Islands and Gaps problems, SQL 92 | HSJOIN ( 5) 0. 0429039 0 /---------+--------- 4. 66667 HSJOINx ( 6) ( 9) 0. 020955 0 0 /-------+------- 7 7 IXSCAN ( 7) ( 8) ( 10) ( 11) 0. 00982571 0 0 | | 7 7 INDEX: SYSIBM SQL 150907181038980 Q 4 Q 5 Q 1 Q 2 RETURN ( 1) Cost I/O | 4. 14815 GRPBY ( 2) 0. 0448336 0 | 4. 14815 TBSCAN ( 3) 0. 0445873 0 | 4. 14815 SORT ( 4) 0. 0441009 0 | HSJOIN ( 5) 0. 0429039 0 44

SQL 2003, Window functions Islands and Gaps problems, SQL 2003 select a, n , row_number() over (order by n) as total_order , row_number() over (partition by a order by n) as partition_order from t A N TOTAL_ORDER PART_ORDER ? A 1 1 1 A 2 2 2 A 3 3 3 B 4 4 1 C 5 5 1 C 6 6 2 A 7 7 4 45

SQL 2003, Window functions Islands and Gaps problems, SQL 2003 select a, n , row_number() over (order by n) , row_number() over (partition by a order by n) as grp from t A N TOTAL_ORDER PART_ORDER GRP A 1 1 1 0 A 2 2 2 0 A 3 3 3 0 B 4 4 1 3 C 5 5 1 4 C 6 6 2 4 A 7 7 4 3 46

SQL 2003, Window functions Islands and Gaps problems, SQL 2003 select a, min(n) as start, max(n) as stop from ( select a, n , row_number() over (order by n) , row_number() over (partition by a order by n) as grp from t ) group by a, grp A START STOP A 1 3 A 7 7 B 4 4 C 5 6 47

SQL 2003, Window functions Islands and Gaps problems, SQL 2003 Rows RETURN ( 1) Cost I/O | 7 GRPBY ( 2) 0. 0186826 0 | 7 TBSCAN ( 3) 0. 0181778 0 | 7 SORT ( 4) 0. 0175568 0 | | 7 TBSCAN ( 5) 0. 0135646 0 | 7 SORT ( 6) 0. 0129436 0 | 7 IXSCAN ( 7) 0. 00982571 0 | 7 INDEX: SYSIBM SQL 150907181038980 Q 1 48

SQL 2003, Window functions Cumulative sum, SQL 92 select student_id, course_code, prio, credits , ( select sum(credits) from tmp. selected_courses c 2 where c 1. student_id = c 2. student_id and c 2. prio <= c 1. prio ) as cumulative_credits from tmp. selected_courses c 1 order by student_id, prio; STUDENT_ID COURSE_CODE PRIO CREDITS CUMULATIVE A EKHA 20 1 30. 0 A PSYE 26 2 15. 0 45. 0 A PSYD 11 3 30. 0 75. 0 B MM 5001 1 7. 5 49

SQL 2003, Window functions Total Cost: 365. 191 Rows RETURN ( 1) Cost I/O | 0. 169453 ^NLJOIN ( 2) 365. 191 28. 4065 /---------+--------- 0. 194871 0. 869565 NLJOIN IXSCAN ( 3) ( 15) 352. 326 25. 7158 27. 4065 2 /------------+------------ | 0. 194871 1 378251 TBSCAN GRPBY INDEX: NYA ( 4) ( 9) XPKCOURSE 207. 429 144. 897 Q 3 16. 1362 11. 2704 | | 0. 194871 0. 0875508 SORT ^NLJOIN ( 5) ( 10) 207. 428 144. 897 16. 1362 11. 2704 | /-----+----- 0. 194871 0. 100683 0. 869565 ^NLJOIN IXSCAN ( 6) ( 11) ( 14) 207. 428 132. 032 25. 7158 16. 1362 10. 2704 2 /-------+------- | 4. 04539 0. 0481712 2. 09012 0. 0481712 378251 IXSCAN INDEX: NYA ( 7) ( 8) ( 12) ( 13) XPKCOURSE 51. 4231 38. 5724 51. 4235 38. 5724 Q 6 4 3 | | 3. 00417 e+07 394987 INDEX: NYA X 06 PICKEDCOURSEOFF XAK 5 COURSE_OFFERING Q 2 Q 1 Q 4 Q 5 Cumulative sum, SQL 92 For a particular student Metric Value Est. Cost 365. 191 Est. I/O 28. 4065 Inx Log. Rd 1715 50

SQL 2003, Window functions Cumulative sum, SQL 2003 select student_id, course_code, prio, credits , sum(credits) over (partition by student_id order by prio) as cumulative_credits from tmp. selected_courses c 1 order by student_id, prio; STUDENT_ID COURSE_CODE PRIO CREDITS CUMULATIVE A EKHA 20 1 30. 0 A PSYE 26 2 15. 0 45. 0 A PSYD 11 3 30. 0 75. 0 B MM 5001 1 7. 5 51

SQL 2003, Window functions Total Cost: Query Degree: 220. 295 1 Rows RETURN ( 1) Cost I/O | 0. 169453 ^NLJOIN ( 2) 220. 294 17. 1362 /---+---- 0. 194871 0. 869565 TBSCAN IXSCAN ( 3) ( 8) 207. 429 25. 7158 16. 1362 2 | | 0. 194871 378251 SORT INDEX: NYA ( 4) XPKCOURSE 207. 428 Q 3 16. 1362 | 0. 194871 ^NLJOIN ( 5) 207. 428 16. 1362 /-------+------- 4. 04539 0. 0481712 IXSCAN ( 6) ( 7) 51. 4231 38. 5724 4 3 | | 3. 00417 e+07 394987 INDEX: NYA X 06 PICKEDCOURSEOFF XAK 5 COURSE_OFFERING Q 2 Q 1 Using a window function, 8%, the number of index logical read is. Yes, hmmm. Cumulative sum, SQL 92 For a particular student Metric Value Est. Cost 365. 191 Est. I/O 28. 4065 Inx Log. Rd 1715 Cumulative sum, SQL 2003 For a particular student Metric Value Est. Cost 220. 295 Est. I/O 17. 1362 Inx Log. Rd 145 52

SQL 2003, Window functions Availability VERSION DB 2 LUW My. SQL Postgre. SQL Oracle SQL Server YEAR NOTE 7. 1 2000 - - 8. 4 2009 8 i 1999 2005 53

Agenda • SQL 1999 • Lateral, Common Table Expressions • SQL 2003 • Window functions • SQL 2008 • Additional Window Functions • SQL 2011 • Temporal features 54

SQL 2008, Additional Window Functions LAG(…) LEAD(…) FIRST_VALUE(…) LAST_VALUE(…) NTH_VALUE(col, n) FROM FIRST/LAST RESPECT/IGNORE NULLS 55

SQL 2008, Additional Window Functions SELECT firstnme , lag(firstnme) over (order by firstnme) FROM … FIRSTNME LAG(…) CHRISTINE … DANIEL CHRISTINE DAVID DANIEL DELORES DAVID DIAN DELORES 56

SQL 2008, Additional Window Functions SELECT firstnme , lag(firstnme, 2) over (order by firstnme) FROM … FIRSTNME LAG(…) CHRISTINE … DANIEL … DAVID CHRISTINE DELORES DANIEL DIAN DAVID 57

SQL 2008, Additional Window Functions SELECT firstnme , lag(firstnme) over (order by firstnme) , max(firstnme) over (order by firstnme rows between 1 preceding and 1 preceding) FIRSTNME LAG(…) CHRISTINE MAX(…) … … DANIEL CHRISTINE DAVID DANIEL DELORES DAVID DIAN DELORES Syntactic sugar for sql 99 relative offset, lead and lag are. 58

SQL 2008, Additional Window Functions SELECT firstnme , first_value(firstnme) over (order by firstnme) FROM … FIRSTNME FIRST_VALUE CHRISTINE DANIEL CHRISTINE DAVID CHRISTINE DELORES CHRISTINE DIAN CHRISTINE 59

SQL 2008, Additional Window Functions SELECT firstnme , first_value(firstnme) over (order by firstnme) , max(firstnme) over (order by firstnme rows between unbounded preceding and unbounded preceding ) FIRSTNME FIRST_VALUE MAX(…) CHRISTINE ? DANIEL CHRISTINE ? DAVID CHRISTINE ? DELORES CHRISTINE ? DIAN CHRISTINE ? SQL 0104 N An unexpected token "preceding" was found following "ceding and unbounded". Expected tokens may include: "FOLLOWING". 60

SQL 2008, Additional Window Functions With tmp as ( select firstnme , row_number() over (order by firstname) rn from employee ) select firstname , (select firstnme from tmp where rn = 1) as first_value from tmp 61

SQL 2008, Additional Window Functions A couple of pitfalls, some sample data with init(volt, rnd, price) as ( values (0. 03, rand(), cast(100 * rand as decimal(8, 2))) ), u (n, volt, cp, price) as ( select 0 as n, volt, 2 * volt * rnd, price, from init union all select n+1, volt, case when 2 * volt * rand() > volt then 2 * volt * rand() - (2 * volt) else 2 * volt * rand() end as cp, price + (price * cp) + rand() from u where n + 1 < 100000 ) select current_timestamp - 3 days + n seconds as ts , price, int(10000*rand()) as volume from u 62

SQL 2008, Additional Window Functions A couple of pitfalls, find open/close/ per 5 minute period create function period_start(ts timestamp) returns timestamp language sql no external action deterministic return ts - mod(minute(ts), 5) minutes - second(ts) seconds - microsecond(ts) microseconds @ 63

SQL 2008, Additional Window Functions A couple of pitfalls, find open/close/ per 5 minute period select distinct period_start(ts) , first_value(price) over ( partition by period_start(ts) order by ts) as open ) from sample 64

SQL 2008, Additional Window Functions A couple of pitfalls, find open/close/ per 5 minute period select distinct period_start(ts) , last_value(price) over ( partition by period_start(ts) order by ts) as close ) from sample 65

SQL 2008, Additional Window Functions A couple of pitfalls, find open/close per 5 minute period select distinct period_start(ts) , last_value(price) over ( partition by period_start(ts) order by ts rows between current row and unbounded following ) as close from sample 66

SQL 2008, Additional Window Functions A couple of pitfalls, find open/close per 5 minute period select distinct period_start(ts) , first_value(price) over ( partition by period_start(ts) order by ts desc) as close from sample 67

SQL 2008, Additional Window Functions A couple of pitfalls, partition by select period_start, open , lag(open) over (partition by period_start order by period_start) as lag_open from ( select distinct period_start(ts) as period_start , first_value(price) over ( partition by period_start(ts) order by ts) as open from sample ) PERIOD_START OPEN LAG_OPEN 2015 -09 -19 -12. 42. 05 1278. 84 - 2015 -09 -19 -12. 42. 10 1261. 01 - 68

SQL 2008, Additional Window Functions A couple of pitfalls, partition by select period_start, open , lag(open) over (order by period_start) as lag_open from ( select distinct period_start(ts) as period_start , first_value(price) over ( partition by period_start(ts) order by ts) as open from sample ) PERIOD_START OPEN LAG_OPEN 2015 -09 -19 -12. 42. 05 1278. 84 1261. 01 2015 -09 -19 -12. 42. 10 1261. 01 1256. 03 69

SQL 2008, Additional Window Functions Availability VERSION DB 2 LUW My. SQL Postgre. SQL Oracle SQL Server YEAR 9. 5 NOTE 2006 No N: th value - - 8. 4 2009 8 i 2012 1999 No N: th value 2012 70

Agenda • SQL 1999 • Lateral, Common Table Expressions • SQL 2003 • Window functions • SQL 2008 • Additional Window Functions • SQL 2011 • Temporal features 71

SQL 2011, Temporal features Temporal stuff in SQL 2011 contains loads and loads of things! • Business_time without overlaps 72

SQL 2011, Temporal features Business_time without overlaps, SQL 92 create table tmp 1 ( id INT NOT NULL , start DATE NOT NULL , end DATE NOT NULL , primary key (id, start ) , check (start <= end) ) @ 73

SQL 2011, Temporal features Business_time without overlaps, SQL 92 create trigger trg 1 no cascade before insert on tmp 1 referencing new as n for each row when ( exists ( select 1 from tmp 1 where n. id = id and n. end > start and n. start < end ) ) signal sqlstate '77000' ('Invalid period') @ 74

SQL 2011, Temporal features Business_time without overlaps, SQL 2011 create table tmp 2 ( id INT NOT NULL , start DATE NOT NULL , end DATE NOT NULL , period business_time(start, end) , primary key (id, business_time without overlaps) ) @ 75

SQL 2011, Temporal features Importing 100000 rows with groups of n identical id’s 700 600 500 400 300 200 100 0 SQL 92 0 00 10 10 00 0 10 10 1 SQL 2011 n SQL 92 (s) SQL 2011 (s) 1 32 30 10 33 27 100 40 31 1000 94 31 10000 608 31 100000 ? 31 76

SQL 2011, Temporal features Availability, without overlaps VERSION DB 2 LUW My. SQL Postgre. SQL YEAR NOTE 10. 1 2012 - - 9. 2 2012 EXCLUDE constraint Oracle - - SQL Server - - 77

Questions? 78

Thank You for listening! 79

Lennart Jonsson Umeå University LENNART. JONSSON@UMU. SE ERIK. LENNART. JONSSON@GMAIL. COM Session F 03 SQL refinements since SQL 92 Please fill out your session evaluation before leaving!