AllPairsShortestPath Example: Unweighted, Unbounded Graph - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.10
1.1
Published
October 2019
Language
English (United States)
Last Update
2019-12-31
dita:mapPath
ima1540829771750.ditamap
dita:ditavalPath
jsj1481748799576.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantage™

Input

In the following figure, nodes represent persons—light blue for males and dark blue for females. The directed edges represent phone calls from one person to another. Node size represents number of connections (degree centrality).

Graph of Phone Calls between Persons
Graph of input for example of Machine Learning Engine function AllPairsShortestPath on unweighted, unbounded graph

The graph in the figure is represented by the Vertices and Edges tables callers and calls, respectively.

Vertices: callers
callerid callername
1 John
2 Carla
3 Simon
4 Celine
5 Winston
6 Diana
Edges: calls
callerfrom callerto calls
2 4 7
2 6 12
4 6 4
1 2 10
1 3 2
1 4 5
1 6 6
3 6 1
5 6 10

SQL Call

SELECT * FROM AllPairsShortestPath (
  ON callers AS Vertices PARTITION BY callerid
  ON calls AS Edges PARTITION BY callerfrom
  USING
  TargetKey ('callerto')
  MaxDistance (-1)
) AS dt ORDER BY source, target;

Output

 source target distance 
 ------ ------ -------- 
      1      2      1.0
      1      3      1.0
      1      4      1.0
      1      6      1.0
      2      4      1.0
      2      6      1.0
      3      6      1.0
      4      6      1.0
      5      6      1.0

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