NRQL math using SELECT

NRQL supports the use of basic and advanced mathematical operators within a SELECT clause. You can apply mathematical calculations on both individual attributes and also the results of aggregator functions.

Use basic math operators with SELECT

To use basic math functions in NRQL, include operators within the SELECT clause:

• Addition: +
• Subtraction: -
• Multiplication: *
• Division: /

Here are some examples:

SELECT duration-databaseDuration FROM Transaction

SELECT count(*)/uniqueCount(session) FROM PageView

SELECT average(duration-databaseDuration) FROM Transaction

Use advanced math operators with SELECT

NRQL also includes some advanced mathematical functions that can be used for complex calculations. This is helpful if you want to process data to display it more effectively in the UI, or make calculations on queried results in a single step.

abs

abs(n) returns the absolute value of n. For non-negative n it returns n, and for negative n it returns the positive number -n. For example abs(2) = 2, and abs(-4) = 4.

clamp_max, clamp_min

The clamping functions impose an upper or lower bound on values. For example, clamp_max(duration, 10) returns the duration, unless it exceeds 10, in which case 10 is returned. Similarly clamp_min(duration, 1) will not return any value lower than 1.

The following example query and chart show the result of clamping both min and max to keep the value in the range 96-98.

FROM SystemSample 
SELECT average(cpuPercent) AS 'raw',
clamp_min(clamp_max(average(cpuPercent), 98), 96) AS 'clamped'
TIMESERIES

exp

Computes the natural exponential function of the argument: exp(n) = pow(e, n).

Logarithmic functions: ln, log, log2, log10

These functions compute the logarithm of the argument for various bases.

• ln(n) computes the natural logarithm: the logarithm base e.
• log2(n) computes the logarithm base 2.
• log10(n) computes the logarithm base 10.
• log(n, b) allows logarithms to be computed with an arbitrary base b.
• All logarithms satisfy the identity: log(pow(b, n), b) = n.

Note that log(0) is undefined, for all bases. Be aware that if you take the logarithm of something that might be zero, you may end up getting no value back from your query.

pow

pow(n, m) computes n raised to the power m. For example, n * n * ... * n, with m copies of n.

Rounding functions: round, floor, ceil

These three functions force decimal numbers to one of the neighboring integers.

• floor(n) returns the closest integer less than or equal to n.
• ceil(n) (short for "ceiling") returns the closest integer greater than or equal to n.
• round(n) returns the closest integer to n in either direction.

sqrt

sqrt(n) returns the square root of n, that is, the number such that sqrt(n) * sqrt(n) = n.

Results with STRING or FLOAT

Here is how NRQL handles strings present in math calculations:

Examples:

• sum(1+STRING) = 0
• sum(1+MIXED) = skips records where MIXED is a string
• average(1+STRING) = 0
• average(1+MIXED) = skips records where MIXED is a string

NULL and zero both appear as 0 in the dashboard. To override NULL values with another numeric value, use the syntax:

SELECT average(purchasePrice OR 0)

This will replace NULL values with 0 or any number specified.