Re: multi-column aggregates

I may not fully understand your situation but

SELECT distinct grouping,
(SELECT integer_column FROM test.test_agg_last WHERE
grouping=t1.grouping ORDER BY cur_date DESC LIMIT 1) AS last_int,
(SELECT integer_column FROM test.test_agg_last WHERE
grouping=t1.grouping ORDER BY cur_date ASC LIMIT 1) AS first_int,
(SELECT integer_column FROM test.test_agg_last WHERE
grouping=t1.grouping ORDER BY cur_date2 DESC LIMIT 1) AS last_int2,
(SELECT integer_column FROM test.test_agg_last WHERE
grouping=t1.grouping ORDER BY cur_date2 ASC LIMIT 1) AS first_int2
FROM test.test_agg_last t1

produced the same result as at least your first example:

1,8,78,78,8
2,32,32,32,32

I think it does what you are trying to do and can be expanded to your
other examples. Maybe not as cool as using composite aggregates, though.

-- BMT

Chris Kratz wrote:

>Thanks Tom,
>
>Well for anyone else who may be interested in doing something similar, here is
>what we did. It does require typecasting going into the functions, composite
>types and using the dot notation to get the value back out of the composite
>object returned. But it works.
>
>This is what we wanted...
>
>select last(cur_date, some_column) from some_table....
>
>We got this close...
>
>select (last((cur_date, some_column)::last_int_agg)).value as last_int from...
>
>which I think will be useable for what we need. If anyone has ideas to
>simplify this, I would appreciate it. Example and generation script attached
>at end.
>
>I do have to say that the flexibility in postgres for creating our own data
>types and aggregate functions is wonderfull. Kudos again to everyone who has
>but so much time and energy into postgres.
>---
>
>-Chris
>
>On Thursday 09 March 2006 01:08 pm, Tom Lane wrote:
>
>
>>Chris Kratz <chris(dot)kratz(at)vistashare(dot)com> writes:
>>
>>
>>>Is there any way in postgres to have an aggregate that uses input from
>>>two columns without using composite types?
>>>
>>>
>>No.
>>
>> regards, tom lane
>>
>>
>
>
>-------------------------------------------------------------------------------------
>simple example test data::
>-------------------------------------------------------------------------------------
>test=# select id, grouping, cur_date::date, cur_date2::date, integer_column
>from test_agg_last;
> id | grouping | cur_date | cur_date2 | integer_column
>----+----------+------------+------------+----------------
> 1 | 1 | 2006-01-05 | 2006-01-03 | 8
> 2 | 1 | 2006-01-01 | 2006-01-05 | 78
> 3 | 2 | 2006-01-03 | 2006-01-01 | 32
>(3 rows)
>
>test=# select
>test-# grouping,
>test-# (last((cur_date, integer_column)::last_int_agg)).value as last_int,
>test-# (first((cur_date, integer_column)::last_int_agg)).value as
>first_int,
>test-# (last((cur_date2, integer_column)::last_int_agg)).value as
>last_int2,
>test-# (first((cur_date2, integer_column)::last_int_agg)).value as
>first_int2
>test-# from test_agg_last
>test-# group by grouping
>test-# order by grouping
>test-# ;
> grouping | last_int | first_int | last_int2 | first_int2
>----------+----------+-----------+-----------+------------
> 1 | 8 | 78 | 78 | 8
> 2 | 32 | 32 | 32 | 32
>(2 rows)
>
>-------------------------------------------------------------------------------------
>First and Last aggregates using an arbitrary date column
>-------------------------------------------------------------------------------------
>-- aggregate types
>create type last_int_agg as (cur_date timestamp, value int);
>create type last_txt_agg as (cur_date timestamp, value text);
>create type last_rel_agg as (cur_date timestamp, value double precision);
>create type last_num_agg as (cur_date timestamp, value numeric(12,2));
>create type last_dte_agg as (cur_date timestamp, value date);
>create type last_tme_agg as (cur_date timestamp, value time);
>create type last_bln_agg as (cur_date timestamp, value boolean);
>create type last_ntv_agg as (cur_date timestamp, value interval);
>
>-- generic last accumulator function
>CREATE OR REPLACE function last_accum(anyelement, anyelement) returns
>anyelement
> AS $$
> BEGIN
> IF $1.cur_date IS NULL and $2.cur_date IS NOT NULL
> THEN RETURN $2;
> ELSEIF $1.cur_date IS NOT NULL and $2.cur_date IS NULL
> THEN RETURN $1;
> ELSEIF $2.cur_date>$1.cur_date
> THEN RETURN $2;
> ELSE RETURN $1;
> END IF;
> END;
>$$ LANGUAGE plpgsql;
>
>-- generic first accumulator function
>CREATE OR REPLACE function first_accum(anyelement, anyelement) returns
>anyelement
> AS $$
> BEGIN
> IF $1.cur_date IS NULL and $2.cur_date IS NOT NULL
> THEN RETURN $2;
> ELSEIF $1.cur_date IS NOT NULL and $2.cur_date IS NULL
> THEN RETURN $1;
> ELSEIF $2.cur_date<$1.cur_date
> THEN RETURN $2;
> ELSE RETURN $1;
> END IF;
> END;
>$$ LANGUAGE plpgsql;
>
>-- last aggregate
>CREATE AGGREGATE last (
> sfunc = last_accum,
> basetype = anyelement,
> stype = anyelement
>);
>
>-- first aggregate
>CREATE AGGREGATE first (
> sfunc = first_accum,
> basetype = anyelement,
> stype = anyelement
>);
>
>-- test data
>
>create table test_agg_last(
> id serial primary key,
> grouping integer,
> cur_date timestamp,
> cur_date2 timestamp,
> integer_column integer,
> real_column double precision,
> currency_column numeric(12,2),
> text_column text,
> date_column date,
> time_column time without time zone,
> interval_column interval,
> boolean_column boolean);
>
>insert into test_agg_last(grouping, cur_date, cur_date2, integer_column,
>real_column, currency_column, text_column, date_column, time_column,
>interval_column, boolean_column)
>values(1, '1/5/06', '1/3/06', 8, 38.7, '12.00', 'Four score', '12/3/78',
>'3:32pm', '1 day', true);
>
>insert into test_agg_last(grouping, cur_date, cur_date2, integer_column,
>real_column, currency_column, text_column, date_column, time_column,
>interval_column, boolean_column)
>values(1, '1/1/06', '1/5/06', 78, 1.998, '35000.00', 'and seven', '6/18/05',
>'12:00am', '4 hours', false);
>
>innsert into test_agg_last(grouping, cur_date, cur_date2, integer_column,
>real_column, currency_column, text_column, date_column, time_column,
>interval_column, boolean_column)
>values(2, '1/3/06', '1/1/06', 32, 0.000001, '100000.00', 'years ago',
>'1/25/2010', '11:37am', '23 minutes', true);
>
>-- test using multiple date columns with first and last
>select
> grouping,
> (last((cur_date, integer_column)::last_int_agg)).value as last_int,
> (first((cur_date, integer_column)::last_int_agg)).value as first_int,
> (last((cur_date2, integer_column)::last_int_agg)).value as last_int2,
> (first((cur_date2, integer_column)::last_int_agg)).value as first_int2
>from test_agg_last
>group by grouping
>order by grouping
>;
>
>-- test several different common types
>select
> grouping,
> (last((cur_date, integer_column)::last_int_agg)).value as last_int,
> (first((cur_date, integer_column)::last_int_agg)).value as first_int,
> (last((cur_date, real_column)::last_rel_agg)).value as last_real,
> (first((cur_date, real_column)::last_rel_agg)).value as first_real,
> (last((cur_date, currency_column)::last_num_agg)).value as last_currency,
> (first((cur_date, currency_column)::last_num_agg)).value as first_currency,
> (last((cur_date, text_column)::last_txt_agg)).value as last_text,
> (first((cur_date, text_column)::last_txt_agg)).value as first_text,
> (last((cur_date, date_column)::last_dte_agg)).value as last_date,
> (first((cur_date, date_column)::last_dte_agg)).value as first_date,
> (last((cur_date, time_column)::last_tme_agg)).value as last_time,
> (first((cur_date, time_column)::last_tme_agg)).value as first_time,
> (last((cur_date, interval_column)::last_ntv_agg)).value as last_interval,
> (first((cur_date, interval_column)::last_ntv_agg)).value as first_interval,
> (last((cur_date, boolean_column)::last_bln_agg)).value as last_boolean,
> (first((cur_date, boolean_column)::last_bln_agg)).value as first_boolean
>
>from test_agg_last
>group by grouping
>order by grouping
>;
>
>-- cleanup test data
>
>drop table test_agg_last;
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: don't forget to increase your free space map settings
>
>
>

--
Regards,
Berend Tober
Seaworthy Systems, Inc.
860-767-9061