16.10 - Pattern Matching - Teradata Database

Teradata Analytic Functions

Teradata Database
November 2017
Programming Reference

Conceptually, nPath pattern matching proceeds like this: Starting from a row in a partition, the function tries to match the given pattern along the row sequence in the partition (ordered as specified in the ORDER BY clause).

If the function cannot match the pattern, it outputs nothing; otherwise, it continues to the next row. When the function finds a sequence of rows that match the pattern, it selects the largest set of rows that constitute the match and outputs a row based on this match.

For example, suppose that the pattern is 'A.B+' and the rows that constitute the match start at a row t1 and end at row t4. Suppose that t1 matches A and each of t2,t3, and t4 matches B. When the matching is complete, A represents t1 and B represents t2, t3, and t4. Using the rows represented by A and B, the function evaluates the Result argument (typically applying an aggregate function to each symbol in the pattern), outputs one row with the result values, and proceeds to search for the next pattern match.

Before running nPath on a large data set, create a small data set that includes the pattern that you want to find. Test your pattern on the small data set, refine the pattern until nPath gives the desired output, and then using the refined pattern for the large data set.