Purpose
Expands a column having a PERIOD data type, creating a regular time series of rows based on the period value in the input row.
The expansion of a PERIOD column produces one value-equivalent row for each of the time granules in the epoch or timestamp representation of the specified time granule.
You can perform time series expansions only on PERIOD expressions which can be regular period expressions or derived period columns.
Syntax Elements
- EXPAND ON expand_expression
- Time series expansion information in a query expression.
- The EXPAND ON clause generates a regular time series as a sequence of values at each of the granules or at each point of a predefined interval in the specified period from an input row.
- The expand_expression variable specifies a PERIOD column name or PERIOD expression on which the selected rows are to be expanded. This is referred to as the input row for the EXPAND ON operation.
- If the expansion period is null, then the expanded row has a null expanded value.
- The specified column or expression must have a PERIOD data type or can be a derived period column.
-
expand_column_
alias
- AS expand_column_
alias
- aliased name of the PERIOD expression to be expanded.
- The aliased column or expression must have a PERIOD data type.
- You can reference expand_column_alias in the select list, including inside an expression that does not reference any other columns and refers to the expanded value for an expanded row.
- You cannot specify expand_column_alias in other clauses within the same query block except for an ORDER BY clause.
- You cannot specify expand_column_alias in any clauses in a subquery or correlated subquery, nor can you specify it in any aggregate or statistical function in the select list.
- BY expand_interval
- the interval literal (interval expression) or anchored literal by which expand_column_name is to be expanded, where expand_interval is one of the valid expand_interval options, including:
- interval_literal
- ANCHOR anchor_name
- ANCHOR PERIOD anchor_name
- For a complete list of the expand interval literals, see Expand Interval Literal Expressions.
- This value specifies the granularity of the value-equivalent rows produced by the EXPAND ON clause.
- If you do not specify the BY expand_interval option, then the expansion interval defaults to the granularity of the element type of the PERIOD value for the column.
- The expansion commences from the beginning value of the expansion period and terminates at the ending value of the expansion period, incrementing by interval_literal for each expanded row.
- In this process, the last expanded row might not cover the expansion interval duration, producing only a partial period. By definition, a partial period is a period with a duration that is less than the expanded interval.
- ANCHOR anchor_name
- ANCHOR PERIOD anchor_name
- Expansion is an anchor PERIOD expansion.
- Specify PERIOD only for anchor PERIOD expansions.
- If you specify PERIOD for noninterval data, then the expansion is an Anchor PERIOD expansion.
- If you do not specify PERIOD for noninterval data, then the expansion is an Anchor Point expansion.
- The variable anchor_name specifies an anchor name literal. For a complete list of the anchor name literals, see Anchor Name Literal Expressions.
- AT time_literal
- Optional Time literal. For a complete list of Time literals, see
SQL Data Types and Literals
.
- If you do not specify a time literal value, the value defaults to ‘00:00:00.000000 00:00’ for an ANCHOR_MILLISECOND, ANCHOR_SECOND, ANCHOR_MINUTE, ANCHOR_HOUR, WEEK_BEGIN, MONTH_BEGIN, QUARTER_BEGIN, or YEAR_BEGIN anchor and to ‘23:59:59.999999 00:00’ for any other anchors.
- FOR period_expression
- Optional PERIOD expression. For a comprehensive list of valid PERIOD expressions, see
SQL Data Types and Literals
.
- The purpose of specifying a PERIOD expression is to limit the number of rows to be expanded to period_expression, where period_expression represents the period of interest.
- The expansion period is the overlapping period of the qualified row and the PERIOD constant you specify as period_expression.
- Otherwise, the expanding period is the PERIOD value of the selected row.
- The data type of period_expression must be comparable with the PERIOD data type of the expanded column.
- If the specified PERIOD expression is either null or does not overlap with the row, then Teradata Database does not expand the row because it does not qualify for expansion.
ANSI Compliance
EXPAND ON is a Teradata extension to the ANSI SQL:2011 standard.
Because of this, Teradata Database reports a warning message if you use an EXPAND ON clause in a session in which the SQL Flagger is set. For information about the SQL Flagger, see
SQL Fundamentals
.
Note that the Teradata SQL EXPAND ON clause does not share any functionality with the EXPANDING clause of the ANSI SQL:2011 standard.