16.20 - nPath Results - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ - NewSQL Engine Analytic Functions

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
July 2019
Content Type
Programming Reference
Publication ID
B035-1206-162K
Language
English (United States)

The Result syntax element defines the output columns, specifying the values to retrieve from the matched rows and the aggregate function to apply to these values.

For each pattern, the nPath function can apply one or more aggregate functions to the matched rows and output the aggregated results. These are the supported aggregate functions:

  • SQL aggregate functions AVG, COUNT, MAX, MIN, and SUM, described in Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
  • ML Engine nPath sequence aggregate functions described in the following table

In the following table, col_expr is an expression whose value is a column name, symbol is defined by the Symbols syntax element, and symbol_list has this syntax:

{ symbol | ANY (symbol[,...]) }
Function Description
COUNT (
 { * | [DISTINCT] col_expr }
 OF symbol_list )
Returns either the number of total number of matched rows (*) or the number (or distinct number) of col_expr values in the matched rows.
FIRST (
  col_expr OF symbol_list )
Returns the col_expr value of the first matched row.
LAST (
  col_expr OF symbol_list )
Returns the col_expr value of the last matched row.
NTH (
 col_expr, n OF symbol_list )
Returns the col_expr value of the nth matched row, where n is a nonzero value of the data type SMALLINT, INTEGER, or BIGINT.

The sign of n determines whether the nth matched row is nth from the first or last matched row. For example, if n is 1, the nth matched row is the first matched row, and if n is -1, the nth matched row is the last matched row.

If n is greater than the number of matched rows, the nth function returns NULL.

FIRST_NOTNULL (
  col_expr OF symbol_list )
Returns the first non-null col_expr value in the matched rows.
LAST_NOTNULL (
  col_expr OF symbol_list )
Returns the last non-null col_expr value in the matched rows.
MAX_CHOOSE (
  quantifying_col_expr,
  descriptive_col_expr
  OF symbol_list )
Returns the descriptive_col_expr value of the matched row with the highest-sorted quantifying_col_expr value. For example, MAX_CHOOSE (product_price, product_name OF B) returns the product_name of the most expensive product in the rows that map to B.

The descriptive_col_expr can have any data type. The qualifying_col_expr must have a sortable datatype (SMALLINT, INTEGER, BIGINT, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, VARCHAR, or CHARACTER).

MIN_CHOOSE (
  quantifying_col_expr,
  descriptive_col_expr
  OF symbol_list )
Returns the descriptive_col_expr value of the matched row with the lowest-sorted qualifying_col_expr value. For example, MIN_CHOOSE (product_price, product_name OF B) returns the product_name of the least expensive product in the rows that map to B.

The descriptive_col_expr can have any data type. The qualifying_col_expr must have a sortable datatype (SMALLINT, INTEGER, BIGINT, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, VARCHAR, or CHARACTER).

DUPCOUNT (
  col_expr OF symbol_list )
Returns the duplicate count for col_expr in the matched rows. That is, for each matched row, the function returns the number of occurrences of the current value of col_expr in the immediately preceding matched row.

When col_expr is also the ORDER BY col_expr, this function returns the equivalent of ROW_NUMBER()-RANK().

DUPCOUNTCUM (
  col_expr OF symbol_list )
Returns the cumulative duplicate count for col_expr in the matched rows. That is, for each matched row, the function returns the number of occurrences of the current value of col_expr in all preceding matched rows.

When col_expr is also the ORDER BY col_expr, this function returns the equivalent of ROW_NUMBER()-DENSE_RANK().

ACCUMULATE (
  [ DISTINCT | CDISTINCT ]
  col_expr OF symbol_list
  [ DELIMITER 'delimiter'] )
Returns, for each matched row, the concatenated values in col_expr, separated by delimiter. The default delimiter is ', ' (a comma followed by a space).

DISTINCT limits the concatenated values to distinct values.

CDISTINCT limits the concatenated values to consecutive distinct values.

The characters in col_expr must belong to the LATIN character set. If they are from the UNICODE character set, translate them to LATIN. For example:
TRANSLATE (col_expr USING UNICODE_TO_LATIN) as col_expr

You can compute an aggregate over more than one symbol. For example, SUM (val OF ANY (A,B)) computes the sum of the values of the attribute val across all rows in the matched segment that map to A or B.