7.00.02 - nTree Arguments - Aster Analytics

Teradata Aster® Analytics Foundation User GuideUpdate 2

Product
Aster Analytics
Release Number
7.00.02
Release Date
September 2017
Content Type
Programming Reference
User Guide
Publication ID
B700-1022-700K
Language
English (United States)
Root_Node
Specifies the BOOLEAN SQL expression that defines the root nodes of the trees (for example, parent_id IS NULL).
Node_ID
Specifies the SQL expression whose value uniquely identifies a node in the input table (for example, order_id).
A node can appear multiple times in the data set, with different parents.
Parent_ID
Specifies the SQL expression whose value identifies the parent node.
Allow_Cycles
Specifies whether trees can contain cycles. If not, a cycle in the data set causes the function to throw an exception. Default: 'false'. For information about cycles, see Cycles in nTree.
Starts_With
Specifies the node from which to start tree traversal—must be 'root', 'leaf', or a SQL expression that identifies a node.
Mode
Specifies the direction of tree traversal from the start node—up to the root node or down to the leaf nodes.
Output
Specifies when to output a tuple—at every node along the traversal path ('all') or only at the end of the traversal path ('end'). Default: 'end'.
Max_Distance
[Optional] Specifies the maximum tree depth. Default: 5.
Logging
[Optional] Specifies whether the function prints log messages. Default: 'false'.
Result
Specifies aggregate operations to perform during tree traversal. The function reports the result of each aggregate operation in the output table. The syntax of aggregate is:
operation (expression) [ ALIAS alias ]

operation is either PATH, SUM, LEVEL, MAX, MIN, IS_CYCLE, AVG, or PROPAGATE.

expression is a SQL expression. If operation is LEVEL or IS_CYCLE, expression must be *.

alias is the name of the output table column that contains the result of the operation. Default: The string operation(expression); for example, PATH(node_name).

The function ignores alias if it is the same as an input table column name.
For the path from the Starts_With node to the last traversed node, the operations do the following:
  • PATH

    Outputs the value of expression for each node, separating values with '->'.

  • SUM

    Computes the value of expression for each node and outputs the sum of these values.

  • LEVEL

    Outputs the number of hops.

  • MAX

    Computes the value of expression for each node and outputs the highest of these values.

  • MIN

    Computes the value of expression for each node and outputs the lowest of these values.

  • IS_CYCLE

    Outputs the cycle (if any).

  • AVG

    Computes the value of expression for each node and outputs the average of these values.

  • PROPAGATE

    Evaluates expression with the value of the Starts_With node and propagates the result to every node.