Grouping based on every N days in postgresql

aggregation, postgresql, time, window-functions

Solution

SQL Fiddle

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

Problem

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?

Original source

Related problems