一系列日期的最近记录 [英] Closest record for a series of dates

问题描述

我知道要获取日期之前的最近记录，我可以使用查询：

I know that to get the closest record prior to a date I can use the query:

select * 
from results 
where resulttime = (select max(resulttime) 
                    from results 
                    where some_id = 15 
                      and resulttime < '2012-07-27');

但是我需要连续几天这样做，以便我知道每个记录的最近记录天。有什么想法吗？

But I need to do this for a series of days, so that I know the closest record for each day. Any ideas?

一系列的日子将由 generate_sequence（）生成。

The series of days would be generated by generate_sequence().

最接近的先前记录可能在我们想要其值的前一天，但仍需要返回。

The closest prior record may be in a prior day to what we want the value for, but still need to be returned.

推荐答案

应该最简单& 左联接和 DISTINCT ON 最快：

Should be simplest & fastest with a LEFT JOIN and DISTINCT ON:

WITH x(search_ts) AS (
    VALUES
     ('2012-07-26 20:31:29'::timestamp)              -- search timestamps
    ,('2012-05-14 19:38:21')
    ,('2012-05-13 22:24:10')
    )
SELECT DISTINCT ON (x.search_ts)
       x.search_ts, r.id, r.resulttime
FROM   x
LEFT   JOIN results r ON r.resulttime <= x.search_ts -- smaller or same
-- WHERE some_id = 15                                -- some condition?
ORDER  BY x.search_ts, r.resulttime DESC;

结果（虚拟值）：

search_ts           | id     | resulttime
--------------------+--------+----------------
2012-05-13 22:24:10 | 404643 | 2012-05-13 22:24:10
2012-05-14 19:38:21 | 404643 | 2012-05-13 22:24:10
2012-07-26 20:31:29 | 219822 | 2012-07-25 19:47:44

我使用 CTE 来提供值，可以是表或函数或非嵌套数组或由< a href = http://www.postgresql.org/docs/current/interactive/functions-srf.html#FUNCTIONS-SRF-SERIES rel = nofollow> generate_series（） 以及其他内容。 （您是说 generate_series（）的意思是 generate_sequence（）吗？）

I use a CTE to provide the values, could be a table or function or unnested array or a set generated with generate_series() something else as well. (Did you mean generate_series() by "generate_sequence()"?)

第一个我 JOIN 搜索时间戳到表中所有具有或更早 resulttime 的行。我使用 LEFT JOIN 代替 JOIN ，以便在没有先前的结果时间完全在表中。

First I JOIN the search timestamps to all rows in the table with earlier or equal resulttime. I use LEFT JOIN instead of JOIN so that search timestamps are not dropped when there is no prior resulttime in the table at all.

在 DISTINCT ON（x.search_ts）与 ORDER BY x.search_ts，r.resulttime DESC 结合，我们得到最大的（或同等最大的一个） resulttime 小于或等于每个搜索时间戳。

With DISTINCT ON (x.search_ts) in combination with ORDER BY x.search_ts, r.resulttime DESC we get the greatest (or one of the equally greatest) resulttime that is smaller or equal to each search timestamp.

这篇关于一系列日期的最近记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！