User Defined Aggregation Functions

DirectQuery delegates some aggregation work to the remote database. The core Atoti Server 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);

For this UDAF to be usable with DirectQuery, you must provide a SQL translation of it.
To build this SQL UDAF you need :

• the Atoti UDAF key
• the number of inputs of the SQL expressions (i.e. the number of SQL columns to read)
• the SQL expression(s) resolving the aggregation function results(s)
• the aggregation function(s) results types
• the re-aggregation function if neeeded

info

You must provide as much aggregation results as needed in your registered Copper terminate function.

final UserDefinedSqlAggregationFunction udaf =
    UserDefinedSqlAggregationFunction.builder()
        .pluginKey("MY_SUM_PRODUCT")
        .inputsNumber(2)
        .sqlExpressionProviders(
            List.of(
                sqlColumnNames ->
                    "SUM(" + sqlColumnNames.get(0) + " * " + sqlColumnNames.get(1) + ")"))
        .outputTypes(List.of(StandardTypes.DOUBLE))
        .reaggregable(IAggregationFunction.SUM_FUNCTION_PLUGIN_KEY)
        .build();

Finally, you can register the SQL UDAF in the DirectQuery application.

final Application application =
    Application.builder(connector)
        .schema(schema)
        .managerDescription(managerDescription)
        .userDefinedSqlAggregationFunctions(List.of(udaf))
        .build();

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

final ICellSet result =
    IActivePivotQueryRunner.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

Re-aggregable UDAF

For some features like aggregate table with user defined function a reaggregable UDAF is a plus.
An aggregation function is re-aggregable if a function, capable of merging two aggregation results into one, exists. For instance, the sum product aggregation can be re-aggregated by the SUM aggregation function in the example above.

final UserDefinedSqlAggregationFunction udaf =
    UserDefinedSqlAggregationFunction.builder()
        .pluginKey("MY_SUM_PRODUCT")
        .inputsNumber(2)
        .sqlExpressionProviders(
            List.of(
                sqlColumnNames ->
                    "SUM(" + sqlColumnNames.get(0) + " * " + sqlColumnNames.get(1) + ")"))
        .outputTypes(List.of(StandardTypes.DOUBLE))
        .reaggregable(IAggregationFunction.SUM_FUNCTION_PLUGIN_KEY)
        .build();