Aggregate Functions in Sequenced Queries - 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™

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