User Defined Aggregation Functions

DirectQuery delegates some aggregation work to the remote database. The core ActivePivot aggregation (such as SUM, AVG, MAX...) are natively converted to the remote database equivalent aggregations.

However, if you write some custom aggregation functions (UDAF) you will also need to define how this aggregation is done in your remote database.

Sum product example

This example is based on a ClickHouse database and SQL but can easily be ported to other databases.

For instance let's consider a simple sum product on scalar values, which in SQL would be SUM(quantity * price).

Here is a simple implementation with Copper:

Copper.userDefinedAgg("QUANTITY", "UNIT_PRICE")
        .aggregationBuffer(Types.TYPE_DOUBLE)
        .pluginKey("MY_SUM_PRODUCT")
        .contribute((reader, agg) -> agg.addDouble(0, reader.readDouble(0) * reader.readDouble(1)))
        .merge((in, out) -> out.addDouble(0, in.readDouble(0)))
        .outputFromBuffer(0)
        .as("My Sum Product")
        .publish(context);

When converting the query to SQL for ClickHouse, DirectQuery will look for an aggregation function with the same plugin key. This aggregation function will convert the fields to SQL code.

We simply need to register the aggregation function, with the plugin key we use in Atoti+, the number of inputs (i.e. the number of SQL columns to read), the list of functions to write the SQL expression from the list of the column names and their output type. The number of functions (and their output type) should be the same as the number of inputs expected by the Copper function you just registered.

Session.registerUdaf(
        "MY_SUM_PRODUCT",
        2,
        List.of(sqlColumnNames -> "SUM(" + sqlColumnNames.get(0) + " * " + sqlColumnNames.get(1) + ")"),
        List.of(Types.TYPE_DOUBLE));

This new Aggregation can now be used in a DirectQuery project and the computation will be pushed to the remote database.

final ICellSet result = ActivePivotQueryRunner.create()
        .withWildcardCoordinate("DATE")
        .forMeasures("My Sum Product")
        .run(pivot);
GetAggregatesResultCellset.check(pivot, result).getHumanRepresentationOfCellSet();

DATE	My Sum Product
2022-02-03	580.0
2022-01-31	798.0
2022-02-04	258.0
2022-02-01	1030.0
2022-02-02	604.0

Sum product example​

Sum product example