可以在datetime字段的单个元素上过滤SQL行吗？(Possible to filter SQL rows on a single element of a datetime field?)

可以在datetime字段的单个元素上过滤SQL行吗？(Possible to filter SQL rows on a single element of a datetime field?) mysql

我有一个MySQL数据库，存储一个长而详细的时间序列（即几年/分钟采样的多年测量）。 当我将这些数据提供给客户端进行显示时，我想对数据进行下采样，这样我就不会向图表发送大量数据。 例如，如果客户需要显示1年的数据，我不想发送该年的每个样本; 我宁愿每小时发一个点。

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

为清晰起见编辑：我正在寻找一种有效的方法来返回DATETIME范围内的每个第N行。 我可以非常轻松地获得范围内的每一行; 这是我得到的每N次下采样。 我不一定需要这个为任意N工作; 如果我能得到它就足够了，比如说每一行都有分钟== 0和秒== 0，或每行有秒== 0等等。

I have a MySQL database storing a long, detailed time series (i.e., years of measurements sampled several times/minute). When I give this data to a client for display, I'd like to downsample the data so that I'm not sending massive amounts of data to graph. For example, if the client needs to display 1 year's worth of data, I don't want to send every sample from that year; I'd rather send, say, one point for every hour.

I'm using Flask/SQLAlchemy, and obviously I could just pull all the data from that interval and process it in Flask, but I'd prefer to accomplish this with SQL/SQLAlchemy for efficiency and so I don't have to re-implement Flask-SQLAlchemy's pagination. One way to do this would be to filter on datetime for rows that have some time fields==0; for example, if I got a year's worth of data where MM:SS==00:00, I'd get one point per hour, which is pretty much what I need. Is this possible? Is there a better way to get every Nth row from a query result in SQL or SQLAlchemy?

Edit for clarity: I am looking for an efficient way to return every Nth row in a DATETIME range. I can get every row in a range pretty easily; it's the every-Nth downsampling that's got me. I don't necessarily need this to work for arbitrary N; it's sufficient if I can get, say every row with minutes==0 and seconds==0, or every row with seconds==0, etc.

最满意答案

使用group by和summary函数可以每小时获得一个值，例如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

It is possible to get a single value per hour using group by and summary function, such as min() or 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")

The above query will group the observations by hour using date_format() function and will give you the average of the observed values within the hour. You can use different summary functions, such as min() or max() to get different value from each group as befitting for your sampling technique.

You can also get those observations that were taken exactly at a round hour using where criteria:

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