Pattern matching searches

You can search time series data for matches to a particular pattern of values. For example, after you identify a pattern of abnormal electricity usage that indicates an outage, you can search for matches to that pattern to find other outages. Pattern matching searches find matches to a pattern that you supply. Pattern matching searches do not discover patterns in time series data.

A pattern is a sequence of numeric values in a field within the TimeSeries subtype. You can specify a search pattern as a time range in a specific time series or as a list of numeric values. A match is a sequence of values from a target time series that conform to the search criteria. A matching pattern has the same number of values as the search pattern. A match is returned as a primary key value, a time range, and a similarity score.

You search for pattern matches by running the TSPatternMatch function. You specify the target time series instance, the time interval of the search, and how closely the data must match the specified pattern.

You can create a pattern matching search index on a time series instance to improve query performance. Run the TSCreatePatternIndex function for each time series instance that you want to index. You can control how precisely the data is indexed.

Suppose that you have the following TimeSeries data type, table, and data:

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)]");

The following table shows the time series data in the ts1 table.

Table 1. Time series data in the ts1 table

tstamp	value1
2011-01-01 00:00:00	1
2011-02-01 00:00:00	1
2011-03-01 00:00:00	55
2011-04-01 00:00:00	55
2011-05-01 00:00:00	55
2011-06-01 00:00:00	55
2011-07-01 00:00:00	1
2011-08-01 00:00:00	45
2011-91-01 00:00:00	45
2011-10-01 00:00:00	45
2011-11-01 00:00:00	45
2011-12-01 00:00:00	1

You identify an interesting pattern of (55),(55),(55),(55) and you want to find any matches to it in the value1 column. The sequence of values in the time range 2011-03-01 00:00:00 to 2011-06-01 00:00:00 exactly matches the pattern.