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 | 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:
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:
Download a zip file of all examples and a SQL script file that creates their input tables.