On 22/07/10 16:50, Wes Devauld wrote:
>
> I was searching for a way to keep using last() and keeping the extraction to
> a single step, although the more I fight with it, the less I think that it
> is worth it. If you have any further suggestions, I would appreciate
> hearing them.
You can certainly do it in a single query. I've commented out the
event_date_idx below because it's far from guaranteed it'll be useful to
you.
BEGIN;
DROP TABLE IF EXISTS events;
CREATE TABLE events ( e_id SERIAL, e_ts timestamp(0) without time zone, PRIMARY KEY (e_id)
);
INSERT INTO events (e_ts)
SELECT '2010-01-01 01:01:01'::timestamp without time zone
+ i * '1 minute'::interval
FROM generate_series(0,999999) i;
-- CREATE INDEX event_date_idx ON events ((e_ts::date),e_ts);
CREATE INDEX events_ts_idx ON events (e_ts);
-- EXPLAIN ANALYSE
SELECT e.e_id, e.e_ts, minmax.tgt_day
FROM ( SELECT (e_ts::date) AS tgt_day, min(e_ts) as first_ts, max(e_ts) as last_ts FROM
events GROUP BY 1
) AS minmax
JOIN events e
ON (e.e_ts = minmax.first_ts) OR (e.e_ts = minmax.last_ts)
ORDER BY e_ts
;
COMMIT;
Using the real, windowing versions of first/last in 8.4+ will still
require sorting the whole table (AFAICT) so isn't likely to be much
improvement over a self-join here.
-- Richard Huxton Archonet Ltd