This example compares multiple time series to both a common template and each other. Each time series represents stock prices and the template represents a series of stock index prices.
Input
timeseriesid | timestamp1 | stockprice |
---|---|---|
1 | 0 | 24.2019 |
1 | 0.025063 | 27.8701 |
1 | 0.050125 | 31.4969 |
1 | 0.075188 | 35.083 |
1 | 0.100251 | 38.6286 |
1 | 0.125313 | 42.1343 |
1 | 0.150376 | 45.6005 |
1 | 0.175439 | 49.0276 |
1 | 0.200501 | 52.4162 |
1 | 0.225564 | 55.7666 |
1 | 0.250627 | 59.0792 |
... | ... | ... |
templateid | timestamp2 | index_price |
---|---|---|
1 | 0 | 0 |
1 | 0.025063 | 0 |
1 | 0.050125 | 0 |
1 | 0.075188 | 0 |
1 | 0.100251 | 0 |
1 | 0.125313 | 0 |
1 | 0.150376 | 0 |
1 | 0.175439 | 0 |
1 | 0.200501 | 0 |
1 | 0.225564 | 0 |
1 | 0.250627 | 0 |
... | ... | ... |
timeseriesid | templateid |
---|---|
1 | 1 |
1 | 2 |
1 | 3 |
2 | 1 |
2 | 2 |
2 | 3 |
3 | 1 |
3 | 2 |
3 | 3 |
4 | 1 |
4 | 2 |
4 | 3 |
SQL Call
SELECT * FROM DTW ( ON timeseriesdata AS InputTable PARTITION BY timeseriesid ORDER BY timestamp1 ON templatedata AS TemplateTable DIMENSION ORDER BY timestamp2 ON mappingdata AS MappingTable PARTITION BY timeseriesid USING TargetColumns ('stockprice', 'timestamp1') TemplateColumns ('indexprice', 'timestamp2') TimeSeriesId ('timeseriesid') TemplateId ('templateid') ) AS dt ORDER BY 1, 2;
Output
timeseries_id template_id warp_distance ------------- ----------- ------------------ 1 1 25163.9453125 1 2 7547.69140625 1 3 19577.634765625 2 1 132.66847229003906 2 2 1904.084716796875 2 3 71.78057861328125 3 1 351.6783142089844 3 2 3614.21240234375 3 3 75.77669525146484 4 1 4927.62451171875 4 2 914.2536010742188 4 3 16641.625
Plot and Interpretation of Results
The warping distance is an unnormalized measure of how dissimilar two time series are. The warp_distance column in the output table has the warping distance for all pairs in the mapping table; that is, for every timeseries_id and template_id number.
Template | Warp Distance |
---|---|
1 | 131.588 |
2 | 106.131 |
3 | ~540 |
Because the dissimilarity of two time series is not based on whether they are temporarily close (the time is stretched and the two time series that are offset by a constant time interval are effectively the same), input 3 is not very dissimilar to templates 1 and 3. However, input 4 has the largest warping distance measure from templates 1 and 3, as the curvature of the latter 2 is far from input 4. Time stretching brings input 4 closer to templates 1 and 3, but with a larger warping path (not output above) and therefore, a larger warping distance.
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.