InfluxDB select top n results from a group by tag

Question

I have a data set of devices and the number of (un)instalmments of my app that are done daily.

A sample data would be:

 time | device_name | daily_installs | daily_uninstall
  t1  |   device1   |       0        |       1
  t1  |   device2   |       2        |       0
  t2  |   device2   |       2        |       0
  t2  |   device3   |       12       |       0

I can group them by device_name and get the total of install that I have by month (or any other range) for example.

But the amount of device is huge, hence I would like to filter only the top 10.

How can I achieve that using InfluxDB?

Answer 1

The answer depends on which version of InfluxDB you're using.

Version 1.1-

Step 1

Select the monthly counts into a new measurement

SELECT count(daily_uninstall) as monthly_uninstall
INTO newmeasurement 
FROM mymeasurement
WHERE time > now() - 4w
GROUP BY device_name

Step 2

Select the top 10 results from the new measurement

SELECT top(monthly_uninstall, 10), device_name
FROM newmeasurement

Version 1.2+

In versions 1.2+ of InfluxDB you'll be able to do this in a single step using subqueries.

SELECT top(monthly_uninstalls,10), device_name
FROM (SELECT count(daily_uninstall) as monthly_uninstall 
      FROM mymeasurement 
      WHERE time > now() - 4w
      GROUP BY device_name)

Answer 2

Version 1.3.4

SELECT top(monthly_uninstalls,device_name,10) 
FROM (SELECT count(daily_uninstall) as monthly_uninstall 
      FROM mymeasurement 
      WHERE time > now() - 4w 
      GROUP BY device_name)

Please note that the syntax is "top("field_name", "tag", "topN") from ...."