Result: Applying Aggregate Functions - Teradata® Database

Database Analytic Functions

Product
Teradata® Database
Release Number
16.10
15.10
Published
November 2017
Language
English (United States)
Last Update
2018-05-10
dita:mapPath
hoj1499019223447.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-1206
lifecycle
previous
Product Category
Teradata® Database

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 specified one or more aggregate functions to the matched rows and output aggregate results.The supported aggregate functions are:
  • SQL aggregate functions AVG, COUNT, MAX, MIN, and SUM
  • Teradata 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 (
 { * 
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. For the example in Pattern Matching, FIRST (pageid OF B) returns the pageid of row t2.
LAST (
  col_expr OF
symbol_list )
Returns the col_expr value of the last matched row. For the example in Pattern Matching, LAST (pageid OF B) returns the pageid of row t4.
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 data type (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 data type (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 (
  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).

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.

For an example, see Example 1: Use FIRST, LAST_NOTNULL, MAX_CHOOSE, and MIN_CHOOSE.