TSPatternMatch function

The TSPatternMatch function returns matches to a specified pattern of values.

Syntax

TSPatternMatch(
               ts            TimeSeries,
               table_name    LVARCHAR,
               primary_key   LVARCHAR,
               col_name      LVARCHAR
               begin_tstamp  DATETIME YEAR TO FRACTION(5),
               end_tstamp    DATETIME YEAR TO FRACTION(5),
               pattern       LIST(DOUBLE PRECISION NOT NULL),
               search_method INTEGER,
               similarity    DOUBLE PRECISION,
               unit_error    DOUBLE PRECISION)  
 returns LIST(SEARCHROW NOT NULL)

ts

The time series value for the specified primary key.

table_name

The name of the time series table. The table must contain a TimeSeries column and have a primary key.

primary_key

The primary key column name.

If the primary key consists of multiple columns, concatenate the column names. Include the table name qualifier before the column name and separate the table and column name combinations with a pipe character (|). For example, if the primary key consists of two columns, the format is:

'table_name.column_name1|table_name.column_name2'

col_name

The name of the column in the TimeSeries data type to search.

Must have a data type of SMALLFLOAT, FLOAT, INTEGER, SMALLINT, or BSON. For a BSON column, you can search individual fields within the BSON document that contain numeric data.

If you are searching a BSON field, include the field name after the subtype column name with dot notation. For example, if the subtype column is named value and the field that you want to search is named v1, set the col_name parameter to value.v1.

begin_stamp

The beginning point of the range to search. Can be NULL, which represents the first element in the time series.

end_stamp

The ending point of the range to search. Can be NULL, which represents the last element in the time series.

pattern

A list of double precision values that represent the pattern for which to search. Can be the result of the getPatternFromTS function.

search_method

The search method:

0 = Subsequence pattern match search. Evaluate all sequences of values.

1 = Whole pattern match search. Evaluate consecutive sequences of values.

similarity

A double precision number 0.0-1.0 that represents the percentage of values in a match that must be within the unit error. Default is 0.8 (80%).

unit_error

A positive double precision value that represents the limit of how much a matching value can differ from the corresponding value in the original pattern. Default is 0.1.

Usage

Run the TSPatternMatch function to find matches to a specific pattern of values in the specified time series and during the specified time range.

Returns

A list of matches in a LIST data type that contains a SEARCHROW data type value for each match. The SEARCHROW data type is a ROW data type with the following definition:

ROW(LVARCHAR,
    DATETIME YEAR TO FRACTION(5),
    DATETIME YEAR TO FRACTION(5),
    DOUBLE PRECISION)

The SEARCHROW data type fields have the following information:

The primary key value for the time series instance.
The begin point of the match.
The end point of the match.
The score of the match, as a double precision number 0.0-1.0 that represents the percentage of values in the matching sequence that are an exact match to the corresponding values in the search pattern.

Examples

The examples use the following TimeSeries row type definition, table, and data for two time series instances:

CREATE ROW TYPE myrow(tstamp datetime year to fraction(5), value1 real);

CREATE TABLE tsdata(id int primary key, ts1 timeseries(myrow));

INSERT INTO tsdata VALUES(1000, 
               "origin(2011-01-01 00:00:00.00000), calendar(ts_1month), 
                container(container), threshold(0), regular, 
                [(1),(1),(55),(55),(55),(55),(1),(45),(45),(45),(45),(1)]");

INSERT INTO tsdata VALUES(2000,
               "origin(2011-01-01 00:00:00.00000), calendar(ts_1month), 
                container(container), threshold(0), regular, 
                [(55),(55),(55),(55),(1),(1),(1),(1),(1),(1),(1),(1)]");

Example: Search for a pattern that is specified by the getPatternFromTS function

The following statement searches for a pattern that is specified by the getPatternFromTS function:

SELECT TSPatternMatch(tsdata.ts1, 'tsdata', '1000', 'value1', 
                      '2011-01-01 00:00:00.00000',
                      '2011-12-01 00:00:00.00000',
                      getPatternFromTS(B.ts1, '2011-01-01 00:00:00.00000', 
                                       '2011-04-01 00:00:00.00000', 'value1'),
                      0, 0.5, 0.5)
    FROM tsdata, tsdata as B 
    WHERE tsdata.id = 1000 and B.id=2000;

(expression)

LIST{ROW('1000','2011-03-01 00:00:00.00000','2011-06-01 00:00:00.00000',
         1.000000000000)}

Example: Search for a pattern that is specified by a list of values

The following statement runs the same search as the statement in the previous example, but the search pattern is specified by a list of values in a LIST data type:

SELECT TSPatternMatch(ts1, 'tsdata', '1000', 'value1', 
                      '2011-01-01 00:00:00.00000',
                      '2011-12-01 00:00:00.00000',
                      'LIST{55,55,55,55}'::LIST(DOUBLE PRECISION NOT NULL),
                      0, 0.5, 0.5)
   FROM tsdata 
   WHERE id = 1000;

(expression)

LIST{ROW('1000','2011-03-01 00:00:00.00000','2011-06-01 00:00:00.00000',
         1.000000000000)}