DataBaser.Net: MATCH

1 예제: 퍼널 분석
2 예제: 증감 패턴
3 참고자료

시퀀스 패턴 분석에 유용하다.

1 예제: 퍼널 분석 #

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'

[edit]

2 예제: 증감 패턴 #

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

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)
)

[edit]

Contents

1 예제: 퍼널 분석 #

2 예제: 증감 패턴 #

3 참고자료 #