Re: Aggregates (last/first) not behaving

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