Oracle SQL or PL/SQL: Add multiple columns in MATCH_RECOGNIZE with different calculations or computed columns

Question

Happy Thanksgiving!!

This post is for learning and educational purpose only

Structure of table, insert statements and SQL query are updated in below db fiddle link to keep this post clean and short. Please refer below link:

db<>fiddle

I am trying to calculate "7 Days moving average" and also to add computed columns based on this new column "7 days moving average". Tried different methods and was getting error "window functions are not allowed here". Later tried in excel spreadsheet and got the formula working. Still not sure how to achieve this in SQL with Match_Recognize and hence seeking help from you all experts.

Please note: I am uploading screenshots of excel and formulae since stackoverflow not giving me option to upload the excel spreadsheet.

Below is the output I am expecting (Column highlighted in yellow):

Update: Thank you for suggestion and for correcting me. Updated the post.

Tricky Part:
Column "7_MOV_AVG": Row 8 (As per excel serial number) is average of cells E2 to E8
Whereas Row 9 (As per excel serial number) onwards the formula considers previous row values and not sure how to achieve that. Below is the screenshot for the calculation:

Appreciate any help with this. Thanks in advance.

Thanks,
Richa

Answer 1

Looks like this is what you need: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=611827551a8d454d69a0986ea8c0b2da

with
 rs_fixed as (select stock,to_date(Close_Date,'dd-mm-yyyy') as Close_Date, price from raw_source) 
,pre as (
    SELECT *
    FROM   rs_fixed
    MATCH_RECOGNIZE (
      PARTITION BY stock
      ORDER BY Close_Date
      MEASURES
        COUNT(*) AS match_size,
        CLASSIFIER() AS pttrn
      ALL ROWS PER MATCH
      PATTERN (
        down+  | up+  | other
      )
      DEFINE
        down AS PREV(price) > price,
        up AS  PREV(price) < price
    )
)
select 
    pre.*
   ,avg(price)
        over(
            partition by stock 
            order by Close_Date
            range between 7 preceding and 1 preceding
            ) as "7_mov_avg"

from pre

As you can see, I fixed your definition of close_date - it should be date not varchar2 for dates arithmetic. Also I don't know why you aggregate C2-C9 for avg for cell C8, so I have changed it as per your description for previous 7 days, not 5 preceding and 1 following, but if you need it really, you can replace windowsing clause above to range between 5 preceding and 1 following. And, finally, if you need to show only rows after 7th day, you can use case clause from the last query in DBFiddle.