AW: Query data with Riak TS

Jan Paulus Paulus at next-kraftwerke.de
Mon Nov 14 10:44:10 EST 2016


Hello Andrei,

thanks for the answer. It is good news that 1.5 will get the ODER BY and LIMIT functionality.
Unfortunately the current AVG implementation is not exactly what we are looking for. The current implementation will sum up all points in the time range and will it divide by the number of points. Currently Riak TS return 4.5 for my example. We are looking for an average which takes into account how long a value was persistent.

Kind Regards,
Jan Paulus

Von: Andrei Zavada [mailto:azavada at contractor.basho.com]
Gesendet: Montag, 14. November 2016 16:19
An: Jan Paulus
Cc: riak-users at lists.basho.com
Betreff: Re: Query data with Riak TS

Hello Jan,

R​eplying to your questions inline:​​
​
 Hi,
we are testing Riak TS for our Application right now. I have a couple of question how to query the data. We are measuring electric power which comes in in odd time intervals.

1. Is it possible to query the value which has been received bevor at a given time? In other words: Get the current / last value of a series.

In Riak TS 1.4 (the latest release), you still have to specify the exact range ​in the WHERE clause.  Because of that, if you are only interested in a single record that was added last before a certain time, you have to do some guesswork to arrive at the suitable boundaries.  Depending on the rate of data ingress, the selection returned may contain one, a few, too many, or no results at all.  One workaround is to issue a sequence of SELECTs, starting from a small range, with the lower boundary of the range progressively moved back until you have the last record included (and hopefully not many previous records which you are going to discard anyway).

In 1.5, we will support ORDER BY and LIMIT clauses which will allow a much simpler solution, e.g.:

 SELECT value FROM table
 WHERE time > 0 AND time < $now
 ORDER BY value DESC LIMIT 1

2. Is it possible to query the average in respect to time?
For instance you have such a measurement reading:
time    | value
-----------------
09:31   |  4
10:02   |  6
10:05   |  3
And you want to query the average from 10:00 to 10:10. I would expect a value of 4.1
4 * 20% + 6 * 30% + 3 * 50% = 4.1

​It is definitely possible:​

 ⁠⁠⁠⁠SELECT AVG(val
​ue​
) FROM
​table
​
 WHERE time >
​=​
​'​2016-11-22
10
​:00'​
AND time <
​'2016-11-22 ​
​10:10'​

​Note that you can specify timestamp values in ISO8601 format: http://docs.basho.com/riak/ts/1.4.0/using/timerepresentations

​
3. Is it possible to query 15 minutes average values for the last day? I would expect 96 values with the average as described in question 2.

No, you will have to issue 96 separate queries, each similar to the one in (2) but with a different 15-min range.​


Thanks a lot.
Jan Paulus

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.basho.com/pipermail/riak-users_lists.basho.com/attachments/20161114/efeade38/attachment-0002.html>


More information about the riak-users mailing list