Am Montag, 13. Februar 2017 17:37:29 UTC+1 schrieb [email protected]: > Hey guys, > > I think I have a similar use case and I am experiencing the same problem. Let > me explain it a bit more then. > > Given following data: > > revenue,projectKey=foo,status=closed,currency=USD,id=5 amount=5100 > 1486477750926 > revenue,projectKey=foo,status=closed,currency=EUR,id=4 amount=4100 > 1486476850926 > revenue,projectKey=foo,status=closed,currency=EUR,id=3 amount=3100 > 1486475950926 > revenue,projectKey=foo,status=open,currency=USD,id=5 amount=5000 1486475050926 > revenue,projectKey=foo,status=open,currency=EUR,id=4 amount=4000 1486474150926 > revenue,projectKey=foo,status=open,currency=EUR,id=3 amount=3000 1486473250926 > revenue,projectKey=foo,status=closed,currency=EUR,id=2 amount=2000 > 1486472350926 > revenue,projectKey=foo,status=open,currency=EUR,id=1 amount=1000 1486471450926 > > > select * from revenue > name: revenue > time amount currency id projectKey status > ---- ------ -------- -- ---------- ------ > 2017-02-07T12:44:10.926Z 1000 EUR 1 foo open > 2017-02-07T12:59:10.926Z 2000 EUR 2 foo closed > 2017-02-07T13:14:10.926Z 3000 EUR 3 foo open > 2017-02-07T13:29:10.926Z 4000 EUR 4 foo open > 2017-02-07T13:44:10.926Z 5000 USD 5 foo open > 2017-02-07T13:59:10.926Z 3100 EUR 3 foo closed > 2017-02-07T14:14:10.926Z 4100 EUR 4 foo closed > 2017-02-07T14:29:10.926Z 5100 USD 5 foo closed > > I would like to e.g. get the total revenue for closed / open orders. > > In the example data I have 3 orders with a new data point where the status > changed. > For the sake of the example, the amount of those 3 "new" orders is "100" > more, so that it's easier to identify the correct numbers in the result. > > Since I'm interested only in the "last" entry of each order, the first thing > to do is to extract this data. > (For some reasons "last()" doesn't work as I would expect, I'm going to use > "top()" instead). > > > select top(amount, 1) as amount,currency,id,projectKey,status from revenue > > group by id > name: revenue > tags: id=1 > time amount currency id projectKey status > ---- ------ -------- -- ---------- ------ > 2017-02-07T12:44:10.926Z 1000 EUR 1 foo open > > name: revenue > tags: id=2 > time amount currency id projectKey status > ---- ------ -------- -- ---------- ------ > 2017-02-07T12:59:10.926Z 2000 EUR 2 foo closed > > name: revenue > tags: id=3 > time amount currency id projectKey status > ---- ------ -------- -- ---------- ------ > 2017-02-07T13:59:10.926Z 3100 EUR 3 foo closed > > name: revenue > tags: id=4 > time amount currency id projectKey status > ---- ------ -------- -- ---------- ------ > 2017-02-07T14:14:10.926Z 4100 EUR 4 foo closed > > name: revenue > tags: id=5 > time amount currency id projectKey status > ---- ------ -------- -- ---------- ------ > 2017-02-07T14:29:10.926Z 5100 USD 5 foo closed > > Before we go any further, let's try to get the SUM of all those orders > > > select sum(amount) from (select top(amount, 1) as > > amount,currency,id,projectKey,status from revenue group by id) > name: revenue > time sum > ---- --- > 1970-01-01T00:00:00Z 15300 > > Alright, seems good. > The next step is to filter out some of those orders (e.g. by projectKey, > currency, status, time, etc). > Let's simply try that by "currency" and "status". > > > select amount,currency,id,projectKey,status from (select top(amount, 1) as > > amount,currency,id,projectKey,status from revenue group by id) where > > projectKey = 'foo' and status = 'open' > name: revenue > time amount currency id projectKey status > ---- ------ -------- -- ---------- ------ > 2017-02-07T12:44:10.926Z 1000 EUR 1 foo open > > Ok, same for "closed" > > > select amount,currency,id,projectKey,status from (select top(amount, 1) as > > amount,currency,id,projectKey,status from revenue group by id) where > > projectKey = 'foo' and status = 'closed' > name: revenue > time amount currency id projectKey status > ---- ------ -------- -- ---------- ------ > 2017-02-07T12:59:10.926Z 2000 EUR 2 foo closed > 2017-02-07T13:59:10.926Z 3100 EUR 3 foo closed > 2017-02-07T14:14:10.926Z 4100 EUR 4 foo closed > 2017-02-07T14:29:10.926Z 5100 USD 5 foo closed > > Also fine. > Now we should just "sum()" the amount of that subquery result and we're good, > right? > > > select sum(amount) from (select amount,currency,id,projectKey,status from > > (select top(amount, 1) as amount,currency,id,projectKey,status from revenue > > group by id) where projectKey = 'foo' and status = 'closed') > (nothing) > > Nothing is returned. What the hell? > Before we were able to sum the results of the first subquery (without > filtering): > > > select sum(amount) from (select top(amount, 1) as > > amount,currency,id,projectKey,status from revenue group by id) > name: revenue > time sum > ---- --- > 1970-01-01T00:00:00Z 15300 > > But as soon as we add some filters with "where" nothing works anymore: > > > select sum(amount) from (select top(amount, 1) as > > amount,currency,id,projectKey,status from revenue group by id) where > > projectKey = 'foo' and status = 'closed' > (nothing) > > Also, if I don't use "sum()" (or any aggregation function), I do get results: > > > select * from (select top(amount, 1) as > > amount,currency,id,projectKey,status from revenue group by id) where > > projectKey = 'foo' and status = 'closed' > name: revenue > time amount currency currency_1 id id_1 projectKey > projectKey_1 status status_1 > ---- ------ -------- ---------- -- ---- ---------- > ------------ ------ -------- > 2017-02-07T12:59:10.926Z 2000 EUR EUR 2 2 foo foo > closed closed > 2017-02-07T13:59:10.926Z 3100 EUR EUR 3 3 foo foo > closed closed > 2017-02-07T14:14:10.926Z 4100 EUR EUR 4 4 foo foo > closed closed > 2017-02-07T14:29:10.926Z 5100 USD USD 5 5 foo foo > closed closed > > This is really confusing and I can't explain what is happening here. > Since functions can only be applied on field values, my naive guess is that > the subquery doesn't return e.g. "amount" as a value anymore. > > Is that even possible to do with influxdb? > Any help would be appreciated, thanks! > > Nicola > > > On Tuesday, January 31, 2017 at 5:57:13 PM UTC+1, [email protected] wrote: > > When I query > > `> select uc from (select uc from (select sum(usageCount) as uc from > > cidMetrics group by cid) where uc > 10000)` > > > > The result is: > > > > name: cidMetrics > > time uc > > ---- -- > > 0 6.3858232e+07 > > 0 9.576334e+06 > > 0 3.37877e+07 > > 0 20435 > > 0 3.231056e+06 > > 0 222637 > > 0 2.659245e+06 > > 0 1.5524067e+07 > > 0 107422 > > 0 5.835639e+06 > > 0 2.62457676e+08 > > 0 66189 > > 0 445911 > > 0 7.822122e+06 > > 0 39588 > > 0 134575 > > 0 45006 > > 0 1.0234932e+07 > > 0 8.09876e+06 > > 0 2.6232166e+07 > > 0 39912 > > 0 304978 > > 0 19287 > > 0 1.821506e+06 > > > > but, when I try to get the count of number of results of that query nothing > > is returned > > `> select count(uc) from (select uc from (select sum(usageCount) as uc from > > cidMetrics group by cid) where uc > 10000)`
Anyone out there with some deeper influxdb query knowledge how can add his 5 cents here? -- [image: commercetools logo] <http://www.commercetools.com> Amtsgericht München, HRB 161496 Geschäftsführer: Denis Werner, Dirk Hörig, Udo Rauch, Dr. Robert Zores www.commercetools.com <https://commercetools.com/de/events-internetworld?utm_source=Mail_DE&utm_medium=commercetools_Mailfooter&utm_term=Internetworld2017_Footer_DE&utm_content=Internetworld2017_Footer_DE&utm_campaign=Events> -- Remember to include the version number! --- You received this message because you are subscribed to the Google Groups "InfluxData" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/influxdb. To view this discussion on the web visit https://groups.google.com/d/msgid/influxdb/b44d1dfb-dd0d-4515-b5ad-9465dc94a1b4%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
