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