[feature request] selectors (e.g. min, max, first, last) should have equivalents to return the actual point

[pauldix]

Right now the min, max, first, and last aggregate functions return values with timestamps set at the time bucket. For instance if you do

SELECT max(value) FROM cpu
WHERE region = 'uswest' and time > now() - 1h
GROUP BY time(10m)

All you'd get out of that query is 10 data points with a timestamp on 10m increments and whatever the value is. What people actually want to know is what was the raw point that the max value corresponds to.

That is, what timestamp, and what other tag information is there. Ideally, that other information would be represented as columns in the result. So I'm thinking of something like:

SELECT maxPoint(value, host) FROM cpu
WHERE region = 'uswest' and time > now() - 1h
GROUP BY time(10m)

The idea is that the maxPointWithTags function would take one or more arguments. The first argument is the field that is used for the max calculations. The following n arguments are the tags and fields that we'd like returned in the result.

Ideally, we'd have this Point function for all of those other aggregates where it makes sense like min, max, first, and last since each of those can be mapped to a single data point.

[dgnorton]

Shouldn't SELECT maxPoint(value, host) FROM cpu be SELECT maxPoint(value), host FROM cpu?

[pauldix]

@dgnorton yeah, makes more sense

[pauldix]

This can actually just be changed to be something like this:

SELECT max(value), time, host FROM CPU

Then in the return we'd end up getting the time of the actual point and the host.

[srikara]

+1

[pauldix]

Issue #3407 has more detail on which combinations are valid and which aren't.

[jasonhancock]

+1

[tslater]

+1. What i need is to do a group by tag and get the first and last times they were recorded.

[beckettsean]

@corylanou this is listed in the 0.9.5 CHANGELOG as a fixed bug, but the issue is still open and I don't see a recent PR referenced here. What's the status?

[corylanou]

@beckettsean this was completed in #4202

[skapin]

Hi, I still get a bug with LAST(*) on
InfluxDB shell version: 1.2.2
-> I get the CORRECT value, BUT, the timestamp shown is incorrect.

curl -G ***** -data-urlencode "q=SELECT last(*) FROM motion where time > (now() - 30m)"

{"results":[{"statement_id":0,"series":[{"name":"motion","columns":["time","last_Head","last_Head_sum","last_KneeLeft","last_KneeLeft_sum","last_KneeRight","last_KneeRight_sum","last_SpineBase","last_SpineBase_sum"],"values":[["2017-04-10T07:29:16.415987682Z",0.0803255330398,698.599190639,0.050377132033,727.684313406,0.0238532125401,721.480931178,0.0354184489207,659.068101473]]}]}]}


$ curl -G **** --data-urlencode "q=SELECT * FROM motion where time > (now() - 30m) ORDER BY DESC LIMIT 1"

{"results":[{"statement_id":0,"series":[{"name":"motion","columns":["time","Head","Head_sum","KneeLeft","KneeLeft_sum","KneeRight","KneeRight_sum","SpineBase","SpineBase_sum","session_id"],"values":[["2017-04-10T07:56:44.282417152Z",0.0803255330398,698.599190639,0.050377132033,727.684313406,0.0238532125401,721.480931178,0.0354184489207,659.068101473,"42w0b0uww3y"]]}]}]}

Same value, but bad timestamp :) (same error if add a group by)

[corylanou]

@jsternberg

[jsternberg]

@skapin can you open a new issue and @mention me on it? I don't want to add onto old issues with something new.

[lu4]

@jsternberg A link to this new issue would be helpful here...