Use nested aggregation to make additional computations

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.

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?

Nested aggregation query structure

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 looks 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 have more than two levels.
• You can apply both TIMESERIES and FACET to any part of the nested query. They don't have to be identical at all levels.
• SINCE, UNTIL, and COMPARE WITH clauses apply to the entire query, and you can only use them at the outermost level.

Nested aggregation query examples

Here are some examples of nested queries:

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

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

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

SELECT count(avgDurations)
FROM (
  SELECT average(duration) AS avgDurations
  FROM Transaction
  FACET entity.guid
  LIMIT 50
)
WHERE avgDurations > 1

SELECT percentile(avgMaxWrites, 50, 75, 99)
FROM (
  SELECT max(avgWrites) AS avgMaxWrites
  FROM (
    SELECT average(diskWritesPerSecond) as avgWrites
    FROM SystemSample
    FACET hostname
    TIMESERIES 1 hour
    LIMIT 50
  )
  FACET hostname
  LIMIT 50
)
SINCE 1 day ago