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.
>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22565551/viewspace-1031288/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22565551/viewspace-1031288/