CREATE AGGREGATE

Defines a user-defined aggregate.

Defines a user-defined aggregate. An aggregate executes a user-define function (UDF) on each row in a selected data set, optionally runs a final UDF on the result set and returns a single value, for example average or standard deviation.

Synopsis

CREATE [ OR REPLACE ] AGGREGATE [ IF NOT EXISTS ] [keyspace_name.]aggregate_name (cql_type)
  SFUNC udf_name 
  STYPE cql_type
  FINALFUNC udf_name
  INITCOND init_value
  [ DETERMINISTIC ] ;
Table 1. Legend
Syntax conventions Description
UPPERCASE Literal keyword.
Lowercase Not literal.
Italics Variable value. Replace with a user-defined value.
[] Optional. Square brackets ( [] ) surround optional command arguments. Do not type the square brackets.
( ) Group. Parentheses ( ( ) ) identify a group to choose from. Do not type the parentheses.
| Or. A vertical bar ( | ) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
... Repeatable. An ellipsis ( ... ) indicates that you can repeat the syntax element as often as required.
'Literal string' Single quotation ( ' ) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
{ key : value } Map collection. Braces ( { } ) enclose map collections or key value pairs. A colon separates the key and the value.
<datatype1,datatype2> Set, list, map, or tuple. Angle brackets ( < > ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
cql_statement; End CQL statement. A semicolon ( ; ) terminates all CQL statements.
[--] Separate the command line options from the command arguments with two hyphens ( -- ). This syntax is useful when arguments might be mistaken for command line options.
' <schema> ... </schema> ' Search CQL only: Single quotation marks ( ' ) surround an entire XML schema declaration.
@xml_entity='xml_entity_type' Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.
OR REPLACE
Overwrites existing aggregate (with the same name). When OR REPLACE is not specified the operations fails if an aggregate with the same name already exists.
IF NOT EXISTS
Creates an aggregate if it does not already exist, and displays no error if it does exist.
Note: IF NOT EXISTS and OR REPLACE are not supported in the same statement.
cql_type
Specify the CQL type input.
Restriction: Frozen collections are not supported.
SFUNC udf_name
Specify a user-defined function. Calls the state function (SFUNC) for each row. The first parameter declared in the user-defined function is the state parameter; the function's return value is assigned to the state parameter, which is passed to the next call. Pass multiple values using collection types, such as tuples.
STYPE cql_type
CQL type of the parameter returned by the state function.
FINALFUNC udf_name
User-defined function executed on the final values in the state parameter.
INITCOND init_value
Define the initial condition, values, of the first parameter in the SFUNC. Set to null when no value defined.
DETERMINISTIC
Always returns the same output for a certain input. Requires an initial condition and returns a single value.
Default: false (non-deterministic).

Examples

Create an aggregate that calculates average in the cycling keyspace.
  1. Set up a test table with data:
    CREATE TABLE cycling.team_average (
      team_name text, 
      cyclist_name text, 
      cyclist_time_sec int, 
      race_title text, 
      PRIMARY KEY (team_name, race_title,cyclist_name)
    );
    
    INSERT INTO cycling.team_average (
      team_name, cyclist_name, cyclist_time_sec, race_title
    ) VALUES (
      'UnitedHealthCare Pro Cycling Womens Team','Katie HALL',11449,
      'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe'
    );
    
    INSERT INTO cycling.team_average (
      team_name, cyclist_name, cyclist_time_sec, race_title
    ) VALUES (
      'UnitedHealthCare Pro Cycling Womens Team','Linda VILLUMSEN',11485,
      'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe'
    );
    
    INSERT INTO cycling.team_average (
      team_name, cyclist_name, cyclist_time_sec, race_title
    ) VALUES (
      'UnitedHealthCare Pro Cycling Womens Team','Hannah BARNES',11490,
      'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe'
    );
    
    INSERT INTO cycling.team_average (
      team_name, cyclist_name, cyclist_time_sec, race_title
    ) VALUES (
      'Velocio-SRAM','Alena AMIALIUSIK',11451,
      'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe'
    );
    
    INSERT INTO cycling.team_average (
      team_name, cyclist_name, cyclist_time_sec, race_title
    ) VALUES (
      'Velocio-SRAM','Trixi WORRACK',11453,
      'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe'
    );
    
    INSERT INTO cycling.team_average (
      team_name, cyclist_name, cyclist_time_sec, race_title
    ) VALUES (
      'TWENTY16 presented by Sho-Air','Lauren KOMANSKI',11451,
      'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe'
    );
  2. Create a function with a state parameter as a tuple that counts the rows (by incrementing 1 for each record) in the first position and finds the total by adding the current row value to the existing subtotal the second position, and returns the updated state.
    CREATE OR REPLACE FUNCTION cycling.avgState (
      state tuple<int,bigint>, val int
    ) 
      CALLED ON NULL INPUT 
      RETURNS tuple<int,bigint> 
      LANGUAGE java AS 
        $$
          if (val != null) { 
            state.setInt(0, state.getInt(0)+1); 
            state.setLong(1, state.getLong(1)+val.intValue()); 
          }
          return state;
        $$
    ;
    Note: Use a simple test to verify that your function works properly.
    CREATE TABLE cycling.test_avg (
      id int PRIMARY KEY,
      state frozen<tuple<int, bigint>>,
      val int
    );
    
    INSERT INTO cycling.test_avg (
      id, state, val
    ) VALUES (
      1, (6, 9949), 51
    );
    
    INSERT INTO cycling.test_avg (
      id, state, val
    ) VALUES (
      2, (79, 10000), 9999
    );
    SELECT state, avgState(state, val), val
    FROM cycling.test_avg;

    The first value was incremented by one and the second value is the results of the initial state value and val.

     state       | cycling.avgstate(state, val) | val
    -------------+------------------------------+------
       (6, 9949) |                   (7, 10000) |   51
     (79, 10000) |                  (80, 19999) | 9999
  3. Create a function that divides the total value for the selected column by the number of records.
    CREATE OR REPLACE FUNCTION cycling.avgFinal (
      state tuple<int, bigint>
    ) 
      CALLED ON NULL INPUT 
      RETURNS double 
      LANGUAGE java AS 
        $$
          double r = 0; 
          if (state.getInt(0) == 0) return null; 
          r = state.getLong(1); 
          r/= state.getInt(0); 
          return Double.valueOf(r);
        $$ 
    ;
  4. Create the user-defined aggregate to calculate the average value in the column:
    CREATE OR REPLACE AGGREGATE cycling.average (
      int
    ) 
      SFUNC avgState 
      STYPE tuple<int,bigint> 
      FINALFUNC avgFinal 
      INITCOND (0, 0)
    ;
  5. Test the function using a select statement:
    SELECT cycling.average(cyclist_time_sec)
    FROM cycling.team_average 
    WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team' 
      AND race_title = 'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe';