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

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.10
1.1
Published
October 2019
Language
English (United States)
Last Update
2019-12-31
dita:mapPath
ima1540829771750.ditamap
dita:ditavalPath
jsj1481748799576.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
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
sequence_table
id datestamp item
1 2004-03-17 16:35:00 A
1 2004-03-17 16:38:00 B
1 2004-03-17 16:42:00 C
2 2004-03-18 01:16:00 B
2 2004-03-18 01:18:00 C
2 2004-03-18 01:20:00 D
3 2004-03-19 08:33:00 A
3 2004-03-19 08:36:00 D
3 2004-03-19 08:38:00 C

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

CREATE MULTISET TABLE nPath_output AS (
  SELECT * FROM nPath@coprocessor (
    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 path1)
    Mode (NONOVERLAPPING)
) AS dt
) WITH DATA;
SELECT * FROM nPath_output ORDER BY id;
 id path1     
 -- --------- 
  1 [a, b, c]
  3 [a, d, c]

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 ('path1')
  MinSupport (2)
) AS dt;

Output

 message                          
 -------------------------------- 
 Finished. 3 patterns were found.

SELECT CAST(pattern AS varchar(10)), support, length FROM output7 ORDER BY 3 DESC, 2 DESC;

 pattern support length 
 ------- ------- ------ 
 a;c           2      2
 a             2      1
 c             2      1

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.