BEGIN
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('1','XYZ','1-Jan-21','315');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('2','XYZ','2-Jan-21','311.1');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('3','XYZ','3-Jan-21','308.1');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('4','XYZ','4-Jan-21','310');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('5','XYZ','5-Jan-21','308.7');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('6','XYZ','6-Jan-21','311.2');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('7','XYZ','7-Jan-21','313.75');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('8','XYZ','8-Jan-21','314.7');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('9','XYZ','9-Jan-21','308.05');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('10','ABC','1-Jan-21','14');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('11','ABC','2-Jan-21','12');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('12','ABC','3-Jan-21','11');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('13','ABC','4-Jan-21','8');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('14','ABC','5-Jan-21','9');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('15','ABC','6-Jan-21','7');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('16','ABC','7-Jan-21','5');
INSERT INTO rawdata (SERIAL_NO,STOCK,CDATE,PRICE) VALUES ('17','ABC','8-Jan-21','10');
END;/
1 rows affected
hidden batch(es)
with
rd_fixed as (select SERIAL_NO,STOCK,CDATE,PRICE from rawdata)
,pred as (
SELECT
*
FROM rd_fixed
MATCH_RECOGNIZE (
PARTITION BY STOCK
ORDER BY CDATE
MEASURES
COUNT(*) AS VALUE_MATCH,
CLASSIFIER() AS PATTERN
ALL ROWS PER MATCH
PATTERN (
down+ | up+ | other
)
DEFINE
down AS PREV(price) > price,
up AS PREV(price) < price
)
)
,moving_average as (
select
pred.*
,round(
avg(PRICE)
over(
partition by STOCK
order by CDATE
range between 5 preceding and current row
)
,2) as "MOV_AVG"
,count(*)
over(
partition by STOCK
order by CDATE
range between 5 preceding and current row
) as cnt5
from pred
)
select
ma.*
,case when cnt5 = 5 then round("MOV_AVG",2) end as only_5_MOV_AVG
from moving_average ma
