> ## Documentation Index
> Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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](../../../copper/copper_measures#aggregated-measures-with-user-defined-aggregate-functions-udaf) (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:

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
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](./user_defined_aggregation#re-aggregable-udaf) if needed

<Info>
  ###

  You must provide as much aggregation results as needed in your registered Copper terminate function.
</Info>

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
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.

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
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.

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
final ICellSet result =
    IActivePivotQueryRunner.create()
        .withWildcardCoordinate(LevelIdentifier.simple("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](../aggregate_table#custom-aggregations) 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.

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
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();
```