[feature request] DOT aggregate functionality or arithmetic inside of aggregates

[amancevice]

It would be nice if InfluxDB offered a dot-product functionality, specifically for continuous queries. Something like:

SELECT DOT(valueA, valueB) FROM my_measurement WHERE time>now()-1h AND time<now()

[hillar]

Do you mean
"dot-product functionality" === if vector valueA is perpendicular with vector valueB, then the dot product is equal to zero

R> a <- c(1,2,3,4,5) 
R> b <- c(2,4,6,8,10) 
R> dot <- acos( sum(a*b,na.rm=TRUE) / ( sqrt(sum(a * a,na.rm=TRUE)) * sqrt(sum(b * b,na.rm=TRUE)) ) ) 
R> dot
[1] 0

[amancevice]

Yes that is correct, although I admit I don't really follow your example in R. I'm looking for something similar to numpy.dot() for Python. So,

In [1]: import numpy

In [2]: a = 1, 2, 3, 4, 5

In [3]: b = 2, 4, 6, 8, 10

In [4]: dot = numpy.dot(a, b)

In [5]: dot
Out[5]: 110

[desa]

@amancevice both valueA and valueB are scalars. InfluxDB doesn't allow you to store vectors or arrays.

[amancevice]

@mjdesa Apologies I must not be explaining myself. Say I have a query that looks like this:

SELECT DOT(valueA, valueB) AS dot FROM my_measurement GROUP BY time(1d)

I would expect InfluxDB to select valueA and valueB from the measurement, group them into 1d groups, and apply the aggregation DOT() on each group where the inputs are the columns, valueA and valueB.

So, if one of the groups in my series looked like this:

time                valueA valueB
1357088400000000000 1      2
1357088400000000000 2      4
1357088460000000000 3      6
1357088520000000000 4      8
1357088520000000000 5      10

I would expect the aggregate row to look like this:

time                dot
1357084800000000000 110

Does that make sense? Perhaps there is already a way to get this result but I thought including a DOT() function would be useful enough to include on its own.

[desa]

@amancevice ah I see what you're saying.

Would the following query work?

SELECT sum(valueA * valueB) AS dot FROM my_measurement GROUP BY time(1d)

[amancevice]

I get the following error trying SUM(valueA * valueB):

ERR: error parsing query: expected field argument in sum()

[desa]

Ah thats a problem. Looks like we don't support arithmetic in aggregate functions.

[amancevice]

Great, thanks for opening the feature request!

[amancevice]

Any idea when this might make it into a release? The ability to do arithmetic in queries -- specifically I'm thinking of continuous queries -- is something of a deal-breaker for using InfluxDB in production.

[desa]

@amancevice at the moment we're rewriting our query engine which will serve as the foundation for building out this sort of functionality. Once the query engine has been rewritten, we'd gladly accept a PR for the functionality you're looking for.

[amancevice]

Sounds good

[beckettsean]

As mentioned in my post to the mailing list we are experimenting with simplifying our open GitHub Issues. This feature request has been rolled into an aggregate issue for all function requests, so that we can close this issue until we are ready to work on it.

You may continue to make comments here. Closing the issue does not mean we are rejecting this idea.

[desa]

@pauldix