- Specifies the BOOLEAN SQL expression that defines the root nodes of the trees (for example, parent_id IS NULL).
- 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.
- Specifies the SQL expression whose value identifies the parent node.
- 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.
- Specifies the node from which to start tree traversal—must be 'root', 'leaf', or a SQL expression that identifies a node.
- Specifies the direction of tree traversal from the start node—up to the root node or down to the leaf nodes.
- 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'.
- [Optional] Specifies the maximum tree depth. Default: 5.
- [Optional] Specifies whether the function prints log messages. Default: 'false'.
- 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:
Outputs the value of expression for each node, separating values with '->'.
Computes the value of expression for each node and outputs the sum of these values.
Outputs the number of hops.
Computes the value of expression for each node and outputs the highest of these values.
Computes the value of expression for each node and outputs the lowest of these values.
Outputs the cycle (if any).
Computes the value of expression for each node and outputs the average of these values.
Evaluates expression with the value of the Starts_With node and propagates the result to every node.