Geek asks and answers

The closest record for a series of dates

I know that to get the closest record to 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 in a few days so that I know the closest record for every day. Any ideas?

A series of days will be generated with generate_sequence().

The closest previous record may be on the previous day for what we need for the value, but still need to be returned.

+5
source share
2 answers

Should be the easiest and fastest with LEFT JOINand 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;

Result (dummy values):

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, , , generate_series() - . ( generate_series() "generate_sequence()"?)

JOIN resulttime. LEFT JOIN JOIN, , resulttime.

DISTINCT ON (x.search_ts) ORDER BY x.search_ts, r.resulttime DESC ( ) resulttime, .

+4

, , ,

SELECT max(resulttime), date_trunc('days',resulttime) FROM results GROUP BY 2;

date_trunc, , GROUP BY max()

+1

More articles:


All Articles