NRQL: Segment your data into buckets

Using NRQL, you can split your query results into buckets that cover certain ranges using the buckets function.

Why use bucketed results?

You can use the FACET buckets() function to group and categorize data into buckets based on specified ranges, such as for creating histograms or frequency distributions. Use it in combination with aggregating functions like count() or sum() to analyze data distribution across different ranges.

Here are some use cases:

• Analyzing response times: You can use FACET buckets() to group response times into ranges (for example, 0-100ms, 100-200ms, 200-300ms) and calculate the count or average response time within each range. This allows you to understand the distribution of response times and identify performance issues.
• Examining request sizes: By using FACET buckets() on request sizes, you can categorize the size of requests into different buckets (for example, 0-1KB, 1-5KB, 5-10KB) and analyze the frequency of requests falling within each bucket. This can help you identify patterns and optimize resource allocation.
• Monitoring error rates: You can use FACET buckets() on error codes or error rates to group them into specific ranges. For example, you could create buckets for different HTTP error codes (like 400, 500, and others) or error rate ranges (for example, 0-1%, or 1-5%). This allows you to track your error distribution and identify areas requiring attention.
• Tracking user engagement: If you have a metric related to user engagement, such as session durations or page views, you can use FACET buckets() to group them into time intervals. This lets you analyze how users engage with your application or website across different time ranges.

Create bucketed NRQL query

To return bucketed results, use the FACET buckets() clause in a NRQL query. A bucketed query has this structure:

SELECT FUNCTION(ATTRIBUTE) 
    FROM DATA_TYPE 
    FACET buckets(ATTRIBUTE, CEILING_VALUE, NUMBER_OF_BUCKETS)

You can use bucketed NRQL queries with any attribute stored as a numerical value in the New Relic database.

Bucket query example

1. Create a NRQL statement with a SELECT statement for an attribute; for example, SELECT average(duration).
2. Add a FACET clause that facets on buckets(). For example buckets(duration, 40, 10). You can also bucket on a different attribute than the one in the SELECT statement. For example, to show the average duration for database calls: buckets(databaseCallCount, 40, 10).

This query computes the average duration for a specific page path across 10 buckets, with an upper limit of 40. All values above our upper limit of 40 are grouped in the last bucket, >=36.0.

SELECT average(duration) FROM PageView WHERE pageUrl LIKE 'http://webportal.telco%' SINCE 1 week ago FACET buckets(duration, 40, 10)

This query returns these results: