Analytics SAQL Reference

Overview
Quick Start
Examples
SAQL Basic Elements
SAQL Operators
SAQL Statements
SAQL Functions
Aggregate Functions
Date Functions
String Functions
Math Functions
Windowing Functions
coalesce()
Query Performance
PDF

Newer Version Available

This content describes an older version of this product. View Latest

Math Functions

To perform numeric operations in a SAQL query, use math functions.

You can use SAQL math functions in foreach statements and in the filter by clause after a foreach statement.

You can't use math functions in a group by clause or in an order by clause. You also can't use math functions in the filter by clause before a foreach statement, but you can use them after the foreach statement.

Functions

This is a list of SAQL math functions.

abs(n)

Returns the absolute number of n as a numeric value. n can be any real numeric value in the range of -1e308 <= n <= 1e308.
This example is valid:
1q = foreach q generate abs(pct_change) as pct_magnitude;
These examples are invalid:
1q = group q by abs(pct_change);
2q = order q by abs(pct_change);

ceil(n)

Returns the nearest integer of equal or greater value to n. n can be any real numeric value in the range of -1e308 <= n <= 1e308.
This example is valid:
1q = foreach q generate ceil(miles) as distance;
These examples are invalid:
1q = group q by ceil(miles);
2q = order q by ceil(miles);

floor(n)

Returns the nearest integer of equal or lesser value to n. n can be any real numeric value in the range of -1e308 <= n <= 1e308.
This example is valid:
1q = foreach q generate floor(miles) as distance;
These examples are invalid:
1q = group q by floor(miles);
2q = order q by floor(miles);

trunc(n[, m])

Returns the value of the numeric expression n truncated to m decimal places. m can be negative, in which case the function returns n truncated to -m places to the left of the decimal point. If m is omitted, it returns n truncated to the integer place. n can be any real numeric value in the range of -1e308 <= n <= 1e308. m can be an integer value between -15 and 15 inclusive.
This example is valid:
1q = foreach q generate trunc(Price, 2) as Price;
These examples are invalid:
1q = group q by trunc(Price, 2);
2q = order q by trunc(Price, 2);

round(n[, m])

Returns the value of n rounded to m decimal places. m can be negative, in which case the function returns n rounded to -m places to the left of the decimal point. If m is omitted, it returns n rounded to the nearest integer. For tie-breaking, it follows round half way from zero convention. n can be any real numeric value in the range of -1e308 <= n <= 1e308. m can be an integer value between -15 and 15, inclusive.
This example is valid:
1q = foreach q generate round(Price, 2) as Price;
These examples are invalid:
1q = group q by round(Price, 2);
2q = order q by round(Price, 2);

exp(n)

Returns the value of Euler's number e raised to the power of n, where e = 2.71828183… The smallest value for n that will not result in 0 is 3e-324. n can be any real numeric value in the range of -1e308 <= n <= 700.
These examples are valid:
1q = foreach q generate exp(value) as value;
2q = filter q by exp(value) < 5;
These examples are invalid:
1q = group q by exp(value);
2q = order q by exp(value);

log(m, n)

Returns the natural logarithm (base m) of a number n. The values m and n can be any positive, non-zero numeric value in the range 0 < m, n <= 1e308 and m ≠ 1.

The smallest number input allowed for m is >0, m!=1. The smallest number for m or n that will not produce 0 is log(10, 0.3e-323).

These examples are valid:
1q = foreach q generate log(10, Population) as Population;
2q = filter q by log(10, Population) < 15;
These examples are invalid:
1q = group q by log(10, Population);
2q = order q by log(10, Population);

power(m, n)

Returns m raised to the nth power. m, n can be any numeric value in the range of -1e308 <= m, n <= 1e308. Returns null if m = 0 and n < 0.

  • If m = 0, n must be a non-negative value.
  • If m < 0, n must be an integer value.
  • The result of power(m, n) must be within the range expressed by a float64 number.
These examples are valid:
1q = foreach q generate power(length, 2) as area, length;
2q = filter q by power(length, 2) > 10;
These examples are invalid:
1q = group q by power(length, 2);
2q = order q by power(length, 2);

sqrt(n)

Returns the square root of a number n. The value n can be any non-negative numeric value in the range of 0 <= n <= 1e308.
These examples are valid:
1q = foreach q generate sqrt(value) as value;
2q = filter q by sqrt(value) < 10;
These examples are invalid:
1q = group q by sqrt(value);
2q = order q by sqrt(value);