{ GROUP BY options | GROUP BY TIME ( { timebucket_duration | * } [ AND value_expression [,...] ] ) [ USING ( timestamp_date_col [, seqno_col ] ) ] [ FILL ( { NULLS | numeric_constant | PREVIOUS | NEXT } ) ] }
Syntax Elements
- options
- The optional clauses for GROUP BY TIME are: USING TIMECODE and FILL.
- timebucket_duration
{ CAL_YEARS | CAL_MONTHS | CAL_DAYS | WEEKS | DAYS | HOURS | MINUTES | SECONDS | MILLISECONDS | MICROSECONDS } ( pos_int )
- value_expression
The value_expression is a column or any expression involving columns (except for scalar subqueries). These expressions are used for grouping purposes not related to time.
There can be one or more comma separated value expressions.
The value_expression must not be a column reference to a view column that is derived from a function and cannot contain any ordered analytical or aggregate functions. The value_expression cannot be a literal.
- timestamp_date_col
- A column expression (with an optional table name) that serves as the timecode for a non-PTI table.
- seqno_col
- A column expression (with an optional table name) that is the sequence number. For a PTI table, it can be TD_SEQNO or any other column that acts as a sequence number. For a non-PTI table, seqno_col is a column that plays the role of TD_SEQNO (because non-PTI tables do not have TD_SEQNO).
- FILL
- The FILL clause allows you to provide values for missing timebucket values. The following variables and values are used by the FILL clause. For more information, see Using the FILL Clause to Handle Missing Timebuckets.
- NULLS
- The missing timebuckets are returned to the user with a null value for all aggregate results.
- numeric_constant
- Any Teradata Vantage supported Numeric literal. The missing timebuckets are returned to the user with the specified constant value for all aggregate results. If the data type specified in the FILL clause is incompatible with the input data type for an aggregate function, an error is reported.
- PREVIOUS
- PREV
- The missing timebuckets are returned to the user with the aggregate results populated by the value of the closest previous timebucket with a non-missing value. If the immediate predecessor of a missing timebucket is also missing, both buckets, and any other immediate predecessors with missing values, are loaded with the first preceding non-missing value. If a missing timebucket has no predecessor with a result (for example, if the timebucket is the first in the series or all the preceding timebuckets in the entire series are missing), the missing timebuckets are returned to the user with a null value for all aggregate results. The abbreviation PREV may be used instead of PREVIOUS.
- NEXT
- The missing timebuckets are returned to the user with the aggregate results populated by the value of the closest succeeding timebucket with a non-missing value. If the immediate successor of a missing timebucket is also missing, both buckets, and any other immediate successors with missing values, are loaded with the first succeeding non-missing value. If a missing timebucket has no successor with a result (for example, if the timebucket is the last in the series or all the succeeding timebuckets in the entire series are missing), the missing timebuckets are returned to the user with a null value for all aggregate results.
- pos_int
- A 16-bit positive integer with a maximum value of 32767.