Geek asks and answers

Time Series Query in Postgress

I use Postgres to track time series data.

---------
|ts|value|
|--------|
|1 |5    |
|--------|
|2 |2    |
|--------|
|5 |10   |
----------

The value of the value field is "the number of events that occurred from the previous timestamp." For example, in 3-5 seconds there were 10 events, as shown in the value of ts 5.

Our user may ask something like: "Show me a linear chart of seconds 0-6, which has data at intervals of 4 seconds." So, we show two data points in seconds 0 and 4, each of which sums up the number of events to the next point. Therefore, a line chart will use this data:

---------
|ts|value|
|--------|
|0 |7    |
|--------|
|4 |10   |
----------

(since 5 + 2 = 7 and 10 = 10)

. (), () . ts integer ( ).

SELECT start+round((ts-start)/interval)*interval as ts1, sum(value)
FROM events
WHERE ts >= start AND ts <= end
GROUP BY ts1
  • , , ?
  • , ts , ?
  • , 10 , 3-5 ( ), 4 ( ), 3 ( )?

sqlfiddle.

+3
1

, , , , . , 0 - 10 1, 5. , .

, , - , .

, , , , . :

SELECT int4range(rstart, rstart+1) AS srange 
FROM generate_series(0,10,1) AS seq(rstart)

0 10 1. :

 srange
---------
 [0,1)
 [1,2)
 [2,3)
 [3,4)
 [4,5)
 [5,6)
 [6,7)
 [7,8)
 [8,9)
 [9,10)
 [10,11)
(11 rows)

, && ( ).

.

:

SELECT lower(srange) AS t,
    sum (CASE 
        -- when data range is fully contained in sample range
        WHEN drange <@ srange THEN value
        -- when data range and sample range overlap, calculate the ratio of the intersection
        -- and use that to apportion the value
        ELSE CAST (value AS DOUBLE PRECISION) * (upper(drange*srange) - lower(drange*srange)) / (upper(drange)-lower(drange))
    END) AS value
FROM (
    -- Generate the range to be plotted (the sample ranges).
    -- To change the start / end of the range, change the 1st 2 arguments
    -- of the generate_series. To change the step size change BOTH the 3rd
    -- argument and the amount added to rstart (they must be equal).
    SELECT int4range(rstart, rstart+1) AS srange FROM generate_series(0,10,1) AS seq(rstart)
) AS s
LEFT JOIN (
    -- Note the use of the lag window function so that for each row, we get
    -- a range from the previous timestamp up to the current timestamp
    SELECT int4range(coalesce(lag(ts) OVER (order by ts), 0), ts) AS drange, value FROM data
) AS d ON srange && drange
GROUP BY lower(srange)
ORDER BY lower(srange)

:

 t  |      value
----+------------------
  0 |                5
  1 |                2
  2 | 3.33333333333333
  3 | 3.33333333333333
  4 | 3.33333333333333
  5 |
  6 |
  7 |
  8 |
  9 |
 10 |
(11 rows)

, - ts , , , .

, . , , , . , , , .

.

Caveat Emptor: , , . - .

+2

More articles:


All Articles