Function to get rolling average with lowest 2 values eliminated?

Question

This is my sample data with the current_Rating column my desired output.


Date         Name    Subject         Importance    Location     Time      Rating  Current_rating
12/08/2020   David   Work            1             London       -         -       4
1/08/2020    David   Work            3             London       23.50     4       3.66
2/10/2019    David   Emails          3             New York     18.20     3       4.33
2/08/2019    David   Emails          3             Paris        18.58     4       4
11/07/2019   David   Work            1             London       -         3       4
1/06/2019    David   Work            3             London       23.50     4       4
2/04/2019    David   Emails          3             New York     18.20     3       5
2/03/2019    David   Emails          3             Paris        18.58     5       -
12/08/2020   George  Updates         2             New York     -         -       2
1/08/2019    George  New Appointments5             London       55.10     2       -

I need to use a function to get values in the current_Rating column.The current_Rating gets the previous 5 results from the rating column for each name, then eliminates the lowest 2 results, then gets the average for the remaining 3. Also some names may not have 5 results, so I will just need to get the average of the results if 3 or below, if 4 results I will need to eliminate the lowest value and average the remaining 3. Also to get the right 5 previous results it will need to be sorted by date. Is this possible? Thanks for your time in advance.

It sounds like you need to create a custom aggregate function. — Hernán Alarcón
Do you need only average number of first 3 values? Do you need function to get this result? Do you need average result of only one name or all? There are so many unclear points. — Abdusoli
@Abdusoli Sorry if I was unclear. It needs to be the average of the highest 3 numbers, from the past 5 results. It needs to be a function to get every entry this rolling average. If no previous meeting for the name then it will return null. — undecided000

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-08-12T12:49:55

What a pain! I think the simplest method might be to use arrays and then unnest() and aggregate:

select t.*, r.current_rating
from (select t.*,
             array_agg(rating) over (partition by name order by date rows between 4 preceding and current row) as rating_5
      from t
     ) t cross join lateral
     (select avg(r) as current_rating
      from (select u.*
            from unnest(t.rating_5) with ordinality u(r, n)
            where r is not null
            order by r desc desc
            limit 3
           ) r
     ) r

Function to get rolling average with lowest 2 values eliminated?

1 Answers