You are viewing an older version of this section. View current production version.
CREATE AGGREGATE
Creates a user-defined aggregate function (UDAF). A UDAF is a callable routine that accepts input parameters, executes programmatic logic in the function body, and returns a scalar-type value.
Syntax
[USING database_name] CREATE [OR REPLACE] AGGREGATE function_name ( [parameter_list] )
RETURNS { data_type [data_type_modifier] }
WITH STATE data_type
INITIALIZE WITH udf_function_name
ITERATE WITH udf_function_name
MERGE WITH udf_function_name
TERMINATE WITH udf_function_name
parameter_list:
data_type [data_type_modifier [, ...] ] [, ...]
data_type_modifier:
DEFAULT default_value | NOT NULL | NULL | COLLATE collation_name
Arguments
OR REPLACE
If specified, replaces a UDAF if one already exists with the same name.
function_name
The name of the function. By their nature, UDAF names may override existing builtin aggregate function names for scalar data types, such as SUM()
. UDAF names cannot be use the same name as stored procedures, tables, views, user-defined scalar-value functions (UDFs), or user-defined table-valued functions (TVFs).
You can also specify database_name
and function_name
together by replacing function_name
with database_name.function_name
instead of specifying the database in USING database_name
. For example, you can write the following:
CREATE AGGREGATE db.some_func(a int)
...
Function names are case-insensitive. This is different than the behavior for table names.
WITH STATE
The STATE
type of a UDAF may be a scalar type, ARRAY
type, or a RECORD
type.
parameter_list
Input parameters are optional. Any number of input parameters can be specified, and each must be delimited by a comma (,
). Each input parameter is specified only by its data type and optional modifier.
UDAFs only allow scalar data types as input parameters. See the Data Types topic for more information.
The following example shows how to declare a single input parameter:
CREATE AGGREGATE my_sum(BIGINT)
...
The following example demonstrates how to declare more than one input parameters that also specify a data type modifier:
CREATE AGGREGATE multi_param_example(a INT, b VARCHAR(255) NOT NULL COLLATE utf8_bin, c DECIMAL(19,4))
...
udf_function_name
The name of each UDF function to execute for the INITIALIZE WITH
, ITERATE WITH
, MERGE WITH
, and TERMINATE WITH
clauses. The INITIALIZE
function takes in no arguments, and it returns a STATE
data type. The ITERATE
function takes in a STATE
data type and the input parameter data type, and it returns a STATE
data type. If the UDAF has n
parameters, the ITERATE
function will take in n+1
arguments, with the first argument being the STATE
type. The MERGE
function takes in two STATE
data types, and it returns a STATE
data type. The TERMINATE
function takes in a STATE
data type, and it returns the type specified in the RETURNS
clause.
Security and Permissions
The invoker of a UDAF must have EXECUTE
permissions on the UDAF. Also, the UDAF’s definer must have EXECUTE
permissions on any UDFs in the function’s body.
Example
The following example creates a new UDAF named avg_udaf
, which uses a RECORD
state type and has the same behavior as the builtin AVG
function. Note that before we can create the UDAF, each of the prerequisite user-defined scalar functions (UDFs) must be created, as the UDAF definition depends on their existence.
Create UDF Dependencies
The avg_udaf
example UDAF depends on the following UDFs:
DELIMITER //
CREATE FUNCTION avg_init() RETURNS RECORD(s BIGINT, c BIGINT) AS
BEGIN
RETURN ROW(0, 0);
END //
DELIMITER ;
DELIMITER //
CREATE FUNCTION avg_iter(state RECORD(s BIGINT, c BIGINT), value BIGINT) RETURNS RECORD(s BIGINT, c BIGINT) AS
BEGIN
RETURN ROW(state.s + value, state.c + 1);
END //
DELIMITER ;
DELIMITER //
CREATE FUNCTION avg_merge(state1 RECORD(s BIGINT, c BIGINT), state2 RECORD(s BIGINT, c BIGINT)) RETURNS RECORD(s BIGINT, c BIGINT) AS
BEGIN
RETURN row(state1.s + state2.s, state1.c + state2.c);
END //
DELIMITER ;
DELIMITER //
CREATE FUNCTION avg_terminate(state RECORD(s BIGINT, c BIGINT)) RETURNS BIGINT AS
BEGIN
RETURN state.s / state.c;
END //
DELIMITER ;
Create UDAF Example
Once the UDF dependencies have been created, you can create the UDAF. Execute the following statement:
CREATE AGGREGATE avg_udaf(BIGINT) RETURNS BIGINT
WITH STATE RECORD(s BIGINT, c BIGINT)
INITIALIZE WITH avg_init
ITERATE WITH avg_iter
MERGE WITH avg_merge
TERMINATE WITH avg_terminate;
After the UDAF has been successfully created, execute the following commands to try it:
memsql> CREATE TABLE t (i BIGINT);
Query OK, 0 rows affected
memsql> INSERT INTO t VALUES (1), (2), (3), (4), (5);
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
memsql> SELECT avg_udaf(i) FROM t;
+-------------+
| avg_udaf(i) |
+-------------+
| 3 |
+-------------+
1 row in set