Aggregate Definitions

Syntax

aggregate_func_def:
    CREATE [OR REPLACE] AGGREGATE [FUNCTION] qname '(' [ paramlist ] ')' RETURNS func_data_type EXTERNAL NAME external_function_name
  | CREATE [OR REPLACE] AGGREGATE [FUNCTION] qname '(' [ paramlist ] ')' RETURNS func_data_type LANGUAGE language_keyword '{' function_impl_code '}'

func_data_type:
      TABLE '(' column data_type ',' ... ')'
    | data_type

language_keyword:
    C | CPP | PYTHON | PYTHON3 | R

Introduce a new user-defined aggregation function for which the implementation is specified externally using. The function can be written in any of the supported programming languages, or directly bound to an existing MAL operation.

To allow other users to invoke a user-defined aggregate function, you must grant the other users (or PUBLIC) EXECUTE privilege for the aggregate function.

A number of built-in aggregate functions is available, see: Aggregate Functions

Associated system table is sys.functions where type = 3.

Examples

CREATE AGGREGATE my_quantile(val bigint, q double) RETURNS bigint EXTERNAL NAME "aggr"."quantile";

DROP AGGREGATE my_quantile;

CREATE AGGREGATE python_aggregate(val INTEGER) 
RETURNS INTEGER 
LANGUAGE PYTHON {
    try:
        unique = numpy.unique(aggr_group)
        x = numpy.zeros(shape=(unique.size))
        for i in range(0, unique.size):
            x[i] = numpy.sum(val[aggr_group==unique[i]])
    except NameError:
        # aggr_group doesn't exist. no groups, aggregate on all data
        x = numpy.sum(val)
    return(x)
};

DROP AGGREGATE python_aggregate;

For languages C and CPP, a C/C++ compiler must be available on the deployment server and the MonetDB server started with option: --set embedded_c=true.

For language PYTHON, the Python3 script interpreter software must be available on the deployment server and the MonetDB server started with option: --set embedded_py=true or --set embedded_py=3.

For language R, the R script interpreter software must be available on the deployment server and the MonetDB server started with option: --set embedded_r=true.

For details see: JIT C/C++ UDFs and MonetDB Embedded. See also: COMMENT ON AGGREGATE.

Associated system table is sys.functions where type = 3 and language > 2.