16.20 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Administration
Programming Reference
Publication ID
B035-1210-162K
Language
English (United States)

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