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:
| 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 |
The following query returns the following table:
SELECT job_transition_path, count(*) AS count FROM nPath (
ON link2 PARTITION BY userid ORDER BY startdate
Mode (NONOVERLAPPING)
Pattern ('CEO.ENGR.OTHER*')
Symbols (title ilike 'software eng%' AS ENGR,
true AS OTHER,
title ilike 'Chief Exec Officer' AS CEO)
Result (accumulate(title OF ANY(ENGR,OTHER,CEO))
AS job_transition_path)
) GROUP BY 1 ORDER BY 2 DESC;
| 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 job_transition_path, count(*) AS count FROM nPath (
ON link2 PARTITION BY userid ORDER BY startdate
Mode (NONOVERLAPPING)
Pattern ('CEO.ENGR.OTHER*.CEO')
Symbols (title ilike 'software eng%' AS ENGR,
true AS OTHER,
title ilike 'Chief Exec Officer' AS CEO)
Result (accumulate(title of ANY(ENGR,OTHER,CEO))
AS job_transition_path)
) GROUP BY 1 ORDER BY 2 DESC;
| 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: