# ColumnStore User Defined Aggregate and Window Functions

## Introduction

Starting with MariaDB ColumnStore 1.1, the ability to create and use user-defined aggregate and window functions is supported in addition to scalar functions. With ColumnStore 1.2, multiple parameters are supported. A C++ SDK is provided, as well as 3 reference examples that provide additional functions that may be of general use:

* **median—mathematical median**, equivalent to percentile\_cont(0.5).
* **avg\_mode—mathematical mode**, i.e., the most frequent value in the set.
* **SSQ—sum of squares**, i.e., the sum of each number squared in the set.

Similar to built-in functions, the SDK supports distributed aggregate execution, where much of the calculation is scaled out across PrimProc[^1] nodes.

## Using User-Defined Aggregate Functions

The reference examples above are included in the standard build of MariaDB ColumnStore. They can be used by registering them as user-defined aggregate functions. The same can be done for new functions, assuming the instance has the updated libraries included.

From a `mcsmysql` prompt:

```sql
CREATE AGGREGATE FUNCTION median RETURNS REAL soname 'libudf_mysql.so';
CREATE AGGREGATE FUNCTION avg_mode RETURNS REAL soname 'libudf_mysql.so';
CREATE AGGREGATE FUNCTION ssq RETURNS REAL soname 'libudf_mysql.so';
```

After this, they may be used in the same way as any other aggregate or window function, like `SUM`:

```sql
SELECT grade, 
AVG(loan_amnt) AVG, 
MEDIAN(loan_amnt) median 
FROM loanstats 
GROUP BY grade 
ORDER BY grade;
```

## Developing a New Function

This requires a MariaDB ColumnStore source tree and the necessary tools to compile C/C++ code. The SDK and reference examples are available in the `utils/udfsdk` directory of the source tree. It contains the SDK documentation, which is also available here:

* [1.2.x UDAF SDK Guide "1.2.x UDAF SDK Guide"](https://github.com/mariadb-corporation/mariadb-columnstore-engine/blob/master/utils/udfsdk/udfsdk.h)

## Limitations

* The implementation of the median and `avg_mode` functions will scale in memory consumption to the size of the set of unique values in the aggregation.

<sub>*This page is: Copyright © 2025 MariaDB. All rights reserved.*</sub>

{% @marketo/form formId="4316" %}

[^1]: PrimProc is the ColumnStore Primitives Processor.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/analytics/mariadb-columnstore/reference/columnstore-user-defined-aggregate-and-window-functions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.