0
0
Read Time:1 Minute, 47 Second
During this post we will discuss one of the important function MATCH_RECOGNIZE() in Snowflake. Sometimes you need to identify sequences of table rows that match a pattern. Snowflake has included the MATCH_RECOGNIZE clause into the analytic function syntax to make pattern matching from SQL simpler. To get more understanding how this function use we will consider the below two scenario.
- Determine the user category based on the INVOICE AMOUNT on particular day. Customer table maintains Customer and its Invoice details. We have observed the different invoices on every other day. We will evaluate TREND how the Invoice generated by customer on each day. Based on the Invoice expenditure we will identify if user is Silver, Gold or Platinum category.
- This example is related to SIEBEL CRM Activity. In case of any issue on SIEBEL application, End user opens an SR with organization. As per the process, end user first sends an Email which is followed by Call. In case no response received User escalates this SR at higher level. As part of problem statement business wants to identify such SR which are followed a specific sequence Email-Call-Escalate. Based on this report business can prioritize these Service request.
Lets see some practical implementation.
- Use case 1: Find the pattern based on the following condition
- BASIC: Invoice amount less than 45
- SILVER: Amount greater than Previous but less than GOLD.
- GOLD: Greater than or equal to 105% of the value of the SILVER
- PLATINUM: Amount greater than GOLD
Expected Output:
Query:
- Use case2: Find the pattern based on the following condition
- EMAIL –>CALL–>ESCALATE
Query:
you will need to refer to the documentation to understand the true level of complexity possible.
Happy
0 %
Sad
0 %
Excited
0 %
Sleepy
0 %
Angry
0 %
Surprise
0 %
