SQL:为最接近特定时间的每一天选择一条记录 [英] SQL: select one record for each day nearest to a specific time

问题描述

我有一个表存储了一个时间点的值:

I have one table that stores values with a point in time:

CREATE TABLE values
(
    value DECIMAL,
    datetime DATETIME 
)

每天可能有很多值，也可能一天只有一个值.现在我想获取最接近一天中给定时间的给定时间跨度(例如一个月)中每一天的值.如果当天有记录，我只想每天获取一个值，如果没有记录，则没有值.我的数据库是 PostgreSQL.我对此很坚持.我可以只获取时间跨度内的所有值并以编程方式为每一天选择最接近的值，但这意味着从数据库中提取大量数据，因为一天可能有很多值.

There may be many values on each day, there may also be only one value for a given day. Now I want to get the value for each day in a given timespan (e.g. one month) which is nearest to a given time of day. I only want to get one value per day if there are records for this day or no value if there are no records. My database is PostgreSQL. I'm quite stuck with that. I could just get all values in the timespan and select the nearest value for each day programmatically, but that would mean to pull a huge amount of data from the database, because there can be many values on one day.

(更新)

把它表述得更抽象一点:我有任意精度的数据(可能是一分钟，可能是两小时或两天)，我想将其转换为一天的固定精度，具体时间为一天.

To formulate it a bit more abstract: I have data of arbitrary precision (could be one minute, could be two hours or two days) and I want to convert it to a fixed precision of one day, with a specific time of day.

(第二次更新)

假设所需时间为 16:00，这是来自已接受答案的查询，具有正确的 postgresql 类型转换:

This is the query from the accepted answer with correct postgresql type converstions, assuming the desired time is 16:00:

SELECT datetime, value FROM values, (
  SELECT DATE(datetime) AS date, MIN(ABS(EXTRACT(EPOCH FROM TIME '16:00' - CAST(datetime AS TIME)))) AS timediff
  FROM values
  GROUP BY DATE(datetime)
  ) AS besttimes
WHERE 
CAST(values.datetime AS TIME) BETWEEN TIME '16:00' - CAST(besttimes.timediff::text || ' seconds' AS INTERVAL)
                                AND TIME '16:00' + CAST(besttimes.timediff::text || ' seconds' AS INTERVAL) 
                                AND DATE(values.datetime) = besttimes.date

推荐答案

朝这个方向发展怎么样?

How about going into this direction?

SELECT values.value, values.datetime
FROM values,
( SELECT DATE(datetime) AS date, MIN(ABS(_WANTED_TIME_ - TIME(datetime))) AS timediff
  FROM values
  GROUP BY DATE(datetime)
) AS besttimes
WHERE TIME(values.datetime) BETWEEN _WANTED_TIME_ - besttimes.timediff
                                AND _WANTED_TIME_ + besttimes.timediff
AND DATE(values.datetime) = besttimes.date

我不确定日期/时间提取和 abs(time) 函数，因此您可能不得不更换它们.

I am not sure about the date/time extracting and abs(time) functions, so you will have to replace them probably.

这篇关于SQL:为最接近特定时间的每一天选择一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！