add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (Stock, Cdate, Open, High, Low, Close, Volume ) AS
SELECT 'XYZ', DATE '2021-01-01', 40.00, 40.50, 38.50, 38.80, 83057 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-02', 39.20, 39.20, 37.20, 37.80, 181814 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-03', 38.00, 38.50, 36.50, 37.00, 117378 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-04', 36.00, 36.10, 35.60, 35.70, 93737 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-05', 35.35, 36.80, 35.10, 36.60, 169106 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-06', 36.50, 38.50, 36.50, 38.00, 123179 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-07', 37.50, 39.50, 37.30, 39.40, 282986 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-08', 39.00, 40.50, 38.50, 40.00, 117437 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-01', 135.35, 136.80, 135.10, 136.60, 16454 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-02', 136.50, 138.50, 136.50, 138.00, 281461 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-03', 137.50, 139.50, 137.30, 139.40, 77334 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-04', 139.00, 140.50, 138.50, 140.00, 321684 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-05', 139.70, 139.80, 139.30, 139.40, 873009 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-06', 139.20, 139.20, 137.20, 137.80, 62522 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-07', 138.00, 138.50, 136.50, 137.00, 114826 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-08', 136.60, 136.80, 135.10, 135.35, 27317 FROM DUAL;
16 rows affected
SELECT *
FROM table_name
MATCH_RECOGNIZE(
PARTITION BY stock
ORDER BY cdate
MEASURES
CLASSIFIER() AS pttrn
ALL ROWS PER MATCH
PATTERN (^initial_value|bullish|bearish|up|down|other)
DEFINE
bullish AS PREV(open) > PREV(close)
AND Close > Open
AND Close > PREV(High)
AND Open < PREV(Low),
bearish AS Close < Open
AND Close < PREV(Close)
AND Open < PREV(Close)
AND PREV(Open) < PREV(Close)
AND Close > PREV(Open)
AND Open < PREV(Close),
up AS close > PREV(close)
AND open > PREV(open),
down AS close < PREV(close)
AND open < PREV(open)
)
STOCK CDATE PTTRN OPEN HIGH LOW CLOSE VOLUME
DDD 01-JAN-21 INITIAL_VALUE 135.35 136.8 135.1 136.6 16454
DDD 02-JAN-21 UP 136.5 138.5 136.5 138 281461
DDD 03-JAN-21 UP 137.5 139.5 137.3 139.4 77334
DDD 04-JAN-21 UP 139 140.5 138.5 140 321684
DDD 05-JAN-21 BEARISH 139.7 139.8 139.3 139.4 873009
DDD 06-JAN-21 DOWN 139.2 139.2 137.2 137.8 62522
DDD 07-JAN-21 DOWN 138 138.5 136.5 137 114826
DDD 08-JAN-21 DOWN 136.6 136.8 135.1 135.35 27317
XYZ 01-JAN-21 INITIAL_VALUE 40 40.5 38.5 38.8 83057
XYZ 02-JAN-21 DOWN 39.2 39.2 37.2 37.8 181814
XYZ 03-JAN-21 DOWN 38 38.5 36.5 37 117378
XYZ 04-JAN-21 DOWN 36 36.1 35.6 35.7 93737
XYZ 05-JAN-21 BULLISH 35.35 36.8 35.1 36.6 169106
XYZ 06-JAN-21 UP 36.5 38.5 36.5 38 123179
XYZ 07-JAN-21 UP 37.5 39.5 37.3 39.4 282986
XYZ 08-JAN-21 UP 39 40.5 38.5 40 117437
SELECT t.*,
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY stock ORDER BY cdate) = 1
THEN 'INITIAL_VALUE'
WHEN LAG(open) OVER (PARTITION BY stock ORDER BY cdate)
> LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
AND Close > Open
AND Close > LAG(high) OVER (PARTITION BY stock ORDER BY cdate)
AND Open < LAG(low) OVER (PARTITION BY stock ORDER BY cdate)
THEN 'BULLISH'
WHEN Close < Open
AND Close < LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
AND Open < LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
AND LAG(open) OVER (PARTITION BY stock ORDER BY cdate)
< LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
AND Close > LAG(open) OVER (PARTITION BY stock ORDER BY cdate)
AND Open < LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
THEN 'BEARISH'
WHEN Close > LAG(Close) OVER (PARTITION BY stock ORDER BY cdate)
AND Open > LAG(Open) OVER (PARTITION BY stock ORDER BY cdate)
THEN 'UP'
WHEN Close < LAG(Close) OVER (PARTITION BY stock ORDER BY cdate)
AND Open < LAG(Open) OVER (PARTITION BY stock ORDER BY cdate)
THEN 'DOWN'
ELSE 'OTHER'
END AS pttrn
FROM table_name t
STOCK CDATE OPEN HIGH LOW CLOSE VOLUME PTTRN
DDD 01-JAN-21 135.35 136.8 135.1 136.6 16454 INITIAL_VALUE
DDD 02-JAN-21 136.5 138.5 136.5 138 281461 UP
DDD 03-JAN-21 137.5 139.5 137.3 139.4 77334 UP
DDD 04-JAN-21 139 140.5 138.5 140 321684 UP
DDD 05-JAN-21 139.7 139.8 139.3 139.4 873009 BEARISH
DDD 06-JAN-21 139.2 139.2 137.2 137.8 62522 DOWN
DDD 07-JAN-21 138 138.5 136.5 137 114826 DOWN
DDD 08-JAN-21 136.6 136.8 135.1 135.35 27317 DOWN
XYZ 01-JAN-21 40 40.5 38.5 38.8 83057 INITIAL_VALUE
XYZ 02-JAN-21 39.2 39.2 37.2 37.8 181814 DOWN
XYZ 03-JAN-21 38 38.5 36.5 37 117378 DOWN
XYZ 04-JAN-21 36 36.1 35.6 35.7 93737 DOWN
XYZ 05-JAN-21 35.35 36.8 35.1 36.6 169106 BULLISH
XYZ 06-JAN-21 36.5 38.5 36.5 38 123179 UP
XYZ 07-JAN-21 37.5 39.5 37.3 39.4 282986 UP
XYZ 08-JAN-21 39 40.5 38.5 40 117437 UP
WITH StockRow AS (
SELECT stock,
close,
cdate,
ROW_NUMBER() OVER(
PARTITION BY stock
ORDER BY cdate
) rn
FROM table_name
WHERE volume > 200000
),
RunGroup AS (
SELECT Base.stock,
Base.cdate,
base.close,
MAX(Restart.rn) OVER (
PARTITION BY Base.stock
ORDER BY Base.cdate
) AS groupingId
FROM StockRow Base
LEFT JOIN StockRow Restart
ON Restart.stock = Base.stock
AND Restart.rn = Base.rn - 1
AND Restart.CLOSE < Base.CLOSE
)
SELECT stock,
COUNT(*) AS consecutiveCount,
MIN(cdate) AS startDate,
MAX(cdate) AS endDate,
min(close) as latestclose
FROM RunGroup
GROUP BY stock, groupingId
HAVING COUNT(*) >= 3
and MAX(cdate) = (select max(cdate) from table_name)
ORDER BY CONSECUTIVECOUNT desc, stock, startDate