SQL查询时间序列以计算平均值 [英] SQL query on a time series to calculate the average

问题描述

我有两个表：

温度表，其中包含每15分钟获取的温度的时间序列

temperature table which contains a time series of temperature taken every 15 minutes

时间表

我需要一个SQL查询，这将允许平均每组15分钟，平均几天内获取的值

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

有人可以帮助我吗？

示例：3天

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

如何计算：

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

请帮助我

推荐答案

如果您的测试这种情况表明您的真实情况，您的时间戳记恰好间隔15分钟，完全不需要 trunc（）。只是普通的 GROUP BY / avg（）：

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;

从'2015-02-24':: date到'2015选择3天的时间片-02-27':: date。请注意，我是如何包含下限并排除上限的。

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.

此外：请勿使用保留的单词 time作为标识符。

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

这篇关于SQL查询时间序列以计算平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！