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