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 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.