nPath Syntax Elements - Analytics Database

Database Analytic Functions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-06
dita:mapPath
gjn1627595495337.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
jmh1512506877710
Product Category
Teradata Vantageā„¢
Mode
Specify the pattern-matching mode:
Option Description
OVERLAPPING Find every occurrence of pattern in partition, regardless of whether it is part of a previously found match. One row can match multiple symbols in a given matched pattern.
NONOVERLAPPING Start next pattern search at row that follows last pattern match.
Pattern
Specify the pattern for which the function searches. You compose pattern with the symbols (which you define in the Symbols syntax element), operators, and parentheses.
When patterns have multiple operators, the function applies them in order of precedence, and applies operators of equal precedence from left to right. To force the function to evaluate a subpattern first, enclose it in parentheses. For more information, see nPath Patterns.
Symbols
Defines the symbols that appear in the values of the Pattern and Result syntax elements. The col_expr is an expression whose value is a column name, symbol is any valid identifier, and symbol_predicate is a SQL predicate (often a column name).
Each col_expr = symbol_predicate must satisfy the SQL syntax of the Analytics Database when nPath is invoked. Otherwise, it is a syntax error.
For example, this Symbols syntax element is for analyzing website visits:
Symbols (
  pagetype = 'homepage' AS H,
  pagetype <> 'homepage' AND pagetype <> 'checkout' AS PP,
  pagetype = 'checkout' AS CO
)
The symbol is case-insensitive; however, a symbol of one or two uppercase letters is easy to identify in patterns.
If col_expr represents a column that appears in multiple input tables, 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
)
For more information about symbols that appear in the Pattern syntax element value, see nPath Symbols. For more information about symbols that appear in the Result syntax element value, see nPath Results.
Filter
[Optional] Specify filters to impose on the matched rows. The function combines the filter expressions using the AND operator.
This is the filter_expression syntax:
symbol_expression comparison_operator symbol_expression
The two symbol expressions must be type-compatible. This is the symbol_expression syntax:
{ FIRST | LAST }(column_with_expression OF [ANY](symbol[,...]))
The column_with_expression cannot contain the operator AND or OR, and all its columns must come from the same input. If the function has multiple inputs, column_with_expression and symbol must come from the same input.
The comparison_operator is either <, >, <=, >=, =, or <>.
Result
Defines the output columns. The col_expr is an expression whose value is a column name; it specifies the values to retrieve from the matched rows. The function applies aggregate_function to these values. For details, see nPath Results.
The function evaluates this syntax element once for every matched pattern in the partition (that is, it outputs one row for each pattern match).