15.00 - Aggregate UDF - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

Aggregate UDF

Purpose  

Takes grouped sets of relational data, makes a pass over each group, and returns one result for the group.

Syntax  

For UDFs that are defined with parameters of TD_ANYTYPE data type:

where:

 

Syntax element …

Specifies …

udf_name

the name of the aggregate UDF.

argument

a valid SQL expression. See Usage Notes for rules that apply to aggregate UDF arguments.

data_type

the desired return type of the TD_ANYTYPE result parameter.

column_expr

a table or a view column reference that is used to determine the return type of the TD_ANYTYPE result parameter.

ANSI Compliance

Aggregate UDFs are partially ANSI SQL:2011 compliant.

The requirement that parentheses appear when the argument list is empty is a Teradata extension to preserve compatibility with existing applications.

The RETURNS data_type or RETURNS STYLE clauses are Teradata extensions to the ANSI SQL standard.

Restrictions

  • Any restrictions that apply to standard SQL aggregate functions also apply to aggregate UDFs.
  • Aggregate UDF expressions cannot appear in a recursive statement of a recursive query. However, a non-recursive seed statement in a recursive query can specify an aggregate UDF.
  • Authorization

    You must have EXECUTE FUNCTION privileges on the function or on the database containing the function.

    To invoke an aggregate UDF that takes a UDT argument or returns a UDT, you must have the UDTUSAGE privilege on the SYSUDTLIB database or on the specified UDT.

    Usage Notes  

    When Teradata Database evaluates an aggregate UDF expression, it invokes the aggregate function once for each item in an aggregation group, passing the detail values of a group through the input arguments. To accumulate summary information, the context is retained each time the aggregate function is called.

    The rules that apply to the arguments in an aggregate function call are the same as those that apply to a scalar function call. See “UDF Arguments” on page 1324.

    The result type of an aggregate UDF is based on the return type specified in the RETURNS clause of the CREATE FUNCTION statement. If the result parameter is of TD_ANYTYPE data type, see “Result Data Type” on page 1324 for information on how the return type is determined.

    The default title of an aggregate UDF appears as:

       UDF_name(argument_list)

    Example  

    Consider the following table definition and data:

       CREATE TABLE Product_Life
       (Product_ID INTEGER,
        Product_class VARCHAR(30),
        Hours INTEGER);
     
       SELECT * FROM Product_Life;

    The output from the SELECT statement is:

    Product_ID  Product_class                         Hours
    -----------  ------------------------------  -----------
            100  Bulbs                                   100
            100  Bulbs                                   200
            100  Bulbs                                   300

    The following is the SQL definition of an aggregate UDF that calculates the standard deviation of the input arguments:

       CREATE FUNCTION STD_DEV (i INTEGER)
       RETURNS FLOAT
       CLASS AGGREGATE (64)
       SPECIFIC std_dev
       LANGUAGE C
       NO SQL
       PARAMETER STYLE SQL
       NOT DETERMINISTIC
       CALLED ON NULL INPUT
       EXTERNAL NAME 'ss!stddev!stddev.c!f!STD_DEV'

    The following query uses the aggregate UDF expression to calculate the standard deviation for the life of a light bulb.

       SELECT Product_ID, SUM(Hours), STD_DEV(Hours)
       FROM Product_Life
       WHERE Product_class = 'Bulbs'
       GROUP BY Product_ID;

    The output from the SELECT statement is:

    Product_ID   Sum(hours)          std_dev(hours)
    -----------  -----------  ----------------------
            100          600   8.16496580927726E 001

    Related Topics

     

    FOR more information on …

    SEE …

    SQL aggregate functions

    “Chapter 2 Aggregate Functions” on page 29.

    window aggregate UDFs

    “Window Aggregate UDF” on page 1330.

    implementing aggregate UDFs

    SQL External Routine Programming.

  • CREATE FUNCTION
  • REPLACE FUNCTION
  • SQL Data Definition Language.
  • Database Administration.
  • EXECUTE FUNCTION and UDTUSAGE privileges

    SQL Data Control Language.

    the TD_ANYTYPE data type

    SQL Data Types and Literals.