17.10 - Example: Ensuring Meaningful Results from a Sequenced Aggregation - 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)

To use aggregate functions meaningfully on the charge column, it would need to be expressed as a cumulative value that matches the granularity of the valid time column. For example, if the charge for each aircraft service job is expressed, in this case, as a charge per day, sequenced aggregations such as SUM and AVG can be applied with meaningful results.

For example, assume the data for the aircraft company had the charge for each type of service job expressed as a charge per day.

ID Job_Type ChargePerDay Duration
123 Wing 20 4 Jan 2011 – 8 Jan 2011
123 Fuselage 10 5 Jan 2011 – 7 Jan 2011
123 Landing Gear 2 6 Jan 2011 – 9 Jan 2011

The following sequenced aggregate query yields meaningful results.

SEQUENCED VALIDTIME
SELECT id,
       SUM (ChargePerDay) TotalChargePerDay ,
       AVG (ChargePerDay) AvgChargePerDay
FROM aircraft_service
GROUP BY 1
ORDER BY VALIDTIME;
ID TotalChargePerDay AveChargePerDay VALIDTIME
123 20 20 ('11/01/04', '11/01/05')
123 30 15 ('11/01/05', '11/01/06')
123 32 11 ('11/01/06', '11/01/07')
123 22 11 ('11/01/07', '11/01/08')
123 2 2 ('11/01/08', '11/01/09')