1.0 - 8.00 - NTree Arguments - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)
Root_Node
Specify the BOOLEAN SQL expression that defines the root nodes of the trees (for example, parent_id IS NULL).
Node_ID
Specify 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
Specify the SQL expression whose value identifies the parent node.
AllowCycles
Specify whether trees can contain cycles. If not, a cycle in the data set causes the function to throw an exception. For information about cycles, see Cycles in NTree.
Default: 'false'
Starts_With
Specify the node from which to start tree traversal—'root', 'leaf', or a SQL expression that identifies a node.
Mode
Specify the direction of tree traversal from the start node—up to the root node or down to the leaf nodes.
Output
Specify 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'
MaxDistance
[Optional] Specify the maximum tree depth.
Default: 5
Logging
[Optional] Specify whether the function prints log messages.
Default: 'false'
Result
Specify aggregate operations to perform during tree traversal, using this syntax:
operation (expression) [ ALIAS alias ]
operation
One of the following:
operation operation Action for Path from Starts_With Node to Last Traversed Node
PATH Outputs value of expression for each node, separating values with '->'.
SUM Computes value of expression for each node and outputs sum of these values.
LEVEL Outputs number of hops.
MAX Computes value of expression for each node and outputs highest of these values.
MIN Computes value of expression for each node and outputs lowest of these values.
IS_CYCLE Outputs cycle (if any).
AVG Computes value of expression for each node and outputs average of these values.
PROPAGATE Evaluates expression with value of Starts_With node and propagates result to every node.
expression
SQL expression. Must be * if operation is LEVEL or IS_CYCLE.
alias
Name of the output table column that contains the result of the operation.
The function ignores alias if it is the same as an input table column name.
Default: The string operation(expression); for example, PATH(node_name)