I found lot of solution related to my issue on Python technology but couldn't find much on Oracle SQL or PL/SQL solution. So trying it on Oracle SQL with Oracle Application Express.

I have db table that has below sample stock data for 8 days. New records are inserted every day after the market closes. I have query to find the stocks that have consecutive increase/decrease in price. Now I would like to add one more column where I can see a Pattern called bullish or bearish based on the present date and previous date values.

 ------- ------------ -------- ------- ------- -------- -------- 
| Stock |   Cdate    |  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 |  36.1 |  35.6 |   35.7 |  93737 |
| XYZ   | 05-01-2021 |  35.35 |  36.8 |  35.1 |   36.6 | 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 |
|       |            |        |       |       |        |        |
| DDD   | 01-01-2021 | 135.35 | 136.8 | 135.1 |  136.6 |  16454 |
| DDD   | 02-01-2021 |  136.5 | 138.5 | 136.5 |    138 | 281461 |
| DDD   | 03-01-2021 |  137.5 | 139.5 | 137.3 |  139.4 |  77334 |
| DDD   | 04-01-2021 |    139 | 140.5 | 138.5 |    140 | 321684 |
| DDD   | 05-01-2021 |  139.7 | 139.8 | 139.3 |  139.4 | 873009 |
| DDD   | 06-01-2021 |  139.2 | 139.2 | 137.2 |  137.8 |  62522 |
| DDD   | 07-01-2021 |    138 | 138.5 | 136.5 |    137 | 114826 |
| DDD   | 08-01-2021 |  136.6 | 136.8 | 135.1 | 135.35 |  27317 |
 ------- ------------ -------- ------- ------- -------- --------

This is how the chart looks for stock XYZ:

On 5th Jan 2021, the trend changed and I would like to add new column saying "Bullish" based on the formula:

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

Expected output will be a new column saying "Bullish" under column Pattern:

 ------- ------------ ------- ------ ------ ------- -------- --------- 
| Stock |   Cdate    | Open  | High | Low  | Close | Volume | Pattern |
 ------- ------------ ------- ------ ------ ------- -------- --------- 
| 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 | 36.1 | 35.6 |  35.7 |  93737 |         |
| XYZ   | 05-01-2021 | 35.35 | 36.8 | 35.1 |  36.6 | 169106 | Bullish |
| 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 |         |
 ------- ------------ ------- ------ ------ ------- -------- ---------

I searched over the internet for solution and the closest I found was

On 5th Jan 2021, the trend changed and I would like to add new column saying "Bearish" based on the formula:

C < O and C < C1 and O < C1 and O1 < C1 and C > O1 and O < C1

where,

O1 = Previous day Open price  
C1 = Previous day Close price  
C = Today's Close price  
O = Today's Open price  


 ------- ------------ -------- ------- ------- -------- -------- --------- 
| Stock |   Cdate    |  Open  | High  |  Low  | Close  | Volume | Pattern |
 ------- ------------ -------- ------- ------- -------- -------- --------- 
| 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 |  36.1 |  35.6 |   35.7 |  93737 |         |
| XYZ   | 05-01-2021 |  35.35 |  36.8 |  35.1 |   36.6 | 169106 | Bullish |
| 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 |         |
|       |            |        |       |       |        |        |         |
| DDD   | 01-01-2021 | 135.35 | 136.8 | 135.1 |  136.6 |  16454 |         |
| DDD   | 02-01-2021 |  136.5 | 138.5 | 136.5 |    138 | 281461 |         |
| DDD   | 03-01-2021 |  137.5 | 139.5 | 137.3 |  139.4 |  77334 |         |
| DDD   | 04-01-2021 |    139 | 140.5 | 138.5 |    140 | 321684 |         |
| DDD   | 05-01-2021 |  139.7 | 139.8 | 139.3 |  139.4 | 873009 | Bearish |
| DDD   | 06-01-2021 |  139.2 | 139.2 | 137.2 |  137.8 |  62522 |         |
| DDD   | 07-01-2021 |    138 | 138.5 | 136.5 |    137 | 114826 |         |
| DDD   | 08-01-2021 |  136.6 | 136.8 | 135.1 | 135.35 |  27317 |         |
 ------- ------------ -------- ------- ------- -------- -------- ---------

Appreciate any help on this.

Update: Sorry if I asking too much. This is what I am trying to achieve. In simple words, I would like to identify areas highlighted in yellow in below screenshots only if it is in upmove/uptrend or downmove/downtrend.

Stock XYZ, bullish on 05-01-2021

Stock DDD, bearish on 05-01-2021

Thanks,
Richa

CodePudding user response：

From Oracle 12c, you can do it using MATCH_RECOGNIZE:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY stock
  ORDER     BY cdate
  MEASURES
    CLASSIFIER() AS pttrn
  ALL ROWS PER MATCH
  PATTERN (bullish|bearish|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)
)

Which, for the sample data:

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;

Outputs:

STOCK CDATE PTTRN OPEN HIGH LOW CLOSE VOLUME

DDD 01-JAN-21 OTHER 135.35 136.8 135.1 136.6 16454

DDD 02-JAN-21 OTHER 136.5 138.5 136.5 138 281461

DDD 03-JAN-21 OTHER 137.5 139.5 137.3 139.4 77334

DDD 04-JAN-21 OTHER 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 OTHER 139.2 139.2 137.2 137.8 62522

DDD 07-JAN-21 OTHER 138 138.5 136.5 137 114826

DDD 08-JAN-21 OTHER 136.6 136.8 135.1 135.35 27317

XYZ 01-JAN-21 OTHER 40 40.5 38.5 38.8 83057

XYZ 02-JAN-21 OTHER 39.2 39.2 37.2 37.8 181814

XYZ 03-JAN-21 OTHER 38 38.5 36.5 37 117378

XYZ 04-JAN-21 OTHER 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 OTHER 36.5 38.5 36.5 38 123179

XYZ 07-JAN-21 OTHER 37.5 39.5 37.3 39.4 282986

XYZ 08-JAN-21 OTHER 39 40.5 38.5 40 117437

STOCK	CDATE	PTTRN	OPEN	HIGH	LOW	CLOSE	VOLUME
DDD	01-JAN-21	OTHER	135.35	136.8	135.1	136.6	16454
DDD	02-JAN-21	OTHER	136.5	138.5	136.5	138	281461
DDD	03-JAN-21	OTHER	137.5	139.5	137.3	139.4	77334
DDD	04-JAN-21	OTHER	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	OTHER	139.2	139.2	137.2	137.8	62522
DDD	07-JAN-21	OTHER	138	138.5	136.5	137	114826
DDD	08-JAN-21	OTHER	136.6	136.8	135.1	135.35	27317
XYZ	01-JAN-21	OTHER	40	40.5	38.5	38.8	83057
XYZ	02-JAN-21	OTHER	39.2	39.2	37.2	37.8	181814
XYZ	03-JAN-21	OTHER	38	38.5	36.5	37	117378
XYZ	04-JAN-21	OTHER	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	OTHER	36.5	38.5	36.5	38	123179
XYZ	07-JAN-21	OTHER	37.5	39.5	37.3	39.4	282986
XYZ	08-JAN-21	OTHER	39	40.5	38.5	40	117437

You could also use the LAG analytic function (which was available before Oracle 12):

SELECT t.*,
       CASE
       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'
       ELSE 'OTHER'
       END AS pttrn
FROM   table_name t

(Which gives similar output.)

db<>fiddle here

Update: tracking UP and DOWN:

Again, this is what MATCH_RECOGNIZE is for:

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)
)

But you can do the same with LAG:

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

db<>fiddle here