如何获得7天前同一时间的百分比变化?

Question

我有一个大的PostgreSQL数据库，里面有时间序列数据。

我查询采样时间为1小时的数据。我想要的是比较最后一个小时的平均值和7天前同一时间的值，我不知道怎么做。

这是我用来获取最新值的方法。

SELECT DATE_TRUNC('hour', datetime) AS time, AVG(value) as value, id FROM database 
GROUP BY id, time 
WHERE datetime > now()- '01:00:00'::interval

Answer 1

您可以使用CTE计算同一时间段内上周的平均值，然后加入id和小时。

with last_week as
(
SELECT 
id,  
extract(hour from datetime) as time, 
avg(value) as avg_value
FROM my_table 
where DATE_TRUNC('hour', datetime) = 
(date_trunc('hour', now() - interval '7 DAYS'))
group by 1,2
)
select n.id, 
DATE_TRUNC('hour', n.datetime) AS time_now, 
avg(n.value) as avg_now, 
t.avg_value as avg_last_week
from my_table n
left join last_week t
on t.id = n.id
and t.time = extract(hour from n.datetime)
where datetime > now()- '01:00:00'::interval
group by 1,2,4
order by 1

我对你的数据如何显示做了一些假设。

**编辑-刚刚注意到您要求更改百分比

以十进制显示变化

select id, 
extract(hour from time_now) as hour_now, 
avg_now, 
avg_last_week, 
coalesce(((avg_now - avg_last_week) / avg_last_week), 0) AS CHANGE
from (
with last_week as
(
SELECT 
id,  
extract(hour from datetime) as time, 
avg(value) as avg_value
FROM my_table 
where DATE_TRUNC('hour', datetime) = 
(date_trunc('hour', now() - interval '7 DAYS'))
group by 1,2
)
select n.id, 
DATE_TRUNC('hour', n.datetime) AS time_now, 
avg(n.value) as avg_now, 
t.avg_value as avg_last_week
from my_table n
left join last_week t
on t.id = n.id
and t.time = extract(hour from n.datetime)
where datetime > now()- '01:00:00'::interval
group by 1,2,4
)z
group by 1,2,3,4
order by 1,2

db-fiddle在这里找到:https://www.db-fiddle.com/f/rWJATypGzHPZ8sG2vXAGXC/4