17.10 - Example: SUM and AVG Aggregations in a Sequenced Query - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

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

SUM and AVG aggregations can be used over state columns of valid-time temporal tables. In our aircraft service example, the information in the NumWorkersAssigned column is a state value, valid for the duration specified in the valid-time column. It is meaningful to ask about the total or average number of workers assigned to an aircraft, as in the following example.

SEQUENCED VALIDTIME
SELECT id,
       SUM (NumWorkersAssigned) TotalWorkersAssigned,
       AVG (NumWorkersAssigned) AvgWorkersAssigned,
FROM aircradt_service
GROUP BY 1
ORDER BY VALIDTIME;
ID TotalWorkersAssigned AvgWorkersAssigned VALIDTIME
123 5 5 ('11/01/04', '11/01/05')
123 8 4 ('11/01/05', '11/01/06')
123 9 3 ('11/01/06', '11/01/07')
123 6 3 ('11/01/07', '11/01/08')
123 1 1 ('11/01/08', '11/01/09')