使用者自定義聚集函式--求平均值(avg)--myavg

使用者自定義聚集函式--求平均值(avg)--myavg[@more@]

CREATE ROW TYPE complex(item FLOAT, rowsum int);

CREATE FUNCTION myavg_init (a float)
RETURNING complex;
RETURN row(0,0)::complex;
END FUNCTION;

CREATE FUNCTION myavg_iter (result complex, value float)
RETURNING complex;
RETURN row(result.item+value,result.rowsum+1)::complex;
END FUNCTION;

CREATE FUNCTION myavg_combine(partial1 complex, partial2 complex)
RETURNING complex;
RETURN row(partial1.item+partial2.item,partial1.rowsum+partial2.rowsum)::complex;
END FUNCTION;

CREATE FUNCTION myavg_final(final complex)
RETURNING float;
DEFINE my_final_result float;

IF (final.rowsum=0)
THEN
LET my_final_result = 0;

ELSE
LET my_final_result = final.item/final.rowsum;

END IF ;

RETURN my_final_result;
END FUNCTION;

CREATE AGGREGATE myavg WITH(
INIT = myavg_init,
ITER = myavg_iter,
COMBINE = myavg_combine,
FINAL = myavg_final);

create table test_avg(c1 char(4),c2 int);
insert into test_avg values('a',1);
insert into test_avg values('a',2);
insert into test_avg values('a',3);
insert into test_avg values('a',4);
insert into test_avg values('b',1);
insert into test_avg values('b',2);
insert into test_avg values('b',3);
insert into test_avg values('c',1);
insert into test_avg values('c',2);
insert into test_avg values('d',1);
insert into test_avg values('e',0);
> select c1,myavg(c2)
> from test_avg
> group by c1;
c1 myavg

d 1.000000000000
e 0.00
a 2.500000000000
b 2.000000000000
c 1.500000000000

5 row(s) retrieved.

>