Oracle SQL or PL/SQL: How to identify candlestick pattern only in end of uptrend or downtrend and set a flag in column?

Question

This question and related answers will be for educational or learning purpose only.

This question is much different from my other post and is not duplicate. Since it was creating confusion and as suggested by @MT0, I am posting this as a new question here.

I have below table, where I upload stock data on daily basis.

/* CREATE TABLE */
CREATE TABLE RAW_SOURCE(
  Stock  VARCHAR(100),
  Close_Date DATE,
  Open   NUMBER,
  High   NUMBER,
  Low    NUMBER,
  Close  NUMBER,
  Volume NUMBER
);

/* 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
);

/* INSERT QUERY NO: 6 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '06/01/2021', 36.5, 38.5, 36.5, 38, 123179
);

/* INSERT QUERY NO: 7 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '07/01/2021', 37.5, 39.5, 37.3, 39.4, 282986
);

/* INSERT QUERY NO: 8 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '08/01/2021', 39, 40.5, 38.5, 40, 117437
);

/* INSERT QUERY NO: 9 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '09/01/2021', 39.7, 39.8, 39.3, 39.4, 873009
);

/* INSERT QUERY NO: 10 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '10/01/2021', 39.2, 39.2, 37.2, 37.8, 62522
);

/* INSERT QUERY NO: 11 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '11/01/2021', 38, 38.5, 36.5, 37, 114826
);

/* INSERT QUERY NO: 12 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '12/01/2021', 36.5, 37.9, 36.3, 37.8, 281461
);

/* INSERT QUERY NO: 13 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '13/01/2021', 37.5, 39.5, 37.3, 39.4, 77334
);

/* INSERT QUERY NO: 14 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '14/01/2021', 39, 40.5, 38.5, 40, 321684
);

Below is the sample data for one stock "XYZ":

+-------+------------+-------+------+------+-------+--------+
| Stock | Close Date | Open  | High | Low  | Close | Volume |
+-------+------------+-------+------+------+-------+--------+
| XYZ   | 01-01-2021 |    40 | 40.5 | 38.5 |  38.8 |  83057 |
| XYZ   | 02-01-2021 |  39.2 | 39.2 | 37.2 |  37.8 | 181814 |
| XYZ   | 03-01-2021 |    38 | 38.5 | 36.5 |    37 | 117378 |
| XYZ   | 04-01-2021 |  36.5 | 36.6 | 35.6 |  35.7 |  93737 |
| XYZ   | 05-01-2021 | 35.35 | 36.8 | 35.1 |  36.7 | 169106 |
| XYZ   | 06-01-2021 |  36.5 | 38.5 | 36.5 |    38 | 123179 |
| XYZ   | 07-01-2021 |  37.5 | 39.5 | 37.3 |  39.4 | 282986 |
| XYZ   | 08-01-2021 |    39 | 40.5 | 38.5 |    40 | 117437 |
| XYZ   | 09-01-2021 |  39.7 | 39.8 | 39.3 |  39.4 | 873009 |
| XYZ   | 10-01-2021 |  39.2 | 39.2 | 37.2 |  37.8 |  62522 |
| XYZ   | 11-01-2021 |    38 | 38.5 | 36.5 |    37 | 114826 |
| XYZ   | 12-01-2021 |  36.5 | 37.9 | 36.3 |  37.8 | 281461 |
| XYZ   | 13-01-2021 |  37.5 | 39.5 | 37.3 |  39.4 |  77334 |
| XYZ   | 14-01-2021 |    39 | 40.5 | 38.5 |    40 | 321684 |
+-------+------------+-------+------+------+-------+--------+

Over the period of time, there will be more than thousands of records for each stock symbol and I would like to identify candlestick pattern only at the top of upmove/uptrend or at the bottom of downmove/downtrend but NOT in sideways (Since this will be false positive). Below is the sample screeshot:

Assuming today is 12th Jan 2021, below is the expected output:

+-------+-------------------+------------+------------+--------------+--------+---------------+
| Stock | Consecutive Count | Start Date |  End Date  | Latest Close | Volume |    Pattern    |
+-------+-------------------+------------+------------+--------------+--------+---------------+
| XYZ   |                 3 | 09-01-2021 | 12-01-2021 |         37.8 | 281461 | Piercing Line |
+-------+-------------------+------------+------------+--------------+--------+---------------+

Since the source table will have many other stocks, would like to show results on 12th Jan 2021 for other stocks as well if there is any pattern identified. I feel this is quite challenging and complex logic. Hence seeking help here. Thanks in advance.

Update: Thank you @JustinCave
Here's the formula for calculation: For Bullish Engulfing:

O1 > C1 and C > O and C > H1 and O < L1
where,

O1 = Previous day Open price  
C1 = Previous day Close price  
C  = Today's Close price  
O  = Today's Open price  
H1 = Previous day High price  
L1 = Previous day Low price 

For Bearish Harami:

(O1 < C1) and (O > C) and (O < C1) and (C > O1) and (H < H1) and (L > L1)
where,
    O1 = Previous day Open price  
    C1 = Previous day Close price  
    C  = Today's Close price  
    O  = Today's Open price  
    H1 = Previous day High price  
    L1 = Previous day Low price 
    H  = Today's High price  
    L  = Today's Low price 

For Piercing Line:

(O < C) and (O1 > C1) and (C > (C1 + O1)/2) and (O < C1) and (C < O1)
    where,
        O1 = Previous day Open price  
        C1 = Previous day Close price  
        C  = Today's Close price  
        O  = Today's Open price 

Answer 1

Patterns in MATCH_RECOGNIZE work in a similar fashion to regular expressions; you want something like:

(Note: your PIERCING_LINE formula does not give the expected output so I have assumed you want C > (C1 + O1)/2 rather than C > C1 + (O1/2).)

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
          PREV(open) < PREV(close)
      AND open > close
      AND open < PREV(close)
      AND close > PREV(open)
      AND high < PREV(high)
      AND low > PREV(low),
    piercing_line AS
      -- O < C and O1 > C1 and C > (C1 + O1)/2 and O < C1 and C < O1
          open < close
      AND PREV(open) > PREV(close)
      AND close > (PREV(close) + PREV(open))/2
      AND open < PREV(close)
      AND close < PREV(open)
)

Which outputs:

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

db<>fiddle here

Answer 2

I've upvoted @MT0's answer and I would use match_recognize for this sort of thing myself since this is squarely the sort of problem it is designed to deal with. However, match_recognize is a pretty sophisticated construct and the patterns you're looking for are pretty simple. So as expressed, you could solve your problem with a simpler query that just uses a few lag analytic functions. As the patterns you're looking for get more sophisticated, you'll find that it'll be easier to express them using match_recognize and harder to handle them just with lag but the current problem is relatively easy to express this way.

Note that I'm making the same change to the "Piercing Line" formula that @MT0 did

with data as (
select src.stock,
       src.close_date,
       src.open o,
       src.close c,
       src.high h,
       src.low l,
       lag(src.open) over (partition by src.stock order by src.close_date) o1,
       lag(src.close) over (partition by src.stock order by src.close_date) c1,
       lag(src.high) over (partition by src.stock order by src.close_date) h1,
       lag(src.low) over (partition by src.stock order by src.close_date) l1
  from raw_source src
)
select d.*,
       case when o1 > c1 and c > o and c > h1 and o < l1
            then 'Bullish Engulfing'
            when (O1 < C1) and (O > C) and (O < C1) and (C > O1) and (H < H1) and (L > L1)
            then 'Bearish Harami'
            when (O < C) and (O1 > C1) and (C > (C1 + O1)/2) and (O < C1) and (C < O1)
            then 'Piercing Line'
         end pattern
  from data d
     

which produces the same results in the pattern column in this dbfiddle. Since we can use the same syntax you're using to express the formulas, though, it may be easier to follow the logic in this query than to understand the match_recognize syntax.