By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE RAW_SOURCE(
Stock VARCHAR(100),
Close_Date VARCHAR(100),
Open NUMBER,
High NUMBER,
Low NUMBER,
Close NUMBER,
Volume NUMBER
);
BEGIN
/* INSERT QUERY NO: 1 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '01/01/2021', 40, 40.5, 38.5, 38.8, 83057
);
/* INSERT QUERY NO: 2 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '02/01/2021', 39.2, 39.2, 37.2, 37.8, 181814
);
/* INSERT QUERY NO: 3 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '03/01/2021', 38, 38.5, 36.5, 37, 117378
);
/* INSERT QUERY NO: 4 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '04/01/2021', 36.5, 36.6, 35.6, 35.7, 93737
);
/* INSERT QUERY NO: 5 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '05/01/2021', 35.35, 36.8, 35.1, 36.7, 169106
);
1 rows affected
SELECT *
FROM raw_source
MATCH_RECOGNIZE (
PARTITION BY stock
ORDER BY Close_Date
MEASURES
CLASSIFIER() AS pttrn
ALL ROWS PER MATCH
PATTERN (
^initial_value
|
down+ (bullish_engulfing | piercing_line | $)
|
up+ (bearish_harami | $)
|
other
)
DEFINE
down AS
PREV(open) > open
AND PREV(close) > close
AND PREV(open) > PREV(close)
AND open > close,
up AS
PREV(open) < open
AND PREV(close) < close
AND PREV(open) < PREV(close)
AND open < close,
bullish_engulfing AS
-- O1 > C1 and C > O and C > H1 and O < L1
PREV(open) > PREV(close)
AND close > open
AND close > PREV(high)
AND open < PREV(low),
bearish_harami AS
-- O1 < C1 and O > C and O < C1 and C > O1 and H < H1 and L > L1
| STOCK | CLOSE_DATE | PTTRN | OPEN | HIGH | LOW | CLOSE | VOLUME |
|---|---|---|---|---|---|---|---|
| XYZ | 01/01/2021 | INITIAL_VALUE | 40 | 40.5 | 38.5 | 38.8 | 83057 |
| XYZ | 02/01/2021 | DOWN | 39.2 | 39.2 | 37.2 | 37.8 | 181814 |
| XYZ | 03/01/2021 | DOWN | 38 | 38.5 | 36.5 | 37 | 117378 |
| XYZ | 04/01/2021 | DOWN | 36.5 | 36.6 | 35.6 | 35.7 | 93737 |
| XYZ | 05/01/2021 | BULLISH_ENGULFING | 35.35 | 36.8 | 35.1 | 36.7 | 169106 |
| XYZ | 06/01/2021 | UP | 36.5 | 38.5 | 36.5 | 38 | 123179 |
| XYZ | 07/01/2021 | UP | 37.5 | 39.5 | 37.3 | 39.4 | 282986 |
| XYZ | 08/01/2021 | UP | 39 | 40.5 | 38.5 | 40 | 117437 |
| XYZ | 09/01/2021 | BEARISH_HARAMI | 39.7 | 39.8 | 39.3 | 39.4 | 873009 |
| XYZ | 10/01/2021 | DOWN | 39.2 | 39.2 | 37.2 | 37.8 | 62522 |
| XYZ | 11/01/2021 | DOWN | 38 | 38.5 | 36.5 | 37 | 114826 |
| XYZ | 12/01/2021 | PIERCING_LINE | 36.5 | 37.9 | 36.3 | 37.8 | 281461 |
| XYZ | 13/01/2021 | UP | 37.5 | 39.5 | 37.3 | 39.4 | 77334 |
| XYZ | 14/01/2021 | UP | 39 | 40.5 | 38.5 | 40 | 321684 |