1.1 - 8.10 - GTree Example: Paths That Cycle or End at Leaves - 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 only paths that either cycle or end at leaves.

Input

Input tables are from GTree Example: 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(nodestring)                           sum(value1) cycle() leaf() edgepath         
 ------------ ---------- ----------------                           ----------- ------- ------ ---------------- 
            1          4 park st->walnut st->water st->             8.0          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->.

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