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.