derivate without aggregation method

[torkelo]

Some questions regarding the derivative function (Which I get so many questions for and people having issues with on IRC on and github). Mostly the problems are caused by Grafana editor not using an aggregate function together with the derivative function. And partly because I think derivative function is buggy.

Question 1)

SELECT derivative("value") AS "value" FROM "logins.count" WHERE time > now() - 15m GROUP BY time(5m)

This query should fail but does not. It succeeds and returns 898 datapoints, so the group by time has not really done its job. It should complain when there is no aggregation method!

Question 2)
InfluxDB Docs:

This example outputs the rate of change per hour of field_key.
SELECT DERIVATIVE(field_key) FROM measurement ... GROUP BY time(1m)

This seems wrong.

You should change the syntax to make a difference between aggregation and transformation.

SELECT MEAN(value) TRANSFORM DERIVATIVE(1s) FROM measurement ... GROUP BY time(1m)

will make it much easier to read and understand (and build tooling for)

[rkuchan]

@torkelo,

Thank you for your query. What version are you using?

[pauldix]

@torkelo I think we recently updated it so that calls to derivative will throw an error if they have a group by time and don't have an aggregate inside. Will be in the 0.9.5 release.

The syntax you propose is interesting, I'm wondering what other people think about it.

[otoolep]

At first glance, I like what @torkelo has proposed.

[torkelo]

@rkuchan I was using 0.9.3

@pauldix that is good news, could not find any issue or PR for it. Maybe missed it.

So more on the syntax suggestion. It was just something that I came up with to make it more clear that there are aggregation functions (that the Group By require), and transformation functions (that act one or more values). It could make the queries more readable and understandable and also help make the distinction clear. Although it makes the query a little more verbose but that is acceptable I think.

But it also makes it easier for a query UI to have one separate selection for aggregation method and one or more for transformation functions. Part of the problem with so many struggling with this function is that it was in the same dropdown in Grafana as aggregation functions. Separating them conceptually in the language would make it easier to separate them in a query editor as well (although it could be done in query editor regardless but would be tricker).

I would love something like this

 SELECT 
   mean(value) -> derivate(1s) -> movingAverage(10) -> scale(100) -> alias('mvg_avg')
FROM measurement GROUP BY time(1m)

and extreme case, but this is much simpler syntax wise, and understanding / readable (to me), than:

 SELECT 
   movingAverage(derivate(mean(value), 1s, 10) * 100 AS 'mvg_avg'
FROM measurement GROUP BY time(1m)

[sebito91]

+1 without a doubt. The best example I've seen of this is from graphite-web (I'm sure @torkelo has experience):

http://graphite.readthedocs.org/en/latest/functions.html?highlight=movingaverage

You have the ability to completely nest functions within one another which provides incredible flexibility when parsing your data.

[torkelo]

I have started work on a big change to the query editor in Grafana so that it supports nested functions and function with parameters. Inspired by the ideas from Graphite editor.

I have added math expression and alias expression as functions but they do not of course nest the expression when they render but append the appropriate expression so the rendered InfluxDB query is still the same. This way I can unify some complex concepts in the query language with a single editor concept. Would love some feedback from influxdb devs.

[jwilder]

@torkelo I like the editor design you propose. It does seems more readable to have a series of transformation applied to each field in order vs nesting multiple functions. Not sure if we lose some needed capabilities though.

I also like the 2nd query syntax you presented as well. The -> might be cumbersome to type though. Maybe borrowing from unix world and using a pipe | would have the same effect?

Not sure if this is better or not but for comparison:

SELECT 
   mean(value) | derivate(1s) | movingAverage(10) | scale(100) | alias('mvg_avg')
FROM measurement GROUP BY time(1m)

[torkelo]

@jwilder
yea, pipe char is better :)

[torkelo]

I think this issue can be closed right? My original concert was that you could use derivative only with group by time without an error, which I think has been fixed.

[jwilder]

Fixed via #4292

[victorhooi]

I know this issue is closed - but the changes to query syntax - was that ever resolved?

As in, is there another ticket for the changes to query syntax that @torkelo and @jwilder were talking about?

[pauldix]

@torkelo I like your query suggestions. Although the pipe that @jwilder suggested is a nice refinement to it. Another option would be to use the chaining syntax that jQuery and D3 have made popular. We're taking this approach with another thing we're building. So it would be something like:

SELECT 
   mean(value).derivate(1s).movingAverage(10).scale(100).alias('mvg_avg')
FROM measurement
WHERE time > now() - 4h
GROUP BY time(1m)

-- or using the D3 style
SELECT 
   mean(value)
     .derivate(1s)
     .movingAverage(10)
     .scale(100)
     .alias('mvg_avg')
FROM measurement
WHERE time > now() - 4h
GROUP BY time(1m)

[beckettsean]

@victorhooi #4327