1.0 - 8.00 - nPath Greedy Pattern Matching - 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)

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 title 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(title 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:



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: