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.