A UDA is a Java class that implements the
org.apache.derby.agg.Aggregator interface.
The org.apache.derby.agg.Aggregator interface extends
java.io.Serializable, so you must make sure that all of the state of your
UDA is serializable. A UDA may be serialized to disk when it performs grouped
aggregation over a large number of groups. That is, intermediate results may be
serialized to disk for a query like the following:
SELECT a, myAggregate( b ) FROM myTable GROUP BY a
The serialization will fail if the UDA contains non-serializable fields.
The following class provides an aggregate that computes the
median value from a list of objects. This is a generic class. Its parameter must
be a linear (Comparable) type.
import java.util.ArrayList;
import java.util.Collections;
import org.apache.derby.agg.Aggregator;
public class Median<V extends Comparable<V>>
implements Aggregator<V,V,Median<V>>
{
private ArrayList<V> _values;
public Median() {}
public void init() { _values = new ArrayList<V>(); }
public void accumulate( V value ) { _values.add( value ); }
public void merge( Median<V> other )
{
_values.addAll( other._values );
}
public V terminate()
{
Collections.sort( _values );
int count = _values.size();
if ( count == 0 ) { return null; }
else { return _values.get( count/2 ); }
}
}
Using this generic class, we can declare UDAs for all of the sortable
data types. For
example:
create derby aggregate intMedian for int external name 'Median';
create derby aggregate varcharMedian for varchar( 32672 ) external name
'Median';
We can then use these UDAs just like built-in
aggregates:
create table intValues( a int, b int );
create table varcharValues( a int, b varchar( 32672 ) );
insert into intValues values ( 1, 1 ), ( 1, 10 ), ( 1, 100 ),
( 1, 1000 ), ( 2, 5 ), ( 2, 50 ), ( 2, 500 ), ( 2, 5000 );
insert into varcharValues values ( 1, 'a' ), ( 1, 'ab' ), ( 1, 'abc' ),
( 2, 'a' ), ( 2, 'aa' ), ( 2, 'aaa' );
select a, intMedian( b ) from intValues group by a;
A |2
-----------------------
1 |100
2 |500
select varcharMedian( b ) from varcharValues;
1
---
aaa
See "CREATE DERBY AGGREGATE statement" in the
for more information.