时间戳

首页 > 解决方案 > 为每第 x 行 PostgreSQL 创建过去 x 小时的平均值

sql - 为每第 x 行 PostgreSQL 创建过去 x 小时的平均值

问题描述

我想根据 postgresql 数据库中的数据为应用程序创建一个图表。因此，我想在总时间范围为 Y 小时（例如 8 小时）的可变时间跨度（例如每 10 分钟）中创建我的值的最后 X 小时（例如 2 小时）的平均值。

图片： https ://i.ibb.co/C8v1mXD/Bildschirmfoto-2019-09-03-um-11-52-51.png

我的 postgreSQL DB 有一个 ID、一个值和一个时间戳列。我尝试了很多与“group by”和“over”一起工作，但不幸的是我没有实现我的目标。也许你们中的一些人很好，能够帮助我？

图片： https ://i.ibb.co/sKpYCbJ/Bildschirmfoto-2019-09-03-um-12-03-42.png

标签： sqlpostgresql

解决方案

如果您使用的是 PG11+，那么范围窗口函数可能会对您有所帮助：

SELECT  
  avg(t.average_me) OVER(ORDER BY t.timestamp_col RANGE BETWEEN INTERVAL '3 hour' PRECEDING AND CURRENT ROW) as a 
FROM yourtable t;

如果timestamp_col每行 R 都有一个 then 的行，这将计算average_meR 的 timestamp_col 和它之前 10 小时的日期之间所有行的平均值。您也可以移动窗口：

SELECT  
  avg(t.average_me) OVER(ORDER BY t.timestamp_col RANGE BETWEEN INTERVAL '3 hour' PRECEDING AND INTERVAL '2 hour' PRECEDING) as a 
FROM yourtable t;

对于 timestamp_col 为 的行 R，这将计算2000-01-01 12:00:00timestamp_col 介于2000-01-01 9:00:00和之间的所有行的平均值2000-01-01 10:00:00

在我发表评论后更新（未经测试）：

SELECT x.* FROM(

 SELECT  
  CASE WHEN kind = avgpoint' THEN 
    avg(t.average_me) OVER(ORDER BY t.timestamp_col RANGE BETWEEN INTERVAL '2 hour' PRECEDING AND INTERVAL '1 hour' PRECEDING)
  END as a 
 FROM 
 (
  --your data
  SELECT 'datarow' as kind, average_me, timestamp_col 
  FROM yourtable;

  UNION ALL

  --your checkpoints, every 15 minutes from 10h ago to now
  SELECT 'avgpoint', null, g.v 
  FROM generate_series(
    now()-'10 hours'::interval, 
    now(),
    '15 minute'::interval
  ) as g(v)
 ) t
) x
WHERE x.kind = 'avgpoint'

它将一堆 15 分钟的间隔插入到数据流中，使用不同的类型（因此可以检测到）。对于每个 'avgpoint' 类型的行，AVG()OVER() 回顾 2 小时到 1 小时前的数据并取平均值。这意味着每 15 分钟您会获得前一小时的平均值：中午时，您会获得上午 10 点到上午 11 点的平均值。在下午 12:15，您将获得 10:15 到 11:15 等

推荐阅读