Nested aggregation: make ordered computations in a single query

With nested aggregation, you can complete a NRQL query, then make additional computations using the results of that query. Nested aggregation provides NRQL capabilities similar to the class of SQL subqueries or subselects where the subquery is in the FROM clause of the outer query.

Tip

This feature is different from our subquery feature, which allows for subqueries in SELECT and WHERE clauses.

Answer complex questions with a single query

Nested aggregation can help you to answer questions like these without building multiple queries:

How can I count the requests per minute for my application, then get the maximum requests per minute for the last hour?
How can I compute the average CPU usage of all my servers or hosts, and list only the ones with usage over 90%?
From all my user sessions, how can I figure out what percentage bounced immediately?

For an example of how to use nested aggregation with an app's error rate query to get percentage data and more, watch this YouTube video (approx. 3:10 minutes).

Nested aggregation query structure and clauses

Every NRQL query must begin with a SELECT statement or a FROM clause. A nested aggregation query uses both a SELECT statement and a FROM clause and applies them to a whole query or queries contained within parentheses.

A complete and properly formatted nested aggregation query will look something like this:

SELECT function(attribute)
FROM (
  SELECT function(attribute)
  FROM dataType
  WHERE attribute
  TIMESERIES integer units
)

A few other details about query and clause behavior:

Nested queries can be more than two levels.
Both TIMESERIES and FACET can be applied to any part of the nested query, and don’t need to be identical at all levels.
SINCE, UNTIL, and COMPARE WITH clauses apply to the entire query and can only be used at the outermost level.

Nested aggregation query examples

Here are some examples of nested queries.

In this example, the inner query first counts the transactions for myApp for each of the last 60 minutes, then the outer query returns the highest 1-minute request rate.

Also, by giving a name to the result of the first query (as rpm), you can create a label for the value returned. Without adding the label, this query will return the value as count for use in the outer query.

SELECT max(rpm)
FROM (
  SELECT count(*) as rpm
  FROM Transaction
  WHERE appName = 'myApp'
  TIMESERIES 1 minute
)

In this example, the inner query calculates the average CPU usage for all hosts, then the outer query filters the results down to only hosts with CPU usage over a risky threshold (90%).

SELECT hostname, cpu
FROM (
  SELECT average(cpuPercent) as cpu
  FROM SystemSample
  FACET hostname
  TIMESERIES 1 minute
)
WHERE cpu > 90

In this example, the inner query calculates the number of PageView events in each user session, then the outer query computes what percentage of those sessions only had a single associated view.

SELECT percentage(count (*), WHERE sessionLength = 1)
FROM (
  SELECT count(*) as sessionLength
  FROM PageView
  FACET session
)

Nested aggregation: make ordered computations in a single query

Tip

Answer complex questions with a single query

Nested aggregation query structure and clauses

Nested aggregation query examples

Total transactions and highest one-minute response rate

Average CPU usage and CPU usage over 90%

Page views in each user session and number of sessions with only one page view