17.00 - Average of Minimums - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1208-170K

The two example queries show the average of nested minimums. In the first query, the table is a PTI table and the second example shows a non-PTI table:

/*PTI Table*/
SELECT AVG(TEMP_MIN) FROM (
    SELECT MIN(TEMPERATURE) AS TEMP_MIN
    FROM OCEAN_BUOYS
    WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00'
    GROUP BY TIME (MINUTES(10) AND BUOYID)
) AS NESTED_MIN_TABLE;


/*Non-PTI Table*/
SELECT AVG(TEMP_MIN) FROM (
    SELECT MIN(TEMPERATURE) AS TEMP_MIN
    FROM OCEAN_BUOYS_NONPTI
    WHERE TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00'
    GROUP BY TIME (MINUTES(10) AND BUOYID)
    USING TIMECODE(TIMECODE)
) AS NESTED_MIN_TABLE;

The results get loaded into the derived table NESTED_MIN_TABLE. They are shown in ascending order, but this is not necessarily the expected order of the values in the derived table.

The results of both queries are identical, so only one result set is shown.
TEMP_MIN
10
10
43
43
70

Result: The actual result of the minimum of results (which are nested in the NESTED_MIN_TABLE):

Average(TEMP_MIN)
-----------------
35