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, stephe...@augur.io 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)`
--
[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
--
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 influxdb+unsubscr...@googlegroups.com.
To post to this group, send email to influxdb@googlegroups.com.
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/c83df3f7-1df4-4307-9504-3206117f2a21%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
