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

evrscar2  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(113)

我有一个小的PostgreSQL表,有两列:

  • time(Unix时间戳,以毫秒为单位)
  • speed(一个整数)

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

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

字符串

ndasle7k

ndasle7k1#

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

  1. SELECT
  2. time_group
  3. , avg(speed) AS avg_speed
  4. , count(*) AS sample_size -- optional
  5. FROM (
  6. SELECT date_trunc('minute', time) + INTERVAL '5 min' * floor(extract(minute from time) / 5) AS time_group, speed
  7. FROM your_table
  8. ) subquery
  9. GROUP BY
  10. time_group

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

  1. SELECT time_group, speed
  2. FROM (
  3. SELECT
  4. time_group,
  5. speed,
  6. ROW_NUMBER() OVER (PARTITION BY time_group ORDER BY random()) AS row_num
  7. FROM (
  8. SELECT
  9. date_trunc('minute', time) + INTERVAL '5 min' * floor(extract(minute from time) / 5) AS time_group,
  10. speed
  11. FROM your_table
  12. ) subquery
  13. ) numbered_rows
  14. WHERE row_num = 1


(nb:它只会是伪随机的,特别是当样本量很小时)

展开查看全部

相关问题