Geek asks and answers

Reduce / Simplify Time Series Data

I have time series data that I am looking to simplify (reduce the number of points by having a graph that supports the same shape). For example, if I had this dataset:

Time: 1, Value: 5
Time: 6, Value: 5
Time: 11, Value: 5.1
Time: 12, Value: 5
Time: 20, Value: 5.2
Time: 22, Value: 6
Time: 23, Value: 10

A simplified version with a tolerance of 0.5 will look something like this:

Time: 1, Value: 5
Time: 20, Value: 5.2
Time: 22, Value: 6
Time: 23, Value: 10

I know the Douglas-Puker algorithm for GIS data, but I'm not sure how to apply it to time series data, since there are different units in the axes. It would be great if I could do all this in a database.

+3
source share
2 answers

I would not know about the built-in function for this. This request can complete the task:

WITH x AS (
    SELECT t, val
          ,@(lead(val) OVER w - val) AS delta1
          ,@(lag(val)  OVER w - val) AS delta2
    FROM   tbl
    WINDOW w AS (ORDER BY t)
    ORDER  BY t
    )
SELECT t, val
FROM   x
WHERE  delta1 > 0.2
   OR  delta2 > 0.2
   OR  delta1 IS NULL
   OR  delta2 IS NULL;

lead() lag() @ CTE ( ).

, 0.2 ( ).

- , delta1 delta2 NULL ( / ). , NULL- SELECT.

.

, :

WITH x AS (
    SELECT t, val
          ,@(lead(val) OVER w + lag(val) OVER w - 2*val) AS deviate
    FROM   tbl
    WINDOW w AS (ORDER BY t)
    ORDER  BY t
    )
SELECT t, val, deviate
FROM   x
WHERE  deviate > 0.2
   OR  deviate IS NULL;

. Time: 12, Value: 5 , . ( .)

+1

Ramer Douglas Peucker - .

+1

More articles:


All Articles