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: AVG, COUNT, MAX, MIN, and SUM, described in Aggregate Functions
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 [ (size, truncateFlag) ] ( [ DISTINCT | CDISTINCT ] col_expr OF symbol_list [ DELIMITER 'delimiter'] ) [ AS result_col_name ] |
Returns, for each matched row, the concatenated values in col_expr, separated by delimiter. size is the maximum size of the accumulated string. The default value of size is 64000 and the maximum is 2097088000. If size is greater than 64000, the accumulated string has data type CLOB. Otherwise, it has data type VARCHAR. truncateFlag specifies whether to truncate the accumulated string if it is longer than size. truncateFlag is TRUE or FALSE (not enclosed in single quotation marks). Its default value is FALSE. delimiter is a string of LATIN characters. Its default value is ', ' (a comma followed by a space). Its maximum size is 100 bytes. DISTINCT limits the concatenated values to distinct values. CDISTINCT limits the concatenated values to consecutive distinct values. The accumulated string can have at most 2097088000 LATIN characters. ACCUMULATE does not support UNICODE characters in the input data. |
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.
See nPath Results Examples for a list of examples.