Efficient way to get the average of past x events within d days per each row in SQL (big data)

Question

I want to find the best and most efficient way to calculate the average of a score from the past 2 events within 7 days, and I need it per each row. I already have a query that works on 60M rows, but on 100% (~500M rows) of the data its collapses (maybe not efficient or maybe lack of resources). can you help? If you think my solution is not the best way please explain. Thank you

I have this table:

user_id  event_id     start        end       score    
---------------------------------------------------
   1       7       30/01/2021   30/01/2021     45       
   1       6       24/01/2021   29/01/2021     25 
   1       5       22/01/2021   23/01/2021     13    
   1       4       18/01/2021   21/01/2021     15
   1       3       17/01/2021   17/01/2021     52 
   1       2       08/01/2021   10/01/2021     8    
   1       1       01/01/2021   02/01/2021     36

I want per line (user id+event id): to get the average score of the past 2 events in the last 7 days.

Example: for this row:

user_id  event_id     start        end       score    
---------------------------------------------------
   1       6       24/01/2021   29/01/2021     25 


user_id  event_id     start        end       score past_7_days_from_start   event_num  
--------------------------------------------------------------------------------------
   1       6       24/01/2021   29/01/2021     25             null              null
   1       5       22/01/2021   23/01/2021     13              yes               1  
   1       4       18/01/2021   21/01/2021     15              yes               2  
   1       3       17/01/2021   17/01/2021     52              yes               3     
   1       2       08/01/2021   10/01/2021     8               no                4      
   1       1       01/01/2021   02/01/2021     36              no                5

so I would select only this rows for the group by and then avg(score):

user_id  event_id     start        end       score past_7_days_from_start   event_num  
--------------------------------------------------------------------------------------
   1       5       22/01/2021   23/01/2021     13              yes               1  
   1       4       18/01/2021   21/01/2021     15              yes               2

Result:

user_id  event_id   start      end     score avg_score_of_past_2_events_within_7_days   
--------------------------------------------------------------------------------------
   1       6    24/01/2021 29/01/2021   25                  14

My query:

SELECT user_id, event_id, AVG(score) as avg_score_of_past_2_events_within_7_days
FROM (
    SELECT 
        B.user_id, B.event_id, A.score,
        ROW_NUMBER() OVER (PARTITION BY B.user_id, B.event_id ORDER BY A.end desc) AS event_num,
    FROM
        "df" A
    INNER JOIN
        (SELECT user_id, event_id, start FROM "df") B 
            ON B.user_id =  FTP.user_id
            AND (A.end BETWEEN DATE_SUB(B.start, INTERVAL 7 DAY) AND B.start))
WHERE event_num >= 2
GROUP BY user_id, event_id

Any suggestion for a better way?

user_8115345 · Answer

I don't believe in your case, there is a more efficient query.

I can suggest you do the following:

Make sure your base table is partition by start and cluster by user_id
Split the query to 3 parts that creating partitioned and clustered tables:

first table: only the inner join O(n^2)
second table: add ROW_NUMBER O(n)
third table: group by

If it is still a problem I would suggest doing batch preprocessing and run the queries by dates.

user_243782 · Answer

I've tried to create a use case with using LEAD functions, but I am not able to test if works on that large dataset.

I create the two before rows as prev and ante using LEAD. Then I have an IF for the 7 days window, and if that matches I create scorePP and scoreAA otherwise they are null.

with t as (
select   1 as user_id,7 as event_id,parse_date('%d/%m/%Y','30/01/2021') as start,parse_date('%d/%m/%Y','30/01/2021') as stop, 45 as score union all       
select   1 as user_id,6 as event_id,parse_date('%d/%m/%Y','24/01/2021') as start,parse_date('%d/%m/%Y','29/01/2021') as stop, 25 as score union all 
select   1 as user_id,5 as event_id,parse_date('%d/%m/%Y','22/01/2021') as start,parse_date('%d/%m/%Y','23/01/2021') as stop, 13 as score union all    
select   1 as user_id,4 as event_id,parse_date('%d/%m/%Y','18/01/2021') as start,parse_date('%d/%m/%Y','21/01/2021') as stop, 15 as score union all
select   1 as user_id,3 as event_id,parse_date('%d/%m/%Y','17/01/2021') as start,parse_date('%d/%m/%Y','17/01/2021') as stop, 52 as score union all 
select   1 as user_id,2 as event_id,parse_date('%d/%m/%Y','08/01/2021') as start,parse_date('%d/%m/%Y','10/01/2021') as stop, 8  as score union all   
select   1 as user_id,1 as event_id,parse_date('%d/%m/%Y','01/01/2021') as start,parse_date('%d/%m/%Y','02/01/2021') as stop, 36 as score union all
select   2 as user_id,3 as event_id,parse_date('%d/%m/%Y','12/01/2021') as start,parse_date('%d/%m/%Y','17/01/2021') as stop, 52 as score union all 
select   2 as user_id,2 as event_id,parse_date('%d/%m/%Y','08/01/2021') as start,parse_date('%d/%m/%Y','10/01/2021') as stop, 8  as score union all   
select   2 as user_id,1 as event_id,parse_date('%d/%m/%Y','01/01/2021') as start,parse_date('%d/%m/%Y','02/01/2021') as stop, 36 as score 
)
select *, (select avg(x) from unnest([scorePP,scoreAA]) as x) as avg_score_7_day from (
SELECT 
t.*,
lead(start,1) over(partition by user_id  order by event_id desc, t.stop desc) prev_start,
lead(stop,1) over(partition by user_id  order by event_id desc, t.stop desc) prev_stop,
lead(score,1) over(partition by user_id  order by event_id desc, t.stop desc) prev_score,
if(((lead(start,1) over(partition by user_id  order by event_id desc, t.stop desc)) between date_sub(start, interval 7 day) and (lead(stop,1) over(partition by user_id  order by event_id desc, t.stop desc))),lead(score,1) over(partition by user_id  order by event_id desc, t.stop desc),null) as scorePP,
/**/
lead(start,2) over(partition by user_id  order by event_id desc, t.stop desc) ante_start,
lead(stop,2) over(partition by user_id  order by event_id desc, t.stop desc) ante_stop,
lead(score,2) over(partition by user_id  order by event_id desc, t.stop desc) ante_score,
if(((lead(start,2) over(partition by user_id  order by event_id desc, t.stop desc)) between date_sub(start, interval 7 day) and (lead(stop,2) over(partition by user_id  order by event_id desc, t.stop desc))),lead(score,2) over(partition by user_id  order by event_id desc, t.stop desc),null) as scoreAA,
 from 
 t
) 
where coalesce(scorePP,scoreAA) is not null 
order by user_id,event_id desc

user_5221944 · Answer

Consider below approach

select * except(candidates1, candidates2),
  ( select avg(score) 
    from (
      select * from unnest(candidates1) union distinct 
      select * from unnest(candidates2)
      order by event_id desc 
      limit 2
    )
  ) as avg_score_of_past_2_events_within_7_days
from (
  select *, 
    array_agg(struct(event_id, score)) over(order by unix_date(t.start) range between 7 preceding and 1 preceding) as candidates1,
    array_agg(struct(event_id, score)) over(order by unix_date(t.end) range between 7 preceding and 1 preceding) as candidates2
  from your_table t
)

if applied to sample data in your question - output is

Efficient way to get the average of past x events within d days per each row in SQL (big data)

3 Answers