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

The Result argument 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. The supported aggregate functions are:

  • SQL aggregate functions AVG, COUNT, MAX, MIN, and SUM
  • Teradata Aster 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 argument, 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, BIGINTEGER, 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, BIGINTEGER, 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.

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.

More examples:

  • Example 1 uses FIRST, LAST_NOTNULL, MAX_CHOOSE, and MIN_CHOOSE.
  • Example 2 uses FIRST and three forms of ACCUMULATE.
  • Example 3 uses FIRST, three forms of ACCUMULATE, COUNT, and NTH.
  • Example 4 uses nPath to create output that combines values from one row in a sequence with values from the next row in the sequence.