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