Обсуждение: Aggregates (last/first) not behaving

On 22/07/10 07:37, Wes Devauld wrote:
> I have PostgreSQL 8.3.9 [PostgreSQL 8.3.9 on i386-apple-darwin10.3.0,
> compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc.
> build 5646) (dot 1)]
>
> and the custom first and last aggregates from:
> http://wiki.postgresql.org/wiki/First_(aggregate)
> http://wiki.postgresql.org/wiki/Last_(aggregate)
>
> I have a simple table, of two columns.  The first is a timestamp and is
> the primary key, the second is an integer.  I've loaded the table up
> with values, one for every minute, for a whole year.  Some SQL to
> recreate the table and the aggregates can be retrieved from:
>
> http://blog.devauld.ca/aggregate_test.zip (File is approximately 180KB)
>
> Now when I try to make use of the first and last aggregates, I get:
>
> # select first(t), last(t) from test group by extract(day from t);
>          first        |        last
> ---------------------+---------------------
>   2009-01-01 00:00:00 | 2009-01-01 17:02:00
>   2009-01-02 10:07:00 | 2009-01-02 10:06:00
>   2009-01-03 20:15:00 | 2009-01-03 20:14:00
[snip]
> For some reason the aggregates are not falling into the proper group.  I
> can't blame timezones as the results are all over the map, and
> first/last relationship is broken as in some cases 'last' is
> chronologically before 'first'

They all seem grouped properly (by day) to me. Unless I've missed something.

The first/last aggregates aren't ordered in any way. They are "first 
value I happened to find" and "last value I happened to find".

If you want the earliest/latest timestamp from each day, use min() and 
max().

--   Richard Huxton  Archonet Ltd

Wes Devauld <wes@devauld.ca> wrote:

> I believe I lost the flavour of what I'm doing when I constructed this
> example.  I'm not interested in the timepoint as much as the value that is
> attached to it.  I need to be able to find the last chronological record for
> a given day.

> I can get the value for which I am looking in two steps:

> select max(t) as t into table last_of_day from test group by extract(day
> from t);
> select last_of_day.t, test.v from last_of_day, test where last_of_day.t =
> test.t;

> I was fairly happy when first() and last() were discovered, as these two
> steps could be merged, until the table grows too large and the query planner
> decides to sort the results before they are aggregated.

> 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.
> [...]

Perhaps you could /concisely/ rephrase your problem. Finding
the first/last value per group with/without window functions
is a common problem, and there are lots of solutions to it.
But few people will wade through lots of text to find out
what's bothering you.
 For example, you can query the "last" values per day along
the lines of (untested):

| SELECT EXTRACT(day FROM t), v
|   FROM test
|   WHERE t IN (SELECT MAX(t) FROM test
|                 GROUP BY EXTRACT(day FROM t));

Obviously, this doesn't "keep using last()", so I don't know
whether it's good or bad for you.

Tim

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