CODEWITHSUNDEEP
sqlpostgresqlselectaveragewindow-functions
P. Saha
P. Saha

Reputation: 59

Using SQL, how could I take a rolling average over a given number of days across an unspecified number of records each of those days?

I have the following fields. Each record below is unique.

I want to get a rolling average of the field "score" over all of the preceding days for each distinct "record_type" and "something_ind"

Data:

enter image description here

Desired Output: enter image description here

SQL query:

select
     date
    ,record_type
    ,something_ind
    ,avg(score) over(partition by date, record_type, something_ind order by date, record_type, something_ind rows between 6 preceding and current row as rolling_average_score
from table
group by 1,2,3

Upvotes: 0

Views: 100

Answers (1)

GMB
GMB

Reputation: 222582

I want to get a rolling average of the field score over all of the preceding days for each distinct record_type and something_ind.

Use window functions. As I understood your question, you want:

select
    t.*,
    avg(score) over(
        partition by record_type, something_ind 
        order by date
    ) avg_score
from mytable t

Upvotes: 1

Related Questions