1.1 - 8.10 - GTree Example: All Paths from Root Nodes - 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)

This example shows all paths from root nodes.

Input

The vertices (nodes) are bus stops in a small town. The Vertices table lists each bus stop and the boarding fare at that stop.

Vertices: gtree_vertices
nodeid nodestring value
1 Park St 2.25
2 Main St 2.25
3 Walnut St 2.25
4 Water St 3.5
5 High St 2.25

The Edges table represents the bus route. The columns nodeid and nodestring identify the source vertices (where the bus starts) and the columns endnodeid and endnodestring identify the target vertices (where the bus stops).

Edges: gtree_edges
nodeid nodestring endnodeid endnodestring
1 Park St 3 Walnut St
2 Main St 3 Walnut St
3 Walnut St 4 Water St
4 Water St 1 Park St
4 Water St 5 High St

The Root table defines the set of root vertices from which the function starts traversing the graph.

Root: gtree_root
nodeid nodestring value
1 Park St 2.25
2 Main St 2.25

SQL Call

SELECT * FROM GTree (
  ON gtree_vertices AS Vertices PARTITION BY nodeid, nodestring
  ON gtree_edges AS Edges PARTITION BY nodeid, nodestring
  ON gtree_root AS Root PARTITION BY nodeid, nodestring
  USING
  TargetKey ('endnodeid', 'endnodestring')
  AllowCycles ('t')
  MaxDepth (10)
  OutputType ('all')
  Results (
    'Propagate (nodeid) AS start_vertex',
    'Current (nodeid) AS end_vertex',
    'Path (nodestring)',
    'Sum (value1)',
    'Cycle()',
    'Leaf()'
  )
  EdgeResults ('PATH(nodestring, endnodestring) AS edgepath')
) AS dt ORDER BY 1,2;

Output

The output table has one column for each function that the Results or EdgeResults syntax element specifies. The edgepath column shows the links that comprise the path, the cycle column shows whether the path is a cycle, and the sum column shows the total fare for the path (the sum of the boarding fares at each node in the path).

 start_vertex end_vertex path(nodestring)                               sum(value1) cycle() leaf() edgepath         
 ------------ ---------- ----------------                               ----------- ------- ------ ---------------- 
            1          1 park st->                                          2.25    false   false  .-park stwalnut st->.
            1          3 park st->walnut st->                                4.5    false   false  .-park stwalnut st->.-walnut stwater st->.
            1          4 park st->walnut st->water st->                      8.0    true    false  .-park stwalnut st->.-walnut stwater st->.-water stpark st->
            1          4 park st->walnut st->water st->                      8.0    false   false  .-park stwalnut st->.-walnut stwater st->.-water sthigh st->.
            1          5 park st->walnut st->water st->high st             10.25    false   true   .-park stwalnut st->.-walnut stwater st->.-water sthigh st->.
            2          1 main st->walnut st->water st->park st->           10.25    true    false  .-main stwalnut st->.-walnut stwater st->.-water stpark st->.-park stwalnut st->
            2          2 main st->                                          2.25    false   false  .-main stwalnut st->.
            2          3 main st->walnut st->                                4.5    false   false  .-main stwalnut st->.-walnut stwater st->.
            2          4 main st->walnut st->water st->                      8.0    false   false  .-main stwalnut st->.-walnut stwater st->.-water stpark st->.
            2          4 main st->walnut st->water st->                      8.0    false   false  .-main stwalnut st->.-walnut stwater st->.-water sthigh st->.
            2          5 main st->walnut st->water st->high st             10.25    false   true   .-main stwalnut st->.-walnut stwater st->.-water sthigh st->.

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.