Results 1 to 2 of 2

Thread: Calculate current row cell value with previous row cell value

  1. #1
    Join Date
    Nov 2021
    Location
    INDIA
    Posts
    2

    Post Calculate current row cell value with previous row cell value

    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:
    Code:
    +-----------+-------+------------+--------+
    | 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:
    Code:
    +-----------+-------+----------+--------+---------+-------+
    | 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:
    Code:
    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

  2. #2
    Join Date
    Nov 2021
    Location
    INDIA
    Posts
    2
    Hi All,

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

    Thanks,
    Richa

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •