Login/Register
Oracle sql or pl/sql: Compute based on previous row values and on date column
Asked Answered
Solved sqloracleoracle-apex
O

1

3

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: enter image description here

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 this. I modified the query a bit but was not able to add formula or any other new columns. Below is the query I tried:

WITH StockRow AS (SELECT stock, close, cdate,
                         ROW_NUMBER() OVER(PARTITION BY stock
                                           ORDER BY cdate) rn
                  FROM sampletable where volume > 200000),

     RunGroup AS (SELECT Base.stock, Base.cdate, base.close,
                         MAX(Restart.rn) OVER(PARTITION BY Base.stock
                                              ORDER BY Base.cdate) 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 sampletable)
ORDER BY CONSECUTIVECOUNT desc, stock, startDate

Similarly for stock, "DDD" below is how chart looks and the final data:

Stock DDD, bearish

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 enter image description here

Stock DDD, bearish on 05-01-2021 enter image description here

Thanks,
Richa

Osmose answered 4/10, 2021 at 11:36 Comment(0)
B
7

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

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

Burble answered 4/10, 2021 at 12:18 Comment(3)
Thank you @Burble for quick response. I really appreciate your help. Soory if my question was not clear enough. With the query you provided, I am getting bullish or bearish but I want to use it in combination with query which I posted in OP, so that I can identify if in uptrend or in downtrend. As of now this is giving lot of false signals. Is it possible to combine this query with yours? WITH StockRow AS (SELECT stock, close, cdate, ROW_NUMBER() OVER(PARTITION BY stock ORDER BY cdate) rn FROM sampletable where volume > 200000), RunGroup AS (SELECT Base.stock, Base.cdate... – Osmose
@Osmose Your query returns zero rows db<>fiddle so I am not sure what it is intended to do and it is too complicated to try to reverse engineer it. If you want to identify up and down trends then just use more rules in MATCH_RECOGNIZE or other LAG comparisons; I've update the answer with examples. However, my original queries gave the expected output to your original question. If the original question was not as intended then please don't bait and switch and change the question; ask a new, clearer question. – Burble
Sure let me do that @MT0. It's better for me to post new question rather than to confuse myself and everyone else. Good suggestion. Thank you for your valuable time and help. I appreciate it. I have marked your answer as correct – Osmose

© 2022 - 2024 — McMap. All rights reserved.