Re: aggregation problem: first/last/count(*) - Mailing list pgsql-sql

> I think the problem in here is that you want to collect the first and
last values in the same row


Your idea is ok, but it just postpone the problem. And I need the result
within the DB for further calculations /aggregations.

What I need is really something like:

test=# SELECT foo.ts, foo.grp, foo.val,foo2.val        FROM (SELECT grp, MAX(ts) AS max_ts, MIN(ts) AS min_ts
    FROM foo            GROUP BY grp)          AS bar  INNER JOIN foo          ON foo.grp = bar.grp         AND foo.ts
=bar.min_ts  INNER JOIN foo2          ON foo2.grp = bar.grp         AND foo2.ts = bar.max_ts  

I've tested different solutions and the DISTINCT ON clause was better.
(I guess the best solution depend of the distribution of grp and val).


I've also just found aggregate functions for first/last:
http://www.postgresonline.com/journal/index.php?/archives/68-More-Aggreg
ate-Fun-Whos-on-First-and-Whos-on-Last.html


But its is slightly slower as my solution.

I'll still make a test with more data As I guess that swapping will grow
fatser mith my query than with the first/last aggregate functions.


cheers,

Marc Mamin









-----Original Message-----
From: Volkan YAZICI [mailto:yazicivo@ttmail.com]
Sent: Monday, January 26, 2009 4:27 PM
To: Marc Mamin
Cc: pgsql-sql@postgresql.org
Subject: Re: aggregation problem: first/last/count(*)

On Mon, 26 Jan 2009, "Marc Mamin" <M.Mamin@intershop.de> writes:
> create table test
> (
> time         int8, --store the time as epoch
> a_group      varchar,
> category     varchar
> )
>
> ...
>
> SELECT
> FIRST.a_group,
> FIRST.time     as first_time,
> FIRST.category as first_category,
> LAST.time      as last_time,
> LAST.category  as last_category,
> AGG.c_count,
> AGG.c_all
> FROM
> ...

I think the problem in here is that you want to collect the first and
last values in the same row. Instead, splitting them into two sequential
rows would suit better to your database schema design, and you can
rebuild the data structure as you want in the application tier later.
For instance, consider below example:

test=# SELECT ts, grp, val FROM foo;ts | grp | val
----+-----+----- 1 |   1 |   1 2 |   1 |   2 3 |   1 |   3 4 |   2 |   1 4 |   2 |   2 5 |   3 |   1
(6 rows)

test=# SELECT foo.ts, foo.grp, foo.val        FROM (SELECT grp, MAX(ts) AS max_ts, MIN(ts) AS min_ts
FROMfoo            GROUP BY grp)          AS bar  INNER JOIN foo          ON foo.grp = bar.grp         AND (foo.ts =
bar.min_tsOR foo.ts = bar.max_ts);  ts | grp | 
val
----+-----+----- 1 |   1 |   1 3 |   1 |   3 4 |   2 |   1 4 |   2 |   2 5 |   3 |   1
(5 rows)

After receiving above output, you can traverse returned rows one by one
in the application layer and output desired results.


Regards.