17.05 - Sequenced Valid-Time Queries - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K

Sequenced temporal queries allow the extraction of the past, current, or future sequence of states of a temporal table. A query that is sequenced in valid time spans those rows with a period of validity that overlaps the period of applicability of the query. Additional conditions can be specified on the valid-time column to further filter the rows as required.

A query that is sequenced in valid time extracts the states of the tables at each point of time as specified in the period of applicability. The resulting table is a valid-time table. The query is over one or more valid-time tables and produces a valid-time result.

Rows having NULL in the valid-time column are not in the result.

The result set includes a new column named VALIDTIME, which is automatically appended by the system. VALIDTIME shows the valid time of the rows in the result set of the query. This is different from the valid times that were originally defined for the rows. Because a sequenced query specifies a particular time period of applicability, the period of validity of the results is limited by that period of applicability. Therefore, the valid time of each row in the result set is the intersection of the period of applicability of the query with the periods of validity of the qualified rows.

Example: Sequenced valid-time query

SEQUENCED VALIDTIME PERIOD '(2009-01-01, 2009-12-31)'
SELECT *
FROM Policy;

Policy_ID Customer_ID Policy_Type Policy_Details      VALIDTIME
--------- ----------- ----------- ------------------- ------------------------
   541077   766492008 AU          STD-CH-344-YXY-00   ('09/12/21', '09/12/31')
   541008   246824626 AU          STD-CH-345-NXY-00   ('09/10/01', '09/12/31')
   541145   616035020 AU          STD-CH-348-YXN-01   ('09/12/03', '09/12/31')

Although VALIDTIME is the valid-time column of the result set, Validity is the valid-time column of the originally queried Policy table. To show the Validity column in the results requires a subquery, because the valid-time column name cannot appear anywhere in a query that includes a PA. Use a sequenced validtime subquery that does not specify a PA. Because the Validity column is not the valid-time column of the derived table, it can be retrieved using a sequenced outer query that includes a PA:

Example: Sequenced valid-time query returning original valid-time column

SEQUENCED VALIDTIME PERIOD '(2009-01-01, 2009-12-31)' 
SELECT Policy_ID, Customer_ID, Validity FROM (
   SEQUENCED VALIDTIME SELECT Policy.*, Validity 
   FROM Policy) AS my_derived_table;

Policy_ID   Customer_ID  Validity                 VALIDTIME
----------- -----------  -----------------------  ------------------------
     541077   766492008  ('09/12/21', '99/12/31') ('09/12/21', '09/12/31')
     541008   246824626  ('09/10/01', '99/12/31') ('09/10/01', '09/12/31')
     541145   616035020  ('09/12/03', '10/12/01') ('09/12/03', '09/12/31')

Comparing the Validity and VALIDTIME columns demonstrates that the valid-time period of the result set is the intersection of the valid-time period in the original Policy table Validity column with the PA specified in the sequenced select statement.

This example uses the SELECT * asterisk notation in combination with explicit specification of a temporal column. For more information on the asterisk see Asterisks in Select Lists

Other clauses of the SELECT statement, such as WHERE, cannot reference the new VALIDTIME column, and VALIDTIME cannot be used as an alias for any other projected columns.

If a view or a derived table results in a valid-time table, it is treated like any other valid-time table specified in the query. Otherwise, it is treated like a nontemporal table.

The sequenced form of a temporal query is limited to a simple select from a single valid-time table or a simple select with inner joins from multiple tables. A noncorrelated scalar subquery can be used in the temporal query.

The following are not supported for sequenced queries:
  • Outer joins
  • Set operations
  • Ordered analytic functions
  • Subqueries other than noncorrelated scalar subqueries
  • WITH, WITH RECURSIVE, TOP n, DISTINCT
The following rules apply to sequenced inner joins:
  • The term row or rows mentioned in the context of sequenced inner joins implies all the rows of a table where the period of validity overlaps with the period of applicability of the query and satisfies any specified single table conditions.
  • The joined row contains the projected columns in the specified order and a valid-time column whose value is set to the result of the intersection of the period of applicability, the period of validity of the left row, and the period of validity of the right row.
  • If the valid-time column of each table involved in the join has the same granularity as the period of applicability, the granularity of the resulting period of validity is the same as the granularity of the period of applicability. Otherwise, the granularity of the result period of validity is the finest amongst them and each valid-time column is implicitly converted to the finest valid-time granularity before the join is performed.

    For example, consider a sequenced join with a PERIOD(DATE) period of applicability, a table with a PERIOD(TIMESTAMP(3)) valid-time column, and a table with a PERIOD(TIMESTAMP(5)) valid-time column. The resulting VALIDTIME column has a PERIOD(TIMESTAMP(5)) data type.

    The granularity difference does not usually cause an issue. However, it can be confusing for some types of projected columns. For example a column that represents charge per day in the original table can be confusing when it is projected in a result having a validity of hours. Such cases can be avoided if tables with similar granularities are joined, or the data model specifies the same granularity for tables that are likely to be joined.

    Similarly if a non-temporal table is joined with a temporal table, the resulting rows reflect the validity of the temporal table. This validity can be misleading if applied to data that was not originally of a temporal nature.

  • For a sequenced inner join, the left row is joined with a matching row from the right table only if the periods of validity of the rows overlap.

A sequenced query with an ORDER BY clause may specify ordering on the resulting VALIDTIME column. To specify this column in the ORDER BY clause, use the VALIDTIME keyword or use its name delimited by double quotation marks ("VALIDTIME"). If the ordering clause does not specify VALIDTIME, the resulting VALIDTIME column is automatically made a part of the ORDER BY list as the last element in the list with ASC (ascending) default ordering

If the query involves a bitemporal or transaction-time table, refer to the following topics for additional information that applies to the transaction-time dimension:

Aggregate Functions in Sequenced Queries

The result of a sequenced valid-time query is a temporal table with rows that include a VALIDTIME column. The VALIDTIME column shows the valid time, the time for which the row information is valid, for the rows in the result set. This result set valid time is the intersection of the period of applicability of the query with the periods of validity of the qualified rows in the original queried table.

You can include aggregate functions in sequenced valid-time queries. The aggregation is performed on every distinct duration defined by the combination of VALIDTIME periods in the result set.

SEQUENCED TRANSACTIONTIME aggregations are not supported.

Usage Notes

A valid-time table represents state information that is considered valid for a specified duration, the valid time of the row. The valid data can be constant over the period of time, or may represent values that accumulate over the duration of the valid time period. Examples of state information would be:

  • A quantity of an inventory item that represents the amount of the item in stock during a specified period.
  • An insurance policy that has a specific duration of time for which it is valid.
  • The number of hours worked per unit time, such as hours per day worked on a project, where the valid time is expressed in units of whole days, using a PERIOD (DATE) data type.

It is important to carefully evaluate and choose the columns to which aggregations are applied in temporal tables. If an aggregation over time is applied to a non-state column, the results can be misleading and inaccurate.

  • When used on valid-time temporal tables, aggregations such as SUM, AVG, MIN, and MAX should be performed only on state columns. Using such aggregate functions on non-state columns can lead to meaningless or misleading results.
  • The COUNT aggregation is generally safe to perform and gives straightforward results. However, you should ensure that the results are interpreted in an appropriate way that corresponds to the columns used for the aggregation.

Example: COUNT aggregation in a sequenced query

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.

Example: MIN and MAX aggregations in a sequenced query

MIN and MAX functions can be used in sequenced aggregations with temporal tables, however it is important to apply them to appropriate columns. For the aircraft service example, the information in the Charge column is not a state value. Applying MIN and MAX to this column in a time dependent sequenced aggregation would give results that are easily misinterpreted or not meaningful.

However, it would be meaningful to ask for the minimum and maximum number of workers assigned to all jobs combined at any time:

SEQUENCED VALIDTIME
SELECT id,
       min(NumWorkersAssigned) as Minworkers,
       max(NumWorkersAssigned) as Maxworkers
FROM aircraft_service
GROUP BY 1
ORDER BY VALIDTIME;
ID Minworkers Maxworkers VALIDTIME
123 5 5 ('11/01/04', '11/01/05')
123 3 5 ('11/01/05', '11/01/06')
123 1 5 ('11/01/06', '11/01/07')
123 1 5 ('11/01/07', '11/01/08')
123 1 1 ('11/01/08', '11/01/09')

Example: SUM and AVG aggregations in a sequenced query

SUM and AVG aggregations can be used over state columns of valid-time temporal tables. In our aircraft service example, the information in the NumWorkersAssigned column is a state value, valid for the duration specified in the valid-time column. It is meaningful to ask about the total or average number of workers assigned to an aircraft, as in the following example.

SEQUENCED VALIDTIME
SELECT id,
       SUM (NumWorkersAssigned) TotalWorkersAssigned,
       AVG (NumWorkersAssigned) AvgWorkersAssigned,
FROM aircradt_service
GROUP BY 1
ORDER BY VALIDTIME;
ID TotalWorkersAssigned AvgWorkersAssigned VALIDTIME
123 5 5 ('11/01/04', '11/01/05')
123 8 4 ('11/01/05', '11/01/06')
123 9 3 ('11/01/06', '11/01/07')
123 6 3 ('11/01/07', '11/01/08')
123 1 1 ('11/01/08', '11/01/09')

Example: Ensuring meaningful results from a sequenced aggregation

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')

Example: Using a sequenced aggregation with GROUP BY

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 example above 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 query above 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.