16.20 - Average of Averages - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ Time Series Tables and Operations

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1208-162K
Language
English (United States)

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

/*PTI Table*/
SELECT AVG(TEMP_AVG) FROM (
    SELECT AVG(TEMPERATURE) AS TEMP_AVG
    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_AVG_TABLE;


/*Non-PTI Table*/
SELECT AVG(TEMP_AVG) FROM (
    SELECT AVG(TEMPERATURE) AS TEMP_AVG
    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_AVG_TABLE;

The results get loaded into the derived table NESTED_AVG_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_AVG
43
50
54
55
74

Result: The actual result of the average of results (which are nested in the NESTED_AVG_TABLE):

Average(TEMP_AVG)
-----------------
55.2