FrequentPaths Example 7: Use with nPath to Select Sequences - Teradata Vantage

This example uses nPath and FrequentPaths to select sequences.

Input

• InputTable: nPath_output, which the example creates by inputting the following table, sequence_table, to the nPath function

The following statement creates nPath_output, populating it with sequences that start with "A" and end with "C", and using the Accumulate argument to output the full sequence.

CREATE VIEW nPath_output AS (
  SELECT * FROM nPath (
    ON sequence_table PARTITION BY id ORDER BY datestamp
    USING
    Pattern ('itemA.itemAny*.itemC')
    Symbols (item='A' AS itemA, item='C' AS itemC, TRUE AS itemAny)
    "Result" (FIRST(id OF itemA) AS id,
    Accumulate (item OF ANY(itemA, itemAny, itemC)) AS path)
    "Mode" (NONOVERLAPPING)
  ) AS dt
);

This query returns the following table:

SELECT * FROM nPath_output ORDER BY id;

SQL Call

The FrequentPaths function outputs the sequences that start start with 'A' and end with 'C'.

SELECT * FROM FrequentPaths (
  ON nPath_output AS InputTable
  OUT TABLE OutputTable (output7)
  USING
  PartitionColumns ('id')
  PathColumn ('path')
  MinSupport (2)
) AS dt;

Output

Finished. Totally 3 patterns were found.

This query returns the following table:

SELECT * FROM output7 ORDER BY length, pattern;