CODEWITHSUNDEEP
sqlwindow-functions
santoku
santoku

Reputation: 3427

calculate window average without partition or order by column in SQL

If I have a data frame with only number of clicks at certain fixed time interval looking like this:

1
3
4
2
6
1

And I want to calculate their rolling average with the 5 rows above, would this be legit:

SELECT AVG(value) OVER (ORDER BY 1 ASC ROWS 4 PRECEDING ) AS avg_value FROM df GROUP BY 1

Or should it be

SELECT AVG(value) OVER (PARTITION BY 1 ASC ROWS 4 PRECEDING) AS avg_value FROM df GROUP BY 1

Upvotes: 0

Views: 905

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You seem to want:

SELECT df.*,
       AVG(value) OVER (ORDER BY datetimecol ASC
                        ROWS 4 PRECEDING
                       ) AS avg_rolling_5
FROM df;

Notes:

  • A rolling average requires implies an ordering on the data. The datetimecol is for the column that represents that ordering.
  • A rolling average is for the original data, not the aggregated data, so no order by is needed.
  • SQL databases have tables, not dataframes.

Upvotes: 0

Related Questions