mysql - 可以根据日期时间字段的单个元素过滤 SQL 行吗？

Question

我有一个 MySQL 数据库，存储了一个长而详细的时间序列(即，每分钟采样多次的多年测量值)。当我将此数据提供给客户端进行显示时，我想对数据进行下采样，这样我就不会向图表发送大量数据。例如，如果客户端需要显示 1 年的数据，我不想发送该年的所有样本；比如说，我宁愿每小时发送一分。

我正在使用 Flask/SQLAlchemy，显然我可以从该时间间隔中提取所有数据并在 Flask 中处理它，但为了提高效率，我更愿意使用 SQL/SQLAlchemy 来完成此操作，所以我没有重新实现 Flask-SQLAlchemy 的分页。一种方法是根据日期时间过滤具有某些时间字段==0的行；例如，如果我得到一年的数据，其中 MM:SS==00:00，我每小时会得到一个点，这几乎是我所需要的。这可能吗？有没有更好的方法从 SQL 或 SQLAlchemy 中的查询结果中获取每第 N 行？

为清楚起见进行编辑:我正在寻找一种有效的方法来返回 DATETIME 范围内的每 N 行。我可以很容易地得到一个范围内的每一行；这是每 N 次下采样让我着迷。我不一定需要这个来为任意 N 工作；如果我能得到，比如每行分钟==0和秒==0，或者每行秒==0，等等，就足够了。

Answer 1

可以使用分组依据和汇总函数获取每小时的单个值，例如 min() 或 avg():

select date_format(observation_date, "%Y %m %d %H") as obs_hour, avg(observation_value) as avg_value
from observations
group by date_format(observation_date, "%Y %m %d %H")

上述查询将使用 date_format() 按小时对观测值进行分组函数，将为您提供一小时内观察到的值的平均值。您可以使用不同的汇总函数(例如 min() 或 max())从每个组中获取适合您的采样技术的不同值。

您还可以使用以下条件获取在某一小时内准确进行的观察结果:

select *
from observations
where minute(observation_date)=0 and second(observation_date)=0