Example: Ensuring Meaningful Results from a Sequenced Aggregation - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™

To use aggregate functions meaningfully on the charge column, it must 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 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')