CODEWITHSUNDEEP
postgresqltimeaggregationwindow-functions
f.ashouri
f.ashouri

Reputation: 5687

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?

Upvotes: 4

Views: 4284

Answers (2)

user330315
user330315

Reputation: 

select id, 
       temperature, 
       sum(temperature) over (order by "date" rows between 10 preceding and current row)
from the_table;

It might not exactly be what you want, as it will do a moving sum over the last 10 rows, which is not necessarily the same as the last 10 days.

Since Postgres 11, you can now use a range based on an interval

select id, 
       temperature, 
       avg(temperature) over (order by "date" 
                                range between interval '10 days' preceding and current row)
from the_table;

Upvotes: 3

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125464

SQL Fiddle

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

Upvotes: 9

Related Questions