1 예제: 퍼널 분석

2 예제: 증감 패턴

3 참고자료

• 1,2,5,6 순서대로 로그가 남은 캐릭터를 조회하고 싶다.
  
• 중간에 왔다갔다 헤도 상관없다.
  
  

with gamelog(pc_id, log_date, log_no) 
as 
(
    values
    ('pc_id_1', DATE '2020-05-11', 1),
    ('pc_id_1', DATE '2020-05-12', 2),
    ('pc_id_1', DATE '2020-05-13', 3),
    ('pc_id_1', DATE '2020-05-14', 3),
    ('pc_id_1', DATE '2020-05-15', 4),
    ('pc_id_1', DATE '2020-05-16', 5),
    ('pc_id_1', DATE '2020-05-17', 4),
    ('pc_id_1', DATE '2020-05-18', 6),
    ('pc_id_1', DATE '2020-05-18', 5),
    ('pc_id_2', DATE '2020-05-11', 1),
    ('pc_id_2', DATE '2020-05-12', 2),
    ('pc_id_2', DATE '2020-05-13', 3),
    ('pc_id_2', DATE '2020-05-14', 3),
    ('pc_id_2', DATE '2020-05-15', 4),
    --('pc_id_2', DATE '2020-05-16', 5),
    ('pc_id_2', DATE '2020-05-17', 4),
    ('pc_id_2', DATE '2020-05-18', 6)  
)
select * 
from gamelog
match_recognize
(
    partition by pc_id order by log_date
    measures
        prev(log_date) as prev_log_date
    ,   match_number() as match_no
    ,   classifier() as class
    --one row per match --패턴에 맞는 하나의 row만
    all rows per match --패턴에 맞는 모든 row
    --after match skip past last row
    pattern(a+ z* b+ z* c+ z* d+)
    define
        a as a.log_no = 1
    ,   b as b.log_no = 2
    ,   c as c.log_no = 5
    ,   d as d.log_no = 6
    ,   z as 1=1
)
where class <> 'Z'

• 시작 -> 감소 -> 증가 패턴
  
  

with orders(customer_id, order_date, price) 
as 
(
    values
    ('cust_1', DATE '2020-05-11', 100),
    ('cust_1', DATE '2020-05-12', 200),
    ('cust_2', DATE '2020-05-13', 100),
    ('cust_1', DATE '2020-05-14', 100),
    ('cust_2', DATE '2020-05-15',  90),
    ('cust_1', DATE '2020-05-16',  50),
    ('cust_1', DATE '2020-05-17', 100),
    ('cust_2', DATE '2020-05-18', 120)
)
select 
    customer_id
,   order_date
,   price
,   start_price
,   down_price
,   up_price
from orders
match_recognize
(
    partition by customer_id order by order_date
    measures
        start.price as start_price --시작값
    ,   order_date as order_date
    ,   price as price
    ,   last(up.price) as up_price --최고치 중 마지막값
    ,   last(down.price) as down_price --최저치 중 마지막값
    ,   match_number() as match_no
    ,   classifier() as class
    one row per match --패턴에 맞는 하나의 row만
    --all rows per match --패턴에 맞는 모든 row
    --after match skip past last row
    after match skip to next row
    pattern(start down{1} up{1})
    define
        up as price > prev(price)
    ,   down as price < prev(price)
)

• https://docs.snowflake.com/ko/user-guide/match-recognize-introduction
  
• https://modern-sql.com/feature/match_recognize
  
• https://trino.io/docs/current/sql/match-recognize.html?highlight=match_recognize
  
• https://t1.daumcdn.net/cfile/tistory/99D614475C13430C07
  
• https://www.imperva.com/blog/how-to-predict-customer-churn-using-sql-pattern-detection/
  
• https://nightlies.apache.org/flink/flink-docs-release-1.17/docs/dev/table/sql/queries/match_recognize/
  
• https://github.com/ververica/sql-training/blob/master/slides/sql-training-05-pattern-matching.pdf
  
• http://wiki.gurubee.net/pages/viewpage.action?pageId=30441535
  
• https://hrjeong.tistory.com/335
  
• https://www.microsoft.com/en-us/research/uploads/prod/2022/05/match_recognize.pdf
  
• https://docs.oracle.com/cd/E29542_01/apirefs.1111/e12048/pattern_recog.htm#CQLLR2194
  
• https://trino.io/episodes/23.html
  
• https://trino.io/blog/2021/05/19/row_pattern_matching.html