Jan, What you want to do to calculate the "average" is not supported in the subset of SQL available currently through Riak TS. However, it is an easy operation with a small script using one of the supported client libraries.
I personally use the Python client library for all the demonstrations etc. that I create and your calculation would be a very simple operation with a results set from Riak TS which is returned as a DataFrame object which through the Python Pandas library offers a number of one-line methods to calculate "averages". Regards Stephen On 14 November 2016 at 16:05, Andrei Zavada <azav...@contractor.basho.com> wrote: > > > On Mon, Nov 14, 2016 at 5:44 PM, Jan Paulus <pau...@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:azav...@contractor.basho.com] >> *Gesendet:* Montag, 14. November 2016 16:19 >> *An:* Jan Paulus >> *Cc:* riak-users@lists.basho.com >> *Betreff:* Re: Query data with Riak TS >> >> >> >> Hello Jan, >> >> >> >> Replying 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 >> >> >> > > > _______________________________________________ > riak-users mailing list > riak-users@lists.basho.com > http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com > > -- { "name" : "Stephen Etheridge", "title" : "Solution Architect, EMEA", "Organisation" : "Basho Technologies, Inc", "Telephone" : "07814 406662", "email" : "mailto:setheri...@basho.com";, "github" : "http://github.com/datalemming";, "twitter" : "@datalemming"}
_______________________________________________ riak-users mailing list riak-users@lists.basho.com http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
