nPath Arguments - Aster Analytics

Teradata AsterĀ® Analytics Foundation User GuideUpdate 2

Product
Aster Analytics
Release Number
7.00.02
Published
September 2017
Language
English (United States)
Last Update
2018-04-17
dita:mapPath
uce1497542673292.ditamap
dita:ditavalPath
AA-notempfilter_pdf_output.ditaval
dita:id
B700-1022
lifecycle
previous
Product Category
Software
Mode
Specifies the pattern-matching mode:
  • OVERLAPPING:

    The function finds every occurrence of the pattern in the partition, regardless of whether it 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 many commonly used pattern matching utilities, including the UNIX grep utility.

Pattern
Specifies the pattern for which the function searches. You compose pattern with the symbols (which you define in the Symbols argument), 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 Patterns.

Symbols
Defines the symbols that appear in the values of the Pattern and Result arguments. 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).

For example, this Symbols argument 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 argument value, see Symbols. For more information about symbols that appear in the Result argument value, see Results.

Filter
[Optional] Specifies filters to impose on the matched rows. The function combines the filter expressions using the AND operator.

The filter_expression syntax is:

symbol_expression comparison_operator symbol_expression

The two symbol expressions must be type-compatible. The symbol_expression syntax is:

{ 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 !=.

Whether this argument improves or degrades nPath performance depends on several factors. For details, see Filters.

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

The function evaluates this argument once for every matched pattern in the partition (that is, it outputs one row for each pattern match).