Example: Using a sequenced aggregation with GROUP BY - 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
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™

Sequenced aggregations together with GROUP BY in the query includes "empty" valid-time periods during which none of the temporal table rows is valid. Assume the temporal table from the preceding example included another row for which the valid-time period was not contiguous with any of the existing valid time periods.

ID Job_Type ChargePerDay Duration
123 Cockpit 40 1 Jan 2012 – 1 Mar 2012

The preceding query would have returned two additional rows, one for the new duration, and one for the period that included no aircraft maintenance work:

ID TotalChargePerDay AvgChargePerDay VALIDTIME
123 ? (NULL) ? (NULL) ('11/01/09', '12/01/01')
123 40 40 ('12/01/01', '12/03/01')

This allows you to answer questions such as "During the time covered by the table, when were no aircraft undergoing service?"

SEQUENCED VALIDTIME PERIOD(date'2011-01-01', date'2012-03-01')
SELECT id FROM aircraft_service
       HAVING COUNT(ChargePerDay)= 0
       GROUP BY 1
       ORDER BY 1;
ID VALIDTIME
123 ('11/01/09', '12/01/01')

To prevent the "empty" valid-time period from being returned, add a condition to the query using the HAVING clause to filter out these rows.