Query data with Riak TS

Andrei Zavada azavada at contractor.basho.com
Mon Nov 14 11:05:49 EST 2016


On Mon, Nov 14, 2016 at 5:44 PM, Jan Paulus <Paulus at next-kraftwerke.de>
wrote:

> 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.
>
>This​ seems to be a peculiar requirement that Riak TS cannot accommodate
(sorry for not reading your explanation carefully enough).  Indeed, the AVG
function operates on numeric values as isolated scalars, and has no
knowledge of how long each value has persisted.


>
> 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/09db96d6/attachment-0002.html>


More information about the riak-users mailing list