postgresql 在不规则的写入时间获取每第n个值

evrscar2 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(1)|浏览(112)

我有一个小的PostgreSQL表，有两列：

time（Unix时间戳，以毫秒为单位）
speed（一个整数）

我以不规则的时间间隔将值插入到这个表中，有时每分钟插入多次，有时几个小时都没有写入。
目前，我遇到了一个问题，我想每5或10分钟检索一个值。
例如，在14：00到14：05之间，可能有100个条目，我想获得这些条目的平均值（记住，我每隔第n分钟只需要一个值）。如果在14：05到15：00之间没有值，则不应检索任何值。然后在15：00到15：30之间有一个值，该单个值应是我检索的下一个值。
因此，如果到下一个条目的距离< 5分钟，则基本上建立平均值。否则取每个值。
这是我迄今为止尝试过的方法，但它并没有给我给予想要的结果：

SELECT
  MIN(time) AS start_time,
  AVG(speed) AS average_data
FROM my_table
WHERE time >= EXTRACT(EPOCH FROM NOW() - INTERVAL '24 hours') * 1000
GROUP BY FLOOR(EXTRACT(EPOCH FROM to_timestamp(time / 1000)) / 300)
ORDER BY start_time;

字符串

postgresql

来源：https://stackoverflow.com/questions/77488371/get-every-n-th-value-at-irregular-write-times

1条答案

按热度按时间

ndasle7k1#

如果你只想要每5分钟的平均值（存在于数据中），那么使用group by，这将确保每个“时间组”只有一行：

SELECT
       time_group
     , avg(speed) AS avg_speed
     , count(*) AS sample_size -- optional
FROM (
   SELECT date_trunc('minute', time) + INTERVAL '5 min' * floor(extract(minute from time) / 5) AS time_group, speed
   FROM your_table
) subquery
GROUP BY
       time_group

字符串
我对只有一行的措辞有点困惑，所以：
为每5分钟的持续时间计算一个“时间组”，然后在每个现有的“时间组”中分配一个行号，但使用随机顺序。然后只需从每个现有的“时间组”中选择1行。

SELECT time_group, speed
FROM (
   SELECT 
      time_group, 
      speed,
      ROW_NUMBER() OVER (PARTITION BY time_group ORDER BY random()) AS row_num
   FROM (
      SELECT 
         date_trunc('minute', time) + INTERVAL '5 min' * floor(extract(minute from time) / 5) AS time_group, 
         speed
      FROM your_table
   ) subquery
) numbered_rows
WHERE row_num = 1

型
(nb：它只会是伪随机的，特别是当样本量很小时）

赞(0）回复(0）举报 2023-11-18

我来回答

postgresql 在不规则的写入时间获取每第n个值

1条答案

相关问题

热门标签

最新问答