CODEWITHSUNDEEP
sqlpostgresql
mxgoncharov
mxgoncharov

Reputation: 515

SQL query for calculating average in every n rows with step 1

I have some data like this

KEYS: {id, score, user_id}

VALUES:

{1, 23, 2},

{1, 23, 2},

{2, 27, 2},

{3, 42, 2},

{4, 71, 2},

{5, 11, 2}

I need SQL which will return MAX value of AVERAGE score of each 3 rows WITH STEP 1

For example.

1st AVG = AVG(score) WHERE id IN 1,2,3

2st AVG = AVG(score) WHERE id IN 2,3,4

And others...

In the end, I need MAX VALUE OF AVERAGES.

Thank you very much

Upvotes: 2

Views: 3900

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use the avg window function with a window frame specification to consider the current row and the next 2 rows. I assume id column is a primary key in the table.

select max(avg_score)
from (select avg(score) over(order by id rows between current row and 2 following) as avg_score
      from t
     ) x

You should exclude the last 2 rows from this result. Because

  • nth row will have avg_score=score as there is only one row in the window
  • avg_score of the (n-1)th row will be (value of nth row + value of n-1th row)/2 as there are only 2 rows in the window

To exclude them use,

select max(avg_score)
from (select row_number() over(order by id desc) as rn
      ,avg(score) over(order by id rows between current row and 2 following) as avg_score
      from t
     ) x
where rn > 2 --excluding the last 2 rows

If the above needs to be done for each user_id, add a partition by specification as shown.

select distinct user_id,max(avg_score) over(partition by user_id) as max_avg
from (select row_number() over(partition by user_id order by id desc) as rn
      ,avg(score) over(partition by user_id order by id rows between current row and 2 following) as avg_score
      from t
     ) x
where rn > 2 --excluding the last 2 rows

Upvotes: 2

Related Questions