How to Find Patterns in Your Data with SQL

An introduction to the SQL row pattern matching clause match_recognzie.

Transcript

1. oracle.com/free New Free Tier Always Free Oracle Cloud Infrastructure Services you can use for unlimited time 30-Day Free Trial Free credits you can use for more services +
2. 2 How to Find Patterns in Your Data With SQL Chris Saxon, @ChrisRSaxon & @SQLDaily blogs.oracle.com/sql youtube.com/c/TheMagicofSQL asktom.oracle.com
3. Am I Improving? Can Beat My PB? Am I Training Regularly?
4. 4 How to Find Patterns in Your Data With SQL Chris Saxon, @ChrisRSaxon & @SQLDaily blogs.oracle.com/sql youtube.com/c/TheMagicofSQL asktom.oracle.com
5. The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. Statements in this presentation relating to Oracle’s future plans, expectations, beliefs, intentions and prospects are “forward-looking statements” and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that affect our business is contained in Oracle’s Securities and Exchange Commission (SEC) filings, including our most recent reports on Form 10-K and Form 10-Q under the heading “Risk Factors.” These filings are available on the SEC’s website or on Oracle’s website at http://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Safe Harbor
6. This presentation contains <regular expressions>!
7. I thought this was about SQL! blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography
9. * => zero or more matches + => one or more matches {n,m} => N through M matches (either optional)
10. Am I running every day? Ryan McGuire / Gratisography
11. RUN_DATE TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 310 1 02 Jan 2018 1,600 5 03 Jan 2018 3,580 11 06 Jan 2018 1,550 5 07 Jan 2018 300 1 10 Jan 2018 280 1 13 Jan 2018 1,530 5 14 Jan 2018 295 1 15 Jan 2018 292 1
12. RUN_DATE TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 310 1 02 Jan 2018 1,600 5 03 Jan 2018 3,580 11 06 Jan 2018 1,550 5 07 Jan 2018 300 1 10 Jan 2018 280 1 13 Jan 2018 1,530 5 14 Jan 2018 295 1 15 Jan 2018 292 1 #1 #3 #2 #4
13. How I know if rows are consecutive?
14. current value = previous value + 1
15. lag ( run_date ) over ( order by run_date ) Get the previous row's date
16. RUN_DATE RN TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 1 310 1 02 Jan 2018 2 1,600 5 03 Jan 2018 3 3,580 11 06 Jan 2018 4 1,550 5 07 Jan 2018 5 300 1 10 Jan 2018 6 280 1 13 Jan 2018 7 1,530 5 14 Jan 2018 8 295 1 15 Jan 2018 9 292 1 consecutive => constant gap
17. RUN_DATE RN TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 1 310 1 02 Jan 2018 2 1,600 5 03 Jan 2018 3 3,580 11 06 Jan 2018 4 1,550 5 07 Jan 2018 5 300 1 10 Jan 2018 6 280 1 13 Jan 2018 7 1,530 5 14 Jan 2018 8 295 1 15 Jan 2018 9 292 1 - - - - - - - - -
18. RUN_DATE RN RUN_DATE - RN TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 1 31 Dec 2017 310 1 02 Jan 2018 2 31 Dec 2017 1,600 5 03 Jan 2018 3 31 Dec 2017 3,580 11 06 Jan 2018 4 02 Jan 2018 1,550 5 07 Jan 2018 5 02 Jan 2018 300 1 10 Jan 2018 6 04 Jan 2018 280 1 13 Jan 2018 7 06 Jan 2018 1,530 5 14 Jan 2018 8 06 Jan 2018 295 1 15 Jan 2018 9 06 Jan 2018 292 1 - - - - - - - - -
19. RUN_DATE RN RUN_DATE - RN TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 1 31 Dec 2017 310 1 02 Jan 2018 2 31 Dec 2017 1,600 5 03 Jan 2018 3 31 Dec 2017 3,580 11 06 Jan 2018 4 02 Jan 2018 1,550 5 07 Jan 2018 5 02 Jan 2018 300 1 10 Jan 2018 6 04 Jan 2018 280 1 13 Jan 2018 7 06 Jan 2018 1,530 5 14 Jan 2018 8 06 Jan 2018 295 1 15 Jan 2018 9 06 Jan 2018 292 1 - - - - - - - - -
20. Tabibitosan Method
21. row_number () over ( order by run_date )
22. run_date - row_number () over ( order by run_date ) grp
23. with grps as ( select run_date , run_date - row_number () over ( order by run_date ) grp from running_log r ) select min ( run_date ), count (*) from grps group by grp
24. 12c Pattern Matching
25. select * from running_log match_recognize ( ); input output
26. RUN_DATE TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 310 1 02 Jan 2018 1,600 5 03 Jan 2018 3,580 11 06 Jan 2018 1,550 5 07 Jan 2018 300 1 10 Jan 2018 280 1 13 Jan 2018 1,530 5 14 Jan 2018 295 1 15 Jan 2018 292 1 this = prev + 1
27. RUN_DATE TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 310 1 02 Jan 2018 1,600 5 03 Jan 2018 3,580 11 06 Jan 2018 1,550 5 07 Jan 2018 300 1 10 Jan 2018 280 1 13 Jan 2018 1,530 5 14 Jan 2018 295 1 15 Jan 2018 292 1 this = prev + 1 this = prev + 3
28. RUN_DATE TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 310 1 02 Jan 2018 1,600 5 03 Jan 2018 3,580 11 06 Jan 2018 1,550 5 07 Jan 2018 300 1 10 Jan 2018 280 1 13 Jan 2018 1,530 5 14 Jan 2018 295 1 15 Jan 2018 292 1 this = prev + 1 this = prev + 3 this ≠ prev + 1
29. current value = previous value + 1
30. define consecutive as run_date = prev ( run_date ) + 1
31. pattern ( init consecutive* ) define consecutive as run_date = prev ( run_date ) + 1
32. pattern ( init consecutive* ) define consecutive as run_date = prev ( run_date ) + 1 Undefined => "Always true" > 0 matches
33. RUN_DATE VARIABLE TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 INIT 310 1 02 Jan 2018 CONSECUTIVE 1,600 5 03 Jan 2018 CONSECUTIVE 3,580 11 06 Jan 2018 INIT 1,550 5 07 Jan 2018 CONSECUTIVE 300 1 10 Jan 2018 INIT 280 1 13 Jan 2018 INIT 1,530 5 14 Jan 2018 CONSECUTIVE 295 1 15 Jan 2018 CONSECUTIVE 292 1
34. pattern ( init consecutive* ) define consecutive as run_date = prev ( run_date ) + 1 Which row is prev?!
35. order by run_date pattern ( init consecutive* ) define consecutive as run_date = prev ( run_date ) + 1
36. match_recognize ( order by run_date measures first ( run_date ) as start_date, count (*) as days pattern ( init consecutive* ) define consecutive as run_date = prev ( run_date ) + 1 ); How many consecutive rows? First row in group
37. START_DATE DAYS 01 Jan 2018 3 06 Jan 2018 2 10 Jan 2018 1 13 Jan 2018 3
38. So which is better? Pixabay pattern matching 12c 8i* ~speed
39. Am I running >= 3 times/week? Pixabay
40. RUN_DATE TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 310 1 02 Jan 2018 1,600 5 03 Jan 2018 3,580 11 06 Jan 2018 1,550 5 07 Jan 2018 300 1 10 Jan 2018 280 1 13 Jan 2018 1,530 5 14 Jan 2018 295 1 15 Jan 2018 292 1 #1 #3 #2
41. How I know if runs are in the same week?
42. latest Monday = prev latest Monday
43. trunc ( run_date , 'iw' ) Return the start of the ISO week… …Monday!
44. RUN_DATE TRUNC(RUN_DATE, 'IW') TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 01 Jan 2018 310 1 02 Jan 2018 01 Jan 2018 1,600 5 03 Jan 2018 01 Jan 2018 3,580 11 06 Jan 2018 01 Jan 2018 1,550 5 07 Jan 2018 01 Jan 2018 300 1 10 Jan 2018 08 Jan 2018 280 1 13 Jan 2018 08 Jan 2018 1,530 5 14 Jan 2018 08 Jan 2018 295 1 15 Jan 2018 15 Jan 2018 292 1
45. select trunc ( run_date , 'iw' ), count(*) from running_log group by trunc ( run_date , 'iw' )
46. select trunc ( run_date , 'iw' ), count(*) from running_log group by trunc ( run_date , 'iw' ) having count (*) >= 3
47. 12c Pattern Matching
48. latest Monday = prev latest Monday
49. define same_week as trunc ( run_date, 'iw' ) = prev ( trunc ( run_date, 'iw' ) )
50. pattern ( init same_week* ) define same_week as trunc ( run_date, 'iw' ) = prev ( trunc ( run_date, 'iw' ) )
51. pattern ( init same_week {2, } ) define same_week as trunc ( run_date, 'iw' ) = prev ( trunc ( run_date, 'iw' ) ) Two or more matches
52. match_recognize ( order by run_date measures first ( run_date ) as start_date, count (*) as days pattern ( init same_week {2, } ) define same_week as trunc ( run_date, 'iw' ) = prev ( trunc ( run_date, 'iw' ) ) );
53. START_DATE DAYS 01 Jan 2018 5 08 Jan 2018 3
54. match_recognize ( order by run_date measures first ( run_date ) as start_date, count (*) as days pattern ( init same_week {2, } ) define same_week as trunc ( run_date, 'iw' ) = prev ( trunc ( run_date, 'iw' ) ) );
55. match_recognize ( order by run_date measures first ( run_date ) as start_date, count (*) as days pattern ( init consecutive* ) define consecutive as run_date = prev ( run_date ) + 1 );
56. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
57. Am I running >= 3 times in 7 days? Pixabay
58. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
59. RUN_DATE TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 310 1 02 Jan 2018 1,600 5 03 Jan 2018 3,580 11 06 Jan 2018 1,550 5 07 Jan 2018 300 1 10 Jan 2018 280 1 13 Jan 2018 1,530 5 14 Jan 2018 295 1 15 Jan 2018 292 1
60. RUN_DATE TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 310 1 02 Jan 2018 1,600 5 03 Jan 2018 3,580 11 06 Jan 2018 1,550 5 07 Jan 2018 300 1 10 Jan 2018 280 1 13 Jan 2018 1,530 5 14 Jan 2018 295 1 15 Jan 2018 292 1 #1 #2
61. RUN_DATE TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 01 – 07 Jan 2018 310 1 02 Jan 2018 1,600 5 03 Jan 2018 3,580 11 06 Jan 2018 1,550 5 07 Jan 2018 300 1 10 Jan 2018 08 – 14 Jan 2018 280 1 13 Jan 2018 1,530 5 14 Jan 2018 295 1 15 Jan 2018 15 – 21 Jan 2018 292 1
62. RUN_DATE TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 01 – 07 Jan 2018 310 1 02 Jan 2018 1,600 5 03 Jan 2018 3,580 11 06 Jan 2018 1,550 5 07 Jan 2018 300 1 10 Jan 2018 10 – 16 Jan 2018 280 1 13 Jan 2018 1,530 5 14 Jan 2018 295 1 15 Jan 2018 292 1
63. current day < first day + 7
64. 11.2 Recursive With
65. with rws as ( select r.*, row_number() over ( order by run_date ) rn from running_log r ), within_7 ( run_date, time_in_s, distance_in_km, rn, grp_start ) as ( select run_date, time_in_s, distance_in_km, rn, run_date grp_start from rws where rn = 1 union all select r.run_date, r.time_in_s, r.distance_in_km, r.rn, case when r.run_date < w.grp_start + 7 then grp_start else r.run_date end grp_start from within_7 w join rws r on w.rn + 1 = r.rn ) select grp, w.* from within_7 w
66. 10g Model
67. select * from running_log model dimension by ( row_number() over ( order by run_date ) rn ) measures ( run_date, 1 grp, run_date grp_start ) rules ( grp_start[1] = run_date[cv()], grp_start[any] = case when run_date[cv()] < grp_start[cv()-1] + 7 then grp_start[cv() - 1] else run_date[cv()] end , grp[any] = case when run_date[cv()] < grp_start[cv()-1] + 7 then grp[cv() - 1] else nvl(grp[cv() - 1] + 1, 1) end );
68. 12c Pattern Matching
69. current day < first day + 7
70. define within7 as run_date < first ( run_date ) + 7
71. pattern ( within7 {3, } ) define within7 as run_date < first ( run_date ) + 7
72. match_recognize ( order by run_date measures first ( run_date ) as start_date, count (*) as days pattern ( within7 {3, } ) define within7 as run_date < first ( run_date ) + 7 );
73. START_DATE DAYS 01 Jan 2018 5 10 Jan 2018 4
74. Am I getting faster? stocksnap.io
75. current time < prev time
76. define faster as time_in_s < prev ( time_in_s )
77. pattern ( slower faster* ) define faster as time_in_s < prev ( time_in_s )
78. match_recognize ( order by run_date measures classifier () as faster pattern ( slower faster* ) define faster as time_in_s < prev ( time_in_s ) );
79. FASTER SLOWER SLOWER FASTER FASTER
80. match_recognize ( order by run_date measures classifier () as faster one row per match pattern ( slower faster* ) define faster as time_in_s < prev ( time_in_s ) );
81. match_recognize ( order by run_date measures classifier () as faster all rows per match pattern ( slower faster* ) define faster as time_in_s < prev ( time_in_s ) );
82. RUN_DATE FASTER TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 SLOWER 310 1 02 Jan 2018 SLOWER 1,600 5 03 Jan 2018 SLOWER 3,580 11 06 Jan 2018 FASTER 1,550 5 07 Jan 2018 FASTER 300 1 10 Jan 2018 FASTER 280 1 13 Jan 2018 SLOWER 1,530 5 14 Jan 2018 FASTER 295 1 15 Jan 2018 FASTER 292 1
83. RUN_DATE FASTER TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 SLOWER 310 1 02 Jan 2018 SLOWER 1,600 5 03 Jan 2018 SLOWER 3,580 11 06 Jan 2018 FASTER 1,550 5 07 Jan 2018 FASTER 300 1 10 Jan 2018 FASTER 280 1 13 Jan 2018 SLOWER 1,530 5 14 Jan 2018 FASTER 295 1 15 Jan 2018 FASTER 292 1 SLOWER!
84. RUN_DATE TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 310 1 07 Jan 2018 300 1 10 Jan 2018 280 1 14 Jan 2018 295 1 15 Jan 2018 292 1 02 Jan 2018 1,600 5 06 Jan 2018 1,550 5 13 Jan 2018 1,530 5 03 Jan 2018 3,580 11
85. match_recognize ( partition by distance_in_km order by run_date measures classifier () as faster all rows per match pattern ( slower faster* ) define faster as time_in_s < prev ( time_in_s ) );
86. RUN_DATE FASTER TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 SLOWER 310 1 07 Jan 2018 FASTER 300 1 10 Jan 2018 FASTER 280 1 14 Jan 2018 SLOWER 295 1 15 Jan 2018 FASTER 292 1 02 Jan 2018 SLOWER 1,600 5 06 Jan 2018 FASTER 1,550 5 13 Jan 2018 FASTER 1,530 5 03 Jan 2018 SLOWER 3,580 11
87. Can I run 10k in < 50 minutes?
88. Is my average pace < 300 s/km for runs with a total distance <= 10 km
89. cumulative dist <= 10 km
90. define ten_k as sum ( distince_in_km ) <= 10 Returns the running total
91. pattern ( ten_k+ ) define ten_k as sum ( distince_in_km ) <= 10
92. match_recognize ( order by run_date measures first ( run_date ) as strt , round ( avg ( time_in_s / distance_in_km ), 2 ) as mean_pace, sum ( distance_in_km ) as dist pattern ( ten_k+ ) define ten_k as sum ( distince_in_km ) <= 10 );
93. STRT MEAN_PACE DIST 01 Jan 2018 315.00 6 06 Jan 2018 296.67 7 13 Jan 2018 297.67 7 Where's my 11 km run?
94. any runs cumulative dist < 10 and one run cumulative dist >= 10
95. pattern ( )
96. pattern ( under_10k* over_10k )
97. pattern ( under_10k* over_10k ) define under_10k as sum ( distance_in_km ) < 10, over_10k as sum ( distance_in_km ) >= 10 ); Includes under_10k values
98. match_recognize ( order by run_date measures first ( run_date ) as strt , round ( avg ( time_in_s / distance_in_km ), 2 ) as mean_pace sum ( distance_in_km ) as dist pattern ( under_10k* over_10k ) define under_10k as sum ( distance_in_km ) < 10, over_10k as sum ( distance_in_km ) >= 10 );
99. STRT MEAN_PACE DIST 01 Jan 2018 318.48 17 06 Jan 2018 299.00 12 Hmmm….
100. match_recognize ( order by run_date measures first ( run_date ) as strt , round ( avg ( time_in_s / distance_in_km ), 2 ) as mean_pace sum ( distance_in_km ) as dist after match skip past last row pattern ( under_10k* over_10k ) define under_10k as sum ( distance_in_km ) < 10, over_10k as sum ( distance_in_km ) >= 10 );
101. match_recognize ( order by run_date measures first ( run_date ) as strt , round ( avg ( time_in_s / distance_in_km ), 2 ) as mean_pace sum ( distance_in_km ) as dist after match skip to next row pattern ( under_10k* over_10k ) define under_10k as sum ( distance_in_km ) < 10, over_10k as sum ( distance_in_km ) >= 10 );
102. STRT MEAN_PACE DIST 01 Jan 2018 318.48 17 02 Jan 2018 322.73 16 03 Jan 2018 325.45 11 06 Jan 2018 299.00 12
103. 00:48:19
104. Photo by Doruk Yemenici on Unsplash
105. What About Query Performance?
106. MATCH RECOGNIZE SORT Non-deterministic
107. MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTO
108. Pixabay
109. How often did I run 5 km Followed by 2+ 1 km runs Within 7 days?
110. pattern ( five_km one_km {2,} )
111. pattern ( five_km one_km {2,} ) define five_km as distance_in_km = 5,
112. pattern ( five_km one_km {2,} ) define five_km as distance_in_km = 5, one_km as distance_in_km = 1
113. pattern ( five_km one_km {2,} ) define five_km as distance_in_km = 5, one_km as distance_in_km = 1 and run_date < first ( run_date ) + 7
114. match_recognize ( order by run_date measures first ( run_date ) as start_date, count (*) as total_runs pattern ( five_km one_km {2,} ) define five_km as distance_in_km = 5, one_km as distance_in_km = 1 and run_date < first ( run_date ) + 7 );
115. START_DATE TOTAL_RUNS 06 Jan 2018 3 13 Jan 2018 3
116. Why would I want to do that?!
117. Pixabay
118. Row Pattern Matching Use Cases Fraud Analytics 2+ $1 trx between acts 1 $10,000 trx in 7 days Stock Market Trends Price rose 3 days Then fell 3 days Customer Retention 2+ orders/month for years Max 2 orders past 6 mths Date Ranges Finding gaps & overlaps
119. How do I debug it? Gratisography
120. (Regular) [exprsion]+ are easy to missteak
121. regex101.com regex101.com
122. classifier => Which variable matched?
123. classifier => Which variable matched? match_number => Which group is this?
124. classifier => Which variable matched? match_number => Which group is this? all rows per match
125. classifier => Which variable matched? match_number => Which group is this? all rows per match with unmatched rows => Show me everything!
126. match_recognize ( order by run_date measures classifier () as var, match_number () as grp all rows per match with unmatched rows pattern ( five_km one_km {2,} ) define five_km as distance_in_km = 5, one_km as distance_in_km = 1 and run_date < first ( run_date ) + 7 );
127. RUN_DATE VAR GRP TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 310 1 02 Jan 2018 1,600 5 03 Jan 2018 3,580 10 06 Jan 2018 FIVE_KM 1 1,550 5 07 Jan 2018 ONE_KM 1 300 1 10 Jan 2018 ONE_KM 1 280 1 13 Jan 2018 FIVE_KM 2 1,530 5 14 Jan 2018 ONE_KM 2 295 1 15 Jan 2018 ONE_KM 2 292 1
128. RUN_DATE VAR GRP TIME_IN_S DISTANCE_IN_KM 01 Jan 2018 310 1 02 Jan 2018 1,600 5 03 Jan 2018 3,580 10 06 Jan 2018 FIVE_KM 1 1,550 5 07 Jan 2018 ONE_KM 1 300 1 10 Jan 2018 ONE_KM 1 280 1 13 Jan 2018 FIVE_KM 2 1,530 5 14 Jan 2018 ONE_KM 2 295 1 15 Jan 2018 ONE_KM 2 292 1
129. Want more? Pixabay
130. livesql.oracle.com
131. iTunes & PDF FREE! SQL for Data Warehousing and Analytics https://oracle-big-data.blogspot.co.uk Keith Laker Analytic SQL PM
132. #MakeDataGreatAgain oracle-big-data.blogspot.co.uk Ryan McGuire / Gratisography