可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
Is possible to query only for the last value (or n-th value) of the results of a query?
For example, in the query:
SELECT value FROM response_times WHERE time > now() - 1h limit 1000;
Is possible to get only the last value, i.e. the one more far ago in time (possibly the 1000-th element)?
Of course I can retrieve them all and then skip to the last one, but I don't want to waste bandwith this way.
回答1:
In the API, have a look at first/last to select the first or last record of a column.
You also have top/bottom to select more than one record
[edit] top/bottom seem to return higest/lowest values of the time frame
回答2:
If you are using InfluxDB 0.8 dont use FIRST() or LAST() if you have not GROUP BY because its very slow :(
So if you want to get the these Values you shoud use:
First Value:
SELECT * FROM <SERIES> GROUP BY * ORDER BY ASC LIMIT 1
Last Value:
SELECT * FROM <SERIES> GROUP BY * ORDER BY DESC LIMIT 1
Don't delete the GROUP BY * because then it could be possible that you get unexpected values then.
回答3:
SELECT last(value) FROM response_times WHERE time > now() - 1h;
That should return the last value of the column.
However, if you want you can split up the sequence in smaller pieces and select the last value. For instance:
SELECT last(value) FROM response_times WHERE time > now() - 1h GROUP BY time(60s);
It will split up the sequence in 60-second fragments and will pick up for each fragment the latest value.
回答4:
SELECT * FROM <SERIES> ORDER BY ASC LIMIT 1
回答5:
here is a concrete sample, suppose we have data:
> select *,tag from measurement
name: measurement
time field_1 tag_1
---- ------- ------
2019-05-15T03:07:52Z 100 A
2019-05-15T03:07:52Z 101 B
2019-05-15T03:09:52Z 100 A
2019-05-15T03:09:52Z 101 B
and you would like to take the last two columns(latest records per tag_1),
then you can do this:
> select last(field_1),* from measurement group by *
name: measurement
tags: tag_1=A
time last field_1
---- ---- -------
2019-05-15T03:09:52Z 100 100
name: measurement
tags: tag_1=B
time last field_1
---- ---- -------
2019-05-15T03:09:52Z 101 101
and also the visualization on grafana, it'll always show the last records per tag
回答6:
There is no bug or any issue about LIMIT. Yes there is one, but it's about SLIMIT (Series Limit). LIMIT can be safely used for getting first n records.
So in short, the syntax for LIMIT:
SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] LIMIT <N>
the syntax for SLIMIT:
SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] GROUP BY *[,time(<time_interval>)] [ORDER_BY_clause] SLIMIT <N>
You see that GROUP BY clause is [ optional ] in LIMIT.
For detailed explanation about usages of LIMIT and SLIMIT, here is version 1.5 document.
And here is the ongoing issue about SLIMIT.