Using AVERAGE with Time Series Examples | Teradata Vantage - 17.10 - Examples: Using AVERAGE with Time Series - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1208-171K
Language
English (United States)

Example: Calculating an Average for PTI and non-PTI Tables

The following example shows how the AVG function can be used with both Primary Time Index (PTI) and non-PTI tables.

The examples use the tables and data setup in Table and Data Definition for Time Series Aggregates Examples.

/*PTI Table*/
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, AVG(TEMPERATURE), COUNT(*)
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)
ORDER BY 2, 3;

/*Non-PTI Table*/
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, AVG(TEMPERATURE), COUNT(*)
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)
ORDER BY 2, 3;

Result:

The results of both queries are identical so only one result set is shown.
TIMECODE_RANGE GROUP BY TIME (MINUTES(10)) BUOYID AVG (TEMPERATURE) COUNT(*)
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') 1 0 54 3
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') 2 0 55 2
('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:10:00.000000+00:00') 7 1 74 6
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00') 13 44 50 10
('2014-01-06 10:10:00.000000+00:00', '2014-01-06 10:20:00.000000+00:00') 14 44 43 1

Example: Using the HAVING Clause to Filter the AVG Function Results for PTI and non-PTI Tables

The following example shows how the AVG function can be used with the HAVING clause to filter the results.

The examples use the tables and data setup in Table and Data Definition for Time Series Aggregates Examples.

/*PTI Table*/
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, AVG(TEMPERATURE), COUNT(*)
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)
HAVING AVG(TEMPERATURE) > 50
ORDER BY 2, 3;

/*Non-PTI Table*/
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, AVG(TEMPERATURE), COUNT(*)
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)
HAVING AVG(TEMPERATURE) > 50
ORDER BY 2, 3;

Result:

The results of both queries are identical so only one result set is shown.
TIMECODE_RANGE GROUP BY TIME (MINUTES(10)) BUOYID AVG (TEMPERATURE) COUNT(*)
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') 1 0 54 3
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') 2 0 55 2
('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:10:00.000000+00:00') 7 1 74 6