Example: Aggregate UDF - Advanced SQL Engine - Teradata Database

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