Re: aggregation problem: first/last/count(*)

> 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(at)ttmail(dot)com]
Sent: Monday, January 26, 2009 4:27 PM
To: Marc Mamin
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: aggregation problem: first/last/count(*)

On Mon, 26 Jan 2009, "Marc Mamin" <M(dot)Mamin(at)intershop(dot)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
FROM foo
GROUP BY grp)
AS bar
INNER JOIN foo
ON foo.grp = bar.grp
AND (foo.ts = bar.min_ts OR 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.