Calculate current row cell value with previous row cell value

[richa]

Hi All,

Happy Thanksgiving!!

Basically I am trying to calculate moving average which considers current row value with previous row value but unable to succeed. Hence reaching out here to you experts.

Here's the db<>fiddle tried so far which has table, data and query.

I have data as below:

+-----------+-------+------------+--------+
| Serial_No | Stock | Close_Date | PRICE  |
+-----------+-------+------------+--------+
|         1 | XYZ   | 01-01-2021 |    315 |
|         2 | XYZ   | 02-01-2021 |  311.1 |
|         3 | XYZ   | 03-01-2021 |  308.1 |
|         4 | XYZ   | 04-01-2021 |    310 |
|         5 | XYZ   | 05-01-2021 |  308.7 |
|         6 | XYZ   | 06-01-2021 |  311.2 |
|         7 | XYZ   | 07-01-2021 | 313.75 |
|         8 | XYZ   | 08-01-2021 |  314.7 |
|         9 | XYZ   | 09-01-2021 | 308.05 |
|        10 | ABC   | 01-01-2021 |     14 |
|        11 | ABC   | 02-01-2021 |     12 |
|        12 | ABC   | 03-01-2021 |     11 |
|        13 | ABC   | 04-01-2021 |      8 |
|        14 | ABC   | 05-01-2021 |      9 |
|        15 | ABC   | 06-01-2021 |      7 |
|        16 | ABC   | 07-01-2021 |      5 |
|        17 | ABC   | 08-01-2021 |     10 |
+-----------+-------+------------+--------+

Below is the output I am expecting:

+-----------+-------+----------+--------+---------+-------+
| SERIAL_NO | STOCK |  CDATE   | PRICE  | MOV_AVG | FLAG  |
+-----------+-------+----------+--------+---------+-------+
|         1 | XYZ   | 1-Jan-21 |    315 |         |       |
|         2 | XYZ   | 2-Jan-21 |  311.1 |         |       |
|         3 | XYZ   | 3-Jan-21 |  308.1 |         |       |
|         4 | XYZ   | 4-Jan-21 |    310 |         |       |
|         5 | XYZ   | 5-Jan-21 |  308.7 |  310.58 | Red   |
|         6 | XYZ   | 6-Jan-21 |  311.2 |  310.89 | Green |
|         7 | XYZ   | 7-Jan-21 | 313.75 |  312.32 | Green |
|         8 | XYZ   | 8-Jan-21 |  314.7 |  313.51 | Green |
|         9 | XYZ   | 9-Jan-21 | 308.05 |  310.78 | Red   |
|        10 | ABC   | 1-Jan-21 |     14 |         |       |
|        11 | ABC   | 2-Jan-21 |     12 |         |       |
|        12 | ABC   | 3-Jan-21 |     11 |         |       |
|        13 | ABC   | 4-Jan-21 |      8 |         |       |
|        14 | ABC   | 5-Jan-21 |      9 |   10.80 | Red   |
|        15 | ABC   | 6-Jan-21 |      7 |    8.90 | Red   |
|        16 | ABC   | 7-Jan-21 |      5 |    6.95 | Red   |
|        17 | ABC   | 8-Jan-21 |     10 |    8.48 | Green |
+-----------+-------+----------+--------+---------+-------+

Calculation and formula:
Here row 5 moving average is calculated as average price of row 1 to 5
whereas for row 6 onwards, it must consider value of row 5 MOV_AVG and also row 6 PRICE value.
Below two screenshots shows the formula from excel:
To calculate 5 days MOVING AVERAGE till row 5
soavg.png

Formula to calculate moving average from row 6:
soavgcalc.png

Formula to calculate new column FLAG:
If PRICE is greater than MOV_AVG value then Green else Red.

I was able to achieve calculation till row 5 but not sure how to do it from row 6 onwards. Below is the code tried:

with 
 rd_fixed as (select SERIAL_NO,STOCK,CDATE,PRICE from rawdata) 
,pred as (
    SELECT
        SERIAL_NO,STOCK,CDATE,PRICE
    FROM   rd_fixed
    MATCH_RECOGNIZE (
      PARTITION BY STOCK
      ORDER BY CDATE
      MEASURES
        COUNT(*) AS VALUE_MATCH,
        CLASSIFIER() AS PATTERN
      ALL ROWS PER MATCH
      PATTERN (
        down+  | up+  | other
      )
      DEFINE
        down AS PREV(price) > price,
        up AS  PREV(price) < price
    )
)
,moving_average as (
    select
        pred.*
       ,round(
            avg(PRICE)
                over(
                    partition by STOCK 
                    order by CDATE
                    range between 5 preceding and current row
                    )
            ,2) as "MOV_AVG"
       ,count(*)
            over(
                partition by STOCK 
                order by CDATE
                range between 5 preceding and current row
                ) as cnt5
    from pred
)
select
    ma.*
   ,case when cnt5 = 5 then round("MOV_AVG",2) end as only_5_MOV_AVG
from moving_average ma

Forum not allowing to attach excel spreadsheet that includes formula. Hence tried to describe the problem in detail. If there is a way to attach excel please let me know.

Appreciate any help.
Thanks in advance.

Thanks,
Richa

[richa]

Hi All,

Please ignore this post. Before this OP was approved, I found answer. Thank you.

Thanks,
Richa