采样 SQL 时间序列

问题描述

我有一个日期时间的时间序列，双列存储在 mySQL 中，并且希望每分钟对时间序列进行采样(即以一分钟为间隔提取最后一个值).有没有一种有效的方法可以在一个 select 语句中做到这一点?

I have a timeseries of datetime, double columns stored in mySQL and would like to sample the timeseries every minute (i.e. pull out the last value at one minute intervals). Is there an efficient way of doing this in one select statement?

蛮力方式将涉及选择整个系列并在客户端进行采样或为每个点发送一个选择(例如 select * from data where timestampselect * from data where timestamp).

The brute force way would involve either selecting the whole series and doing the sampling on the client side or sending one select for each point (e.g. select * from data where timestamp<xxxxxxxxx order by timestamp desc limit 1).

推荐答案

您可以将时间戳转换为 UNIX 时间戳，按 unix_timestamp DIV 60 分组并从每个组中提取最大时间戳.之后将获取到的列表加入到原表中，以获取获取到的时间戳的数据.

You could convert your timestamps to UNIX timestamps, group by unix_timestamp DIV 60 and pull the maximum timestamps from each group. Afterwards join the obtained list back to the original table to pull the data for the obtained timestamps.

基本上它可能看起来像这样:

Basically it might look something like this:

SELECT
  t.*  /* you might want to be more specific here */
FROM atable t
  INNER JOIN (
    SELECT
      MAX(timestamp) AS timestamp
    FROM atable
    GROUP BY UNIX_TIMESTAMP(timestamp) DIV 60
  ) m ON t.timestamp = m.timestamp

这篇关于采样 SQL 时间序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！