db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: AW: Pivoting tables?
Date Wed, 28 Nov 2012 21:31:23 GMT
Hi John,

You may be able to press user-defined aggregates into service here. They 
are a new feature available in the development trunk and slated for 
release 10.10 next year. To demonstrate how to do this, I'm enclosing an 
ij script plus two Java classes which support the script.

Hope this helps,
-Rick

---------------

First the script:

connect 'jdbc:derby:memory:db;create=true';

create table products( userName varchar( 10 ), product varchar( 10 ), 
quantity int );
insert into products values ( 'me', 'abc', 3 ), ( 'you', 'abc', 5 ), ( 
'you', 'def', 7 );
select * from products;

create type HashMap external name 'java.util.HashMap' language java;
create type NamedInt external name 'NamedInt' language java;

create function nameInt( n varchar( 10 ), v int ) returns NamedInt
language java parameter style java no sql
external name 'NamedInt.nameInt';

create function getInt( n varchar( 10 ), h HashMap ) returns int
language java parameter style java no sql
external name 'NamedInt.getInt';

create derby aggregate nia for NamedInt returns HashMap external name 
'NamedIntAggregator';

-- pivot the table
select userName,
     getInt( 'abc', nia ) "abc",
     getInt( 'def', nia ) "def"
from
(
     select userName, nia( nameInt( product, quantity ) ) nia
     from products
     group by userName
) s
;

Now the two classes:

public class NamedInt   implements java.io.Serializable
{
     public  final   String  name;
     public  final   int     value;
     private NamedInt( String n, int v ) { name = n; value = v; }
     public  static  NamedInt    nameInt( String n, int v ) { return new 
NamedInt( n, v ); }
     public  static  Integer getInt( String n, 
java.util.HashMap<String,Integer> h ) { return  h.get( n ); }
}

... and

import java.util.HashMap;
public  class   NamedIntAggregator    implements  
org.apache.derby.agg.Aggregator<NamedInt,HashMap,NamedIntAggregator>
{
     private HashMap<String,Integer>  _accumulator;
     public  NamedIntAggregator() {}
     public  void    init() {  _accumulator = new 
HashMap<String,Integer>(); }
     public  void    accumulate( NamedInt ni ) { _accumulator.put( 
ni.name, ni.value ); }
     public  void    merge( NamedIntAggregator otherAggregator ) { 
_accumulator.putAll( otherAggregator._accumulator ); }
     public  HashMap terminate() { return _accumulator; }
}




Mime
View raw message