Greedy Pattern Matching - Teradata® Database

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:

nPath Greedy Pattern Matching Example Input Table link2
userid	jobtitle	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

The following query returns the following table:

SELECT dt.job_transition_path, count(*) AS count FROM NPATH (
   ON link2 PARTITION BY userid ORDER BY startdate
   USING
   MODE (NONOVERLAPPING)
   Pattern ('CEO.ENGR.OTHER*')
   Symbols (jobtitle like 'software eng%' AS ENGR,
     true AS OTHER,
     jobtitle like 'Chief Exec Officer' AS CEO)
   Result (accumulate(jobtitle OF ANY(ENGR,OTHER,CEO))
     AS job_transition_path)
) as dt GROUP BY 1 ORDER BY 2 DESC;

nPath Greedy Pattern Matching Example 1 Output Table
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:

The following query returns the following table:

SELECT dt.job_transition_path, count(*) AS count FROM NPATH (
   ON link2 PARTITION BY userid ORDER BY startdate
   USING
   MODE (NONOVERLAPPING)
   Pattern ('CEO.ENGR.OTHER*.CEO')
   Symbols (jobtitle like 'software eng%' AS ENGR,
     true AS OTHER,
     jobtitle like 'Chief Exec Officer' AS CEO)
   Result (accumulate(jobtitle of ANY(ENGR,OTHER,CEO))
     AS job_transition_path)
) as dt GROUP BY 1 ORDER BY 2 DESC;

nPath Greedy Pattern Matching Example 2 Output Table
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: