Relic Solution: Using nested aggregation to count up to 2,000 unique values with precision

Here’s a quick tip.

The uniqueCount() function is a great way to efficiently return the number of unique values of a certain attribute, although the results are approximate when querying beyond 256 values.

With the advent of nested aggregation, however, it’s possible to write an outer query that counts the number of facets returned by an inner query. Because facets represent the unique values of an attribute, you can use this method to precisely count the number of unique values up to the current facet limit (2,000).

Here’s how that works. Let’s say you have somewhere between 256 and 2,000 applications, and you require a precise count of those applications. In order to return that count, you can write a query like so:

FROM
(FROM Transaction SELECT count(*) FACET appName LIMIT MAX)
SELECT count(appName)

The aggregator function on the inner query doesn’t really matter — we just want a faceted query that returns the maximum number of facets. In this case, we’re returning a count of Transaction events grouped by appName values.

The outer query, meanwhile, counts the number of appName values returned by the inner query. If your number of apps is less than or equal to the current facet limit, you will get a precise count of the applications running in your environment.

For other types of data, here’s a template for how this process could look:

FROM
(FROM {Data type} SELECT count(*) FACET {Attribute} LIMIT MAX)
SELECT count({Attribute})

Enjoy!!

3 Likes