Search code examples
postgresqltimeaggregationwindow-functions

Grouping based on every N days in postgresql

I have a table that includes ID, date, values (temperature) and some other stuff. My table looks like this: 

+-----+--------------+------------+
| ID  |  temperature |    Date    |
+-----+--------------+------------+
|  1  |  26.3        | 2012-02-05 |
|  2  |  27.8        | 2012-02-06 |
|  3  |  24.6        | 2012-02-07 |
|  4  |  29.6        | 2012-02-08 |
+-----+--------------+------------+

I want to perform aggregation queries like sum and mean for every 10 days.

I was wondering if it is possible in psql or not?

Solution

  • SQL Fiddle

    select
    "date",
    temperature,
    avg(temperature) over(order by "date" rows 10 preceding) mean
from t
order by "date"