1.0 - 8.00 - GTree Example 2: Show Only Paths That Cycle or End at Leaves - 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)

Input

Input tables are from GTree Example 1: Show All Paths from Root Nodes:

  • vertices: gtree_vertices
  • edges: gtree_edges
  • root: gtree_root

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 ('end') 
  Results (
    'Propagate (nodeid) AS start_vertex',
    'Current (nodeid) AS end_vertex',
    'Path (nodestring)',
    'Sum (value)',
    'Cycle()',
    'Leaf()'
  )
  EdgeResults ('PATH(nodestring, endnodestring) AS edgepath')
) AS dt ORDER BY 1,2;

Output

start_vertex end_vertex path(node_string) sum(value) cycle() leaf() edgepath
1 4 Park St->Walnut St->Water St-> 8 true false .-Park StWalnut St->.-Walnut StWater St->.-Water StPark 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 5 Main St->Walnut St->Water St->High St 10.25 false true .-Main StWalnut St->.-Walnut StWater St->.-Water StHigh St->.