Grouping based on every N days in postgresql

Question

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?

What do you mean by "every 10 days?" Do you want a moving window? 1-10, 2-11, 3-12, etc? Or would 1-10, 11-20, and 21-31 be good enough? — Marvo, Oct 25 '12 at 21:42
I mean moving window. But if it is not really possible in psql I have to resort to the second option. Thank you — f.ashouri, Oct 25 '12 at 21:47

score 9 · Accepted Answer · answered Oct 25 '12 at 22:17

9

SQL Fiddle

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

answered Oct 25 '12 at 22:17

Clodoaldo Neto

118,695
26
233
260

Thank you Clodoaldo. Your solution works for me. But I also need to calculate them for every separate 10 days period. like calculating average first 10 days, second 10 days . . . – f.ashouri Oct 25 '12 at 23:54
1

@user1043898 That's really a different question, but it's already well answered elsewhere. Use `group by extract(epoch from the_date) / extract(epoch from interval '10' day)` . See http://stackoverflow.com/questions/12921598/efficiently-querying-a-huge-time-series-table-for-one-row-every-15-minutes, http://stackoverflow.com/questions/12623358/group-by-data-intervals – Craig Ringer Oct 26 '12 at 00:21
Unfortunately I am not able to perform the query. One explanation is that the records are for every day, and 10 day grouping can be done by grouping of every 10 IDs. But I don't know how to calculate every 10 records. – f.ashouri Oct 27 '12 at 00:24

score 3 · Answer 2 · 2020-03-26T07:30:17.963

3

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;

edited Mar 26 '20 at 07:30

answered Oct 25 '12 at 22:07

Thank you this is exactly what I mean, but I got this error: ERROR: frame starting from current row cannot have preceding rows ^ – f.ashouri Oct 25 '12 at 23:22
1

@user1043898: sorry, got the limits mixed up. See my edit please. – Oct 26 '12 at 06:20

Grouping based on every N days in postgresql

2 Answers2