From: Richard Huxton <dev@archonet.com>
To: Wes Devauld <wes@devauld.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Aggregates (last/first) not behaving
Date: Thu, 22 Jul 2010 20:24:46 +0100
Message-ID: <4C489AFE.2060601@archonet.com> (raw)
In-Reply-To: <AANLkTinkjZLrYsDNnKpvShhu-U5-OAStISy1XPSUqegx@mail.gmail.com>
References: <AANLkTim1g8cejgRokrpTr6a_z6bXeKxOhUSFeTIwMrcK@mail.gmail.com>
<4C480508.8080306@archonet.com>
<AANLkTinkjZLrYsDNnKpvShhu-U5-OAStISy1XPSUqegx@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, wes@devauld.ca
Subject: Re: Aggregates (last/first) not behaving
In-Reply-To: <4C489AFE.2060601@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox