Contents

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'


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


3 谿瑚襭 #