Search code examples
sqloracle-databaserecursionoracle-sqldeveloper

Recursive Query in Oracle 21c to look back in the last 3 weeks

Can you please help me in creating a recursive query in Oracle 21c or using other method to solve this problem.

My data has 3 columns: Ride, Client and Date.The column weeknum is added by me.

What I want is a recursive query or something similar to look back in the last 3 weeks taking in consideration each date and retrive the client/s who were present in at least 1 ride in the last 3 weeks(every week no exception) regarding each date, sum them and the result should be like in the column named Final Result.

I put DDL and DML statements in the db fiddle https://dbfiddle.uk/RT1G3uSp including my attemp but what I have tried is not working. First I tried to put the conditions in the join then I tried to join on rn but is not the way it should be.

I want to clarify my request - for client number 1, I want to find out if he was present in any ride in the last 3 weeks and if he was present with at least one ride every week, then in week number 4 it should be marked with 1, in week number 5 and that's why the weeknumber column must also be taken into account, client number 1 was present with a ride in weeks 2, 3 and 4 and again he must be marked with 1 and having a previous 1 we must now have 2. in the week 6, client number 1 was present again with a ride in weeks 3, 4, 5 and again he must have 1 which, added to the previous 2, should have 3.

That's why I said recursive query, every time we have to go back to see if the respective client is present with at least one ride in each of the last 3 weeks. In this way should be applied for all the clients.

I hope it makes more sense now.

Please ask me if something is unclear again.

Thank you for your help in advance

Ride    Client  Date           Weeknum  Final Result
1         1    1/2/2023           1               0
2         1    1/5/2023           1               0
3         3    1/6/2023           1               0
4         1    1/11/2023          2               0
5         2    1/12/2023          2               0
6         1    1/16/2023          3               0
7         2    1/19/2023          3               0
8         1    1/24/2023          4               1
9         2    1/24/2023          4               0
10        1    1/30/2023          5               2
11        2     2/2/2023          5               1
12        2     2/8/2023          6               2
13        1     2/9/2023          6               3
Solution

  • It could be solved by using MATCH_RECOGNIZE:

    WITH cte AS (
   -- single instance per client,week
   SELECT  CLIENT, MIN(RIDE) AS RIDE, trunc(data,'IW') AS date_week_trunc
   FROM PROB
   GROUP BY CLIENT, trunc(data,'IW')
)
SELECT 
  MR.*
  ,COUNT(MR.CLS) OVER(PARTITION BY CLIENT ORDER BY date_week_trunc) AS final_result
FROM cte  T
MATCH_RECOGNIZE (
   PARTITION BY CLIENT
   ORDER BY date_week_trunc
   MEASURES CLASSIFIER() AS CLS
   ALL ROWS PER MATCH WITH UNMATCHED ROWS
   PATTERN (B+)
   DEFINE B AS (    B.date_week_trunc = PREV(B.date_week_trunc,1) + 7
                AND B.date_week_trunc = PREV(B.date_week_trunc,2) + 14
                AND B.date_week_trunc = PREV(B.date_week_trunc,3) + 21
               )
) MR
ORDER BY RIDE;

    Output:

    enter image description here

    db<>fiddle demo

    How it works:

    • get single entry per client per start of the week - date_week_trunc
    • check if there is entry for each of 3 previous weeks - cls
    • compute running total - final_result