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
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.