17.10 - Example: COUNT Aggregation 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)

Consider the following information about three jobs performed by an aircraft service company:

ID Job_Type Charge Duration NumWorkersAssigned
123 Wing 80 4 Jan 2011 – 8 Jan 2011 5
123 Fuselage 20 5 Jan 2011 – 7 Jan 2011 3
123 Landing Gear 6 6 Jan 2011 – 9 Jan 2011 1

If the information is stored in a temporal table, where duration is the valid time column, the valid times for all the rows can be laid out like this:

Jan 4 5 6 7 8 9
    |-------|
      |---|
        |-----|

Aggregations in sequenced queries partition the combined valid-times of all rows into overlapping and non-overlapping durations. In the example, the valid time periods describe five distinct durations, defined by the boundaries of all the valid-time periods for all the rows in the table:

Jan 4 5 6 7 8 9
    |-|-|-|-|-|

Jan 4-5, 5-6, 6-7, 7-8, and 8-9.

A sequenced query of the whole table (with no WHERE clause qualification) would return aggregations across each of the five distinct durations:

SEQUENCED VALIDTIME
SELECT id, COUNT(*) jobcount
FROM aircraft_service
GROUP BY 1
ORDER BY VALIDTIME;

Returns:

ID Jobcount VALIDTIME
123 1 ('11/01/04', '11/01/05')
123 2 ('11/01/05', '11/01/06')
123 3 ('11/01/06', '11/01/07')
123 2 ('11/01/07', '11/01/08')
123 1 ('11/01/08', '11/01/09')
If you use an ORDER BY VALIDTIME clause with a sequenced valid-time query that includes an aggregation, the ordering takes into account the generated VALIDTIME distinct durations that were calculated to perform the aggregation.

If you use a GROUP BY VALIDTIME clause, the grouping does not use the distinct durations, but instead uses the same VALIDTIME values assigned to the results of nonaggregate queries: the intersection of the period of applicability of the query with the periods of validity of the qualified rows in the original queried table.