Influxdb: [feature request] RATE function

[cnelissen]

Are there any plans to implement a RATE function or similar functionality? What I need is to be able to calculate the rate for a certain event. For example:

Insert some event data every 1 minute:
clicks value=100 2015-09-02T22:40:00Z
clicks value=125 2015-09-02T22:41:00Z
clicks value=181 2015-09-02T22:42:00Z
clicks value=112 2015-09-02T22:43:00Z
clicks value=205 2015-09-02T22:44:00Z

Now I want to know clicks per second for every 5 minute interval for the last day:

SELECT RATE(SUM(value)) FROM clicks WHERE time > now() - 1d GROUP BY time(5m)

There were 723 clicks during this 5 minute period, which equates to a click per second rate of 2.41 (723 / 300 seconds)

I see there is a DERIVATIVE function, but that only tells us the difference between the two points, not the data rate.

[jwilder]

Derivative returns the rate of change between consecutive points. Try:

SELECT DERIVATIVE(SUM(value), 1s) FROM clicks WHERE time > now() - 1d GROUP BY time(5m) fill(0)

That calculates the total clicks in 5m buckets and then the rate of change between each bucket normalized to a 1s rate. It produces 2.41 clicks/second for your sample data.

[matejzero]

Moved my question to #4081

[cnelissen]

Any update on this?

There is a different use case here from DERIVATIVE... A RATE function would take the raw value in a given bucket and divide it by the time that the bucket contains to give you a rate, which has nothing to do with previous values, which is what DERIVATIVE is evaluating.

Hopefully this will demonstrate the difference:

Using DERIVATIVE:

2015-09-02T22:40:00Z   value=1000 (derivative: null,   rate: null)
2015-09-02T22:41:00Z   value=1250 (derivative: 250,    rate: 4.16/sec)
2015-09-02T22:42:00Z   value=1810 (derivative: 560,    rate: 9.30/sec)
2015-09-02T22:43:00Z   value=1120 (derivative: -690,   rate: -11.50/sec)
2015-09-02T22:44:00Z   value=2050 (derivative: 930,    rate: 15.50/sec)

Using RATE:

2015-09-02T22:40:00Z   value=1000 (rate: 16.66/sec)
2015-09-02T22:41:00Z   value=1250 (rate: 20.83/sec)
2015-09-02T22:42:00Z   value=1810 (rate: 30.16/sec)
2015-09-02T22:43:00Z   value=1120 (rate: 18.66/sec)
2015-09-02T22:44:00Z   value=2050 (rate: 34.16/sec)

Basically, we are trying to calculate the rate for the whole value, not the rate of change from previous...

[cnelissen]

For more background on the use case for the data, many systems have ephemeral counters that get cleared every time you query them, which is what this data would represent - the number of times a certain event has happened since the last time the counter was checked - which we then want to translate into a per second rate.

[beckettsean]

@cnelissen you could solve that problem for each specific query with the division operator:

SELECT value / 60 AS rate_per_sec FROM foo GROUP BY time(1m)
SELECT value / 300 AS rate_per_sec FROM foo GROUP BY time(5m)
SELECT value / 5 AS rate_per_min FROM foo GROUP BY time(5m)

[jsternberg]

I think @beckettsean is right that this would just be solved by using the division operator normally. I believe we support this with aggregates in the latest release so you can do SUM(value) / 60 too. If this doesn't work, please comment and I'll reopen the issue.

[Carbenium]

@jsternberg The solution offered by @beckettsean would be feature complete if the division operator would support time literals or there would be some kind of cast to seconds at least.
This would make integration into Grafana easier since their time interval variable only supports substitution as literals.

[Laugenbrezel]

I agree with @Carbenium, this would make life w/ Grafana way easier.
Also the fact that there is a direct connection between the calculation and the "GROUP BY" makes a good point for this RATE function, I think.

[Carbenium]

Any comments from the developers?

[kruegge]

Can we make a new feature request by @Carbenium

The solution offered by @beckettsean would be feature complete if the division operator would support time literals.
This would make integration into Grafana easier since their time interval variable only supports substitution as literals.

[jsternberg]

What's meant by this?

would be feature complete if the division operator would support time literals or there would be some kind of cast to seconds at least

Do you mean being able to do something like value / 5m and have the 5m converted to nanoseconds? Or maybe something like value / (5m / 1s) if you wanted that converted to seconds?

[kruegge]

@jsternberg

I can speak only for myself. I have in example a ratecount for requestsPerSecond and in aggregation of time like 5m i want also the requestsPerSecond in this grouped interval.
I save my data only every 1 minute.

for 1 minute aggregation currently i can use this:
SELECT sum(requestsPerSecond) / 1 FROM "measurement" WHERE time >= now() - 1h GROUP BY time(1m), host
or this
SELECT sum(requestsPerSecond) FROM "measurement" WHERE time >= now() - 1h GROUP BY time(1m), host

but for higher intervals like 5m i must use
SELECT sum(requestsPerSecond) / 5 FROM "measurement" WHERE time >= now() - 1h GROUP BY time(5m), host

for me i want to add time literals like this
SELECT sum(requestsPerSecond) / (5m / 1m) FROM "measurement" WHERE time >= now() - 1h GROUP BY time(5m), host
or this
SELECT sum(requestsPerSecond) / (5m : 1m) FROM "measurement" WHERE time >= now() - 1h GROUP BY time(5m), host

i don't know how the query builder interpret this, but what i want is to calculate the rate per second. In my measurements i have this ony only in 1minute aggregation available, so i can define the min aggregation format.

another example to understand:
When i save my measurements every 10s, then i can write this
SELECT sum(requestsPerSecond) / (5m / 10s) FROM "measurement" WHERE time >= now() - 1h GROUP BY time(1m), host

So you know what i mean or is this a useless feature for you?

[jsternberg]

I don't think it's useless. I'm just trying to think if that math makes sense or if it's going to lead to trouble in the future. I think that math would be consistent with how we currently treat durations and I think that math works when it's done in some other places within the query.

[Carbenium]

@jsternberg

Or maybe something like value / (5m / 1s) if you wanted that converted to seconds?

This would fit our needs.

[mathieulongtin]

+1. I was trying to do what scaleToSeconds does in Graphite.

http://graphite.readthedocs.io/en/latest/functions.html#graphite.render.functions.scaleToSeconds

[edio]

Just in case: grafana now provides $__interval_ms variable for this kind of arithmetic expressions.

[sciurus]

Although what currently exists works, it's pretty cumbersome to always have to write queries like SELECT sum("count") / $__interval_ms * 1000 in order to get rates that are displayed correctly no matter what timerange in Grafana you're looking at.