16.10 - Syntax Elements - Teradata Database

Teradata Analytic Functions

prodname
Teradata Database
vrm_release
15.10
16.10
created_date
November 2017
category
Programming Reference
featnum
B035-1206-151K

query_expression

SELECT statement, including a table operator SELECT call.

For information about the syntax of the SELECT clause, ON clause, PARTITION BY clause, DIMENSION clause, and ORDER BY clause, see the chapter on the SELECT statement in SQL Data Manipulation Language , B035-1146 .

For information about the supported symbol predicates, see the chapter on Logical Predicates in SQL Functions, Operators, Expressions, and Predicates, B035-1145.

ON Clause

The function requires at least one partitioned input table, and can have additional input tables that are either partitioned or DIMENSION tables.
If the input path to nPath is nondeterministic, then the results are nondeterministic.
ON table_name
Name of input table.
ON view_name
Name of input view.
ON query_expression
Expression that resolves to an input table.
AS alias_name
Alias name for the input table.
PARTITION BY column_name
Name of the column by which every partitioned input table is partitioned. The function requires at least one partitioned input table, and can have additional partitioned input tables.
PARTITION BY column_position
Position of the column by which every partitioned input table is partitioned. The function requires at least one partitioned input table, and you can specify additional partitioned input tables.
DIMENSION
You can specify additional DIMENSION input tables.
ORDER BY column_name
Name of the column by which every input table is ordered.
ORDER BY column_position
Position of the column to use for ordering the results.
ORDER BY sort_expression
Expression to use for ordering the results.
ASC
Sort results in ascending order.
DESC
Sort results in descending order.
NULLS FIRST
Sort nulls first in results.
NULLS LAST
Sort nulls last in results.

MODE

Specifies the pattern-matching mode.

OVERLAPPING
The function finds every occurrence of the pattern in the partition, regardless of whether the instance is part of a previously found match. Therefore, one row can match multiple symbols in a given matched pattern.
NONOVERLAPPING
The function begins the next pattern search at the row that follows the last pattern match. This is the default behavior of commonly used pattern matching utilities, including the UNIX grep utility.

PATTERN

Specifies the pattern for which the function searches.

symbolic_search_pattern
You compose symbolic_search_pattern with the symbols that you define in the SYMBOLS argument, operators, and parentheses.
To specify that a subpattern must appear a specific number of times, use the Range-Matching Feature defined later in this section. For pattern matching details, refer to Pattern Matching.
The following table describes the simplest patterns, which you can combine to form more complex patterns. When patterns have multiple operators, the function applies them in order of precedence, and applies operators of equal precedence from left to right. The following table also shows operator precedence. To force the function to evaluate a subpattern first, enclose the subpattern in parentheses. In the following table, A and B are symbols defined in the SYMBOLS argument.
Simple nPath Patterns and Operator Precedence
pattern Description Operator Precedence
A The function returns the rows that contain exactly one occurrence of A. 1 (highest)
A. The function returns the rows that contain exactly one occurrence of A. 1
A? The function returns the rows that contain at most one occurrence of A. The ? operator is nongreedy. 1
A* The function returns the rows that contain zero or more occurrences of A. The * operator is nongreedy. 1
A+ The function returns the rows that contain at least one occurrence of A. The + operator is nongreedy. 1
A.B Cascade operator. The function returns the rows that contain A followed immediately by B. 2
A|B Alternative (or) operator. The function returns the rows that contain either A or B. 3
^A Startanchor operator. The function returns the rows that start with A.  
A$ Endanchor operator. The function returns the rows that end with A.  

SYMBOLS

Defines the symbols that appear in the values of the PATTERN and RESULT arguments.

For example, following is a SYMBOLS argument for analyzing web site visits:
SYMBOLS (
   pagetype = 'homepage' AS H,
   pagetype <> 'homepage' AND pagetype <>
'checkout' AS PP,
   pagetype = 'checkout' AS CO
)

For more information about symbols that appear in the PATTERN argument value, refer to Symbols. For more information about symbols that appear in the RESULT argument value, refer to Result: Applying Aggregate Functions.

logical_predicate
An expression whose value is a column name. If col_expr represents a column that appears in multiple input tables, then you must qualify the ambiguous column name with its table name. For example:
Symbols (
   weblog.pagetype = 'homepage' AS H,
   weblog.pagetype = 'thankyou' AS T,
   ads.adname = 'xmaspromo' AS X,
   ads.adname = 'realtorpromo' AS R
)
AS symbol
Any valid identifier. The symbol is case-insensitive. However, a symbol of one or two uppercase letters is easier to identify in patterns.
symbol_predicate
SQL predicate, often a column name.

RESULT

Defines the output columns.

aggregate_function
The function to apply. For details, see Result: Applying Aggregate Functions. The function evaluates this argument once for every matched pattern in the partition. That is, the function outputs one row for each pattern match.
expression
An expression whose value is a column name. The expression specifies the values to retrieve from the matched rows.
symbol_list
Each symbol represents all the rows that matched the predicate of that symbol in this particular matched PATTERN.
The list can include a single symbol, or can be a comma separated list of more than one. For a single symbol list, ANY and the parentheses are optional. For example, OF A, OF ANY (A), and OF ANY (A,B) are valid for symbol_list, but OF (A,B) and OF (A) are not.
AS column_name
Determined by RESULTS argument. See Result: Applying Aggregate Functions.
Contains data to search for patterns.

Range-Matching Feature

You use the range-matching feature to specify the number of times that a subpattern must appear in a match. You can specify the count as one of the following, enclosed in braces, { }:
  • Exact number number of times that the subpattern appears in a match.
  • Minimum number of times that the subpattern appears in a match.
  • Minimum and maximum number of times that the subpattern appears in a match.
The format is as follows:
   (subpattern){n[,[m]]}
where n is the minimum and m is the maximum.
(subpattern){n}
Subpattern must appear exactly n times.
For example, the following pattern specifies that subpattern (A.B|C) must appear exactly 3 times:
   'X.(Y.Z).(A.B|C){3}'
The preceding pattern is equivalent to the following pattern:
   'X.(Y.Z).(A.B|C).(A.B|C).(A.B|C)'
(subpattern){n,}
Subpattern must appear at least n times. For example, the following pattern specifies that subpattern (A.B|C) must appear at least 4 times:
   'X.(Y.Z).(A.B|C){4,}'
The preceding pattern is equivalent to the following pattern:
   'X.(Y.Z).(A.B|C).(A.B|C).(A.B|C).(A.B|C)*'
(subpattern){n,m}
Specifies that subpattern must appear at least n times and at most m times. For example, the following pattern specifies that subpattern (A.B|C) must appear at least 2 times and at most 4 times:
   'X.(Y.Z).(A.B|C){2,4}'
The preceding pattern is equivalent to the following pattern:
   'X.(Y.Z).(A.B|C).(A.B|C).(A.B|C)?.(A.B|C)?'

Output

nPath Output Table Schema
Column Name Description
partition_column Column by which partitioned input tables are partitioned.
order_column Column by which input tables are ordered.
input_column Determined by RESULT argument. See Result: Applying Aggregate Functions.