1.1 - 8.10 - NTree Syntax Elements - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)
RootNode
Specify the BOOLEAN SQL expression that defines the root nodes of the trees (for example, parent_id IS NULL).
NodeID
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.
ParentID
Specify the SQL expression whose value identifies the parent node.
AllowCycles
[Optional] 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'
StartsWith
Specify the node from which to start tree traversal—'root', 'leaf', or a SQL expression that identifies a node.
Direction
Specify the direction of tree traversal from the start node—up to the root node or down to the leaf nodes.
OutputType
Specify when to output a tuple—at every node along the traversal path ('all') or only at the end of the traversal path ('end').
MaxDistance
[Optional] Specify the maximum tree depth.
Default: 5
OutputLogMessages
[Optional] Specify whether the function prints log messages.
Default: 'false'
Results
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 StartsWith 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 StartsWith 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)