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 Thu, 29 Nov 2012 13:29:06 GMT
On 11/29/12 1:23 AM, John English wrote:
> On 28/11/2012 23:31, Rick Hillegas wrote:
>> 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.
>
> Hmm, looks interesting!
>
>> Hope this helps,
>
> Me too, but I need to spend some time digesting your example...
A couple notes so that the examples make more sense:

1) A pivot table is a lot like a grouped aggregate. Rows with a common 
key end up being grouped together into a single composite row.

2) More information on user-defined aggregates can be found in the 
section titled "Programming user-defined aggregates" here: 
http://db.apache.org/derby/docs/dev/devguide/

3) This user-defined aggregate operates on two user-defined types. The 
types probably deserve better names.

4) The first type (NamedInt) represents what will end up being a column 
in the result row. The NamedInt binds a column name to an int value.

5) The second type (HashMap) is just a bag of these columns. It ends up 
being the contents of the pivoted row.

6) Some user-defined functions have to be created in order to create and 
inspect the user-defined types.

7) The NamedIntAggregator is passed a stream of columns, which it puts 
into the result row.

8) The query at the end of the ij script puts it all together: The inner 
query constructs the result rows, but they are represented as HashMaps. 
The outer query unpacks the HashMaps into displayable rows.

Hope this is useful,
-Rick

>
> Many thanks for this,


Mime
View raw message