clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601479 fiddles created (47962 in the last week).

CREATE TABLE RAWDATA( SERIAL_NO NUMBER, STOCK VARCHAR(10), CDATE DATE, PRICE NUMBER );
 hidden batch(es)


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
STOCK CDATE VALUE_MATCH PATTERN SERIAL_NO PRICE MOV_AVG CNT5 ONLY_5_MOV_AVG
ABC 01-JAN-21 1 OTHER 10 14 14 1
ABC 02-JAN-21 1 DOWN 11 12 13 2
ABC 03-JAN-21 2 DOWN 12 11 12.33 3
ABC 04-JAN-21 3 DOWN 13 8 11.25 4
ABC 05-JAN-21 1 UP 14 9 10.8 5 10.8
ABC 06-JAN-21 1 DOWN 15 7 10.17 6
ABC 07-JAN-21 2 DOWN 16 5 8.67 6
ABC 08-JAN-21 1 UP 17 10 8.33 6
XYZ 01-JAN-21 1 OTHER 1 315 315 1
XYZ 02-JAN-21 1 DOWN 2 311.1 313.05 2
XYZ 03-JAN-21 2 DOWN 3 308.1 311.4 3
XYZ 04-JAN-21 1 UP 4 310 311.05 4
XYZ 05-JAN-21 1 DOWN 5 308.7 310.58 5 310.58
XYZ 06-JAN-21 1 UP 6 311.2 310.68 6
XYZ 07-JAN-21 2 UP 7 313.75 310.48 6
XYZ 08-JAN-21 3 UP 8 314.7 311.08 6
XYZ 09-JAN-21 1 DOWN 9 308.05 311.07 6
 hidden batch(es)