SQL query on a time series to calculate the average

Question

I have two tables:

temperature table which contains a time series of temperature taken every 15 minutes enter image description here

time table

enter image description here

I need a SQL query, which will allow for an average group by 15 minutes avry values taken for several days

can anyone help me please?

example: for 3 days

day1:00:00->10 ; 00:15->11 ;00:30->9......;23:45->12
day2:00:00->9 ; 00:15->2 ;00:30->5......;23:45->4
day3:00:00->8 ; 00:15->10 ;00:30->8......;23:45->5

how calculate:

avarage1 =10+9+8/3
avarage2 =11+2+10/3
avarage3=9+5+8/3
...
avarage96=12+4+5/3

help me please

Why don't you just use a single [`timestamp` (with or without time zone, depends on your workflow)](http://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/9576170#9576170) to replace all of these columns: `years, months, days, hours, minutes, seconds`.? — Erwin Brandstetter, Feb 23 '15 at 17:04
Solutions with detailed explanation: http://stackoverflow.com/questions/8193688/postgresql-running-count-of-rows-for-a-query-by-minute/8194088#8194088 — Erwin Brandstetter, Feb 23 '15 at 17:06
Crucial detail: Are there values for every 15 minutes, and do you want a row in the result for time slices without values? Also: do you want to query the complete table or just a (small) selection - filtered by which criteria? The best query is decided by this. — Erwin Brandstetter, Feb 23 '15 at 17:09
I have a values every 15 minutes I wanted to calculate the average of each slice (de15minutes) for a period: — user26480, Feb 23 '15 at 17:43
Proper table definitions, the actual query you tried (even if it's not working), your version of Postgres .. that would be the way to write a good question. Details here: http://stackoverflow.com/tags/postgresql-performance/info. — Erwin Brandstetter, Feb 23 '15 at 18:48

score 1 · Accepted Answer · answered Feb 25 '15 at 08:00

If your test case is any indication of your real situation, your timestamps already fall on 15 minute intervals exactly and you don't need to trunc() at all. Just a plain GROUP BY / avg():

SELECT  date_time, avg(value) As avg_val
FROM    temperature te
JOIN    "time" ti USING (id_date)
WHERE   date_time >= '2015-02-24'::date
AND     date_time <  '2015-02-28'::date
GROUP   BY 1;

Selecting the time slice of 3 days from '2015-02-24'::date to '2015-02-27'::date. Note how I include the lower and exclude the upper bound.

Aside: don't use the reserved word "time" as identifier.

score 0 · Answer 2 · answered Feb 23 '15 at 17:04

Create a group for each quarter, then use the avg() aggregate function to calculate the average temperature per group:

select  years
,       months
,       days
,       hours
,       floor(minutes / 15)
,       avg(value)
from    Temperature te
join    Time ti
on      te.id_date = ti.id
group by
        years
,       months
,       days
,       hours
,       floor(minutes / 15)

Or a more explicit alternative way to create a group per quarter:

group by
        years
,       months
,       days
,       hours
,       case
        when minutes < 15 then 1
        when minutes < 30 then 2
        when minutes < 45 then 3
        else 4
        end

SQL query on a time series to calculate the average

2 Answers2

Linked