1.1 - 8.10 - nPath Greedy Pattern Matching - 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)

The nPath function uses greedy pattern matching, finding the longest available match despite any nongreedy operators in the pattern.

For example, consider the input table link2:

link2
userid title1 startdate enddate
21 Chief Exec Officer 1994-10-01 2005-02-28
21 Software Engineer 1996-10-01 2001-06-30
21 Software Engineer 1998-10-01 2001-06-30
21 Chief Exec Officer 2005-03-01 2007-03-31
21 Chief Exec Officer 2007-06-01 null

This query returns the following table:

SELECT job_transition_path, count(*) AS count1 FROM nPath@coprocessor (
  ON link2 PARTITION BY userid ORDER BY startdate
  USING
  Mode (NONOVERLAPPING)
  Pattern ('CEO.ENGR.OTHER*')
  Symbols (title1 ilike 'software eng%' AS ENGR,
    true AS OTHER,
    title1 ilike 'Chief Exec Officer' AS CEO)
  Result (accumulate(title1 OF ANY(ENGR,OTHER,CEO))
    AS job_transition_path)
) AS dt GROUP BY 1;
job_transition_path count
[Chief Exec Officer, Software Engineer, Software Engineer, Chief Exec Officer, Chief Exec Officer] 1

In the pattern, CEO matches the first row, ENGR matches the second row, and OTHER* matches the remaining rows:


How pattern 'CEO.ENGR.OTHER*' matches cells in title column in greedy pattern matching example of MLE function nPath

This query returns the following table:

SELECT job_transition_path, count(*) AS count1 FROM nPath@coprocessor (
  ON link2 PARTITION BY userid ORDER BY startdate
  USING
  Mode (NONOVERLAPPING)
  Pattern ('CEO.ENGR.OTHER*.CEO')
  Symbols (title1 ilike 'software eng%' AS ENGR,
    true AS OTHER,
    title1 ilike 'Chief Exec Officer' AS CEO)
  Result (accumulate(title1 of ANY(ENGR,OTHER,CEO))
    AS job_transition_path)
) AS dt GROUP BY 1;
job_transition_path count
[Chief Exec Officer, Software Engineer, Software Engineer, Chief Exec Officer, Chief Exec Officer] 1

In the pattern, CEO matches the first row, ENGR matches the second row, OTHER* matches the next two rows, and CEO matches the last row:


How pattern 'CEO.ENGR.OTHER*.CEO' matches cells in title column in greedy pattern matching example of MLE function nPath

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