Re: multi-column aggregates - Mailing list pgsql-general

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.kratz@vistashare.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;