This topic explains the basic syntax for mathematical functions and provides examples.
The following table lists the mathematical functions supported by Simple Log Service.
-
The following operators are supported:
+-*/%
By default, the / operator discards the decimal part of the result. To retain the decimal part, use an operation such as
x*1.0/y. -
In a Log Service analytic statement, strings must be enclosed in single quotation marks (''). Log Service interprets unquoted text or text enclosed in double quotation marks ("") as a field name or column name. For example, 'status' represents the string 'status', while status or "status" represents the log field status.
|
Function |
Syntax |
Description |
SQL |
SPL |
|
abs(x) |
Returns the absolute value of x. |
√ |
√ |
|
|
acos(x) |
Returns the arc cosine of x. |
√ |
√ |
|
|
asin(x) |
Returns the arc sine of x. |
√ |
√ |
|
|
atan(x) |
Returns the arc tangent of x. |
√ |
√ |
|
|
atan2(x, y) |
Returns the arc tangent of x divided by y. |
√ |
√ |
|
|
cbrt(x) |
Returns the cube root of x. |
√ |
√ |
|
|
ceil(x) |
Rounds x up to the nearest integer. This function is an alias for the ceiling function. |
√ |
√ |
|
|
ceiling(x) |
Rounds x up to the nearest integer. |
√ |
√ |
|
|
cos(x) |
Returns the cosine of x. |
√ |
√ |
|
|
cosh(x) |
Returns the hyperbolic cosine of x. |
√ |
√ |
|
|
cosine_similarity(x, y) |
Returns the cosine similarity between x and y. |
√ |
× |
|
|
degrees(x) |
Converts an angle from radians to degrees. |
√ |
√ |
|
|
e() |
Returns the value of e, the base of the natural logarithm. |
√ |
√ |
|
|
exp(x) |
Raises e to the power of x. |
√ |
× |
|
|
floor(x) |
Rounds x down to the nearest integer. |
√ |
√ |
|
|
from_base(x, y) |
Converts the base-y string x to a number. |
√ |
√ |
|
|
ln(x) |
Returns the natural logarithm of x. |
√ |
√ |
|
|
infinity() |
Returns a value representing positive infinity. |
√ |
√ |
|
|
is_nan(x) |
Checks whether x is Not a Number (NaN). |
√ |
√ |
|
|
log2(x) |
Returns the base-2 logarithm of x. |
√ |
√ |
|
|
log10(x) |
Returns the base-10 logarithm of x. |
√ |
√ |
|
|
log(x, y) |
Returns the base-y logarithm of x. |
√ |
× |
|
|
mod(x, y) |
Returns the remainder of x divided by y. |
√ |
√ |
|
|
nan() |
Returns a NaN value. |
√ |
√ |
|
|
pi() |
Returns the value of π to 15 decimal places. |
√ |
√ |
|
|
pow(x, y) |
Raises x to the power of y. This function is an alias for the power function. |
√ |
√ |
|
|
power(x, y) |
Raises x to the power of y. |
√ |
√ |
|
|
radians(x) |
Converts an angle from degrees to radians. |
√ |
√ |
|
|
rand() |
Returns a random number. |
√ |
√ |
|
|
random() |
Returns a random number in the range [0, 1). |
√ |
√ |
|
|
random(x) |
Returns a random number in the range [0, x). |
√ |
√ |
|
|
round(x) |
Rounds x to the nearest integer. |
√ |
√ |
|
|
round(x, n) |
Round x to n decimal places. |
√ |
√ |
|
|
sign(x) |
Returns the sign of x as 1, 0, or -1. |
√ |
× |
|
|
sin(x) |
Returns the sine of x. |
√ |
√ |
|
|
sqrt(x) |
Returns the square root of x. |
√ |
√ |
|
|
tan(x) |
Returns the tangent of x. |
√ |
√ |
|
|
tanh(x) |
Returns the hyperbolic tangent of x. |
√ |
√ |
|
|
to_base(x, y) |
Converts the number x to its base-y string representation. |
√ |
× |
|
|
truncate(x) |
Removes the fractional part of x. |
√ |
√ |
|
|
width_bucket(x, bound1, bound2, numBuckets) |
Returns the bucket number of x within a range divided into equal-width buckets. |
√ |
× |
|
|
width_bucket(x, bins) |
Uses an array to specify the range of buckets and returns the bucket to which x belongs. |
√ |
× |
Abs function
The abs function calculates the absolute value of x.
Syntax
abs(x)Parameters
|
Parameter |
Description |
|
x |
The parameter can be a smallint, integer, real, tinyint, bigint, double, or decimal. |
Return value type
The returned value has the same data type as the input parameter.
Examples
Calculate the absolute value of -25.
-
Query and analysis statement
* | select abs(-25) -
Query and analysis results: 25.
acos function
Calculates the arc cosine of x.
Syntax
acos(x)Parameters
|
Parameter |
Description |
|
x |
This parameter must be a double in the range [-1, 1]. If the value is outside this range, the function returns NaN. |
Return value type
A double value.
Examples
This example calculates the arc cosine of pi()/4.
-
Query statement
* | SELECT acos(pi()/4) -
Query and analysis results: The query returns 0.6674572160283838.
Asin function
Calculates the arcsine of x.
Syntax
asin(x)Parameters
|
Parameter |
Description |
|
x |
A double value in the range [-1, 1]. If the value is outside this range, the function returns NaN. |
Return value type
double
Examples
Calculates the arcsine of π/4.
-
Query and analysis statement
* | SELECT asin(pi()/4) -
Query and analysis results: 0.9033391101131845.
Atan function
Calculates the arc tangent of x.
Syntax
atan(x)Parameters
|
Parameter |
Description |
|
x |
The value must be a double. |
Return value type
Returns a double value.
Examples
This example calculates the arc tangent of pi()/4.
-
Query statement
* | SELECT atan(pi()/4) -
Query result: 0.6657737500283538
atan2 function
Calculates the arctangent of x divided by y.
Syntax
atan2(x, y)Parameters
|
Parameter |
Description |
|
x |
A double value. |
|
y |
A double value. |
Return value type
Returns a double value.
Examples
Calculate the arctangent of π divided by 6.
-
Query statement
* | SELECT atan2(pi(),6) -
Query result: The return value is 0.48234790710102495.
Cbrt function
Calculates the cube root of x.
Syntax
cbrt(x)Parameters
|
Parameter |
Description |
|
x |
A double value. |
Return value type
Returns a double value.
Examples
Calculate the cube root of 100.
-
Query statement
* | select cbrt(100) -
Query result: 4.641588833612779.
Ceil function
The ceil function rounds x up to the nearest integer. The ceil function is an alias for the ceiling function.
Syntax
ceil(x)Parameters
|
Parameter |
Description |
|
x |
A value of the tinyint, smallint, integer, real, bigint, double, or decimal types.
|
Return value type
The return value has the same data type as the parameter.
Examples
Round the value of the request_time field up to the nearest integer.
-
Sample field
request_time:9.3 -
Query statement
* | SELECT ceil(request_time) AS request_time -
Query results: The value of the request_time column is 10.0.
Ceiling function
The ceiling function rounds x up to the nearest integer.
Syntax
ceiling(x)Parameters
|
Parameter |
Description |
|
x |
A value of type tinyint, smallint, integer, real, bigint, double, or decimal.
|
Return value type
The same data type as the parameter.
Examples
Rounds the value of the request_time field up to the nearest integer.
-
Sample field
request_time:9.3 -
Query and analysis statement
* | SELECT ceiling(request_time) AS request_time -
Query and analysis results: The request_time column is 10.0.
Cos function
Calculates the cosine of x.
Syntax
cos(x)Parameters
|
Parameter |
Description |
|
x |
The parameter must be a double. |
Return value type
Returns a double.
Examples
Calculates the cosine of 30 degrees.
-
Query and analysis statement
* | SELECT cos(pi()/6) -
Query and analysis result: 0.8660254037844387.
Cosh function
The cosh function calculates the hyperbolic cosine of x.
Syntax
cosh(x)Parameters
|
Parameter |
Description |
|
x |
The value is a double. |
Return value type
Returns a double value.
Examples
Calculate the hyperbolic cosine of a 30° angle.
-
Query and analysis statement
* | SELECT cosh(pi()/6) -
Query and analysis results: 1.1402383210764287.
Cosine_similarity
This function calculates the cosine similarity between x and y.
Syntax
cosine_similarity(x, y)Parameters
|
Parameter |
Description |
|
x |
The parameter is a map(varchar, double). |
|
y |
The parameter is a map(varchar, double). |
Return value type
Returns a double value.
Examples
Calculates the cosine similarity between two vectors.
-
Query
* | SELECT cosine_similarity(MAP(ARRAY['a'], ARRAY[1.0]), MAP(ARRAY['a'], ARRAY[2.0])) -
The query returns 1.0.
Degrees function
The degrees function converts an angle from radians to degrees.
Syntax
degrees(x)Parameters
|
Parameter |
Description |
|
x |
The angle in radians, specified as a double value. |
Return value type
The equivalent angle in degrees, returned as a double value.
Examples
Convert π radians to degrees.
-
Query statement
* | SELECT degrees(pi()) -
Query and analysis results: The result is 180.0.
e function
The e function returns the value of e, the base of the natural logarithm.
Syntax
e()Return value type
A double value.
Examples
Returns the value of e.
-
Query
* | SELECT e() -
Query and analysis results: The return value is 2.718281828459045.
exp function
Calculates e to the power of x.
Syntax
exp(x)Parameters
|
Parameter |
Description |
|
x |
A double value. |
Return value type
A double value.
Examples
Calculates e to the power of 3.
-
Query and analysis statement
* | SELECT exp(3) -
Query and analysis result: The return value is 20.085536923187669.
Floor function
The floor function rounds x down to the nearest integer.
Syntax
floor(x)Parameters
|
Parameter |
Description |
|
x |
The value must be a tinyint, smallint, integer, real, bigint, double, or decimal.
|
Return value type
Returns a double value.
Examples
Rounds the value of the request_time field down to the nearest integer.
-
Sample field
request_time:10.3 -
Query statement
* | SELECT floor(request_time) AS request_time -
Query and analysis results: The value of the request_time column is 10.0.
from_base function
The from_base function converts the string x from base y to a number.
Syntax
from_base(x, y)Parameters
|
Parameter |
Description |
|
x |
A varchar string. |
|
y |
The base for the number. The value must be a bigint from 2 to 36. |
Return value type
Returns a bigint.
Examples
Converts the binary string '1101' to its decimal equivalent.
-
Query statement
* | SELECT from_base('1101',2) -
Results: The return value is 13.
Ln function
The ln function calculates the natural logarithm of x.
Syntax
ln(x)Parameters
|
Parameter |
Description |
|
x |
A double value greater than 0. |
Return value type
A double value.
Examples
This example calculates the natural logarithm of 2.
-
Query and analysis statement
* | SELECT ln(2) -
Query and analysis results: Returns 0.6931471805599453.
Infinity function
The infinity function returns positive infinity.
Syntax
infinity()Return value type
double
Examples
Returns positive infinity.
-
Query and analysis statement
* | SELECT infinity() -
Query and analysis results: Infinity.
Is_nan function
The is_nan function returns true if x is NaN (Not a Number), and false otherwise.
Syntax
is_nan(x)Parameters
|
Parameter |
Description |
|
x |
A double value. |
Return value type
Returns a boolean value.
Examples
Check whether the value of the status field is NaN.
-
Query and analysis statement
* | SELECT is_nan(status) -
Query and analysis results: The statement returns false.
log2 function
The log2 function calculates the base-2 logarithm of x.
Syntax
log2(x)Parameters
|
Parameter |
Description |
|
x |
A double value. |
Return value type
Returns a double value.
Examples
Calculate the base-2 logarithm of 100.
-
Query and analysis statement
* | SELECT log2(100) -
Query and analysis results: The return value is 6.643856189774725.
log10 function
The log10 function calculates the base-10 logarithm of x.
Syntax
log10(x)Parameters
|
Parameter |
Description |
|
x |
A |
Return value type
Returns a double value.
Examples
This example calculates the base-10 logarithm of 100.
-
Query and analysis statement
* | SELECT log10(100) -
Query and analysis result: 2.0.
Log function
The log function calculates the base-y logarithm of x.
Syntax
log(x, y)Parameters
|
Parameter |
Description |
|
x |
The value must be a double. |
|
y |
The value must be a double. |
Return value type
Returns a double value.
Examples
Calculates the base-5 logarithm of 100.
-
Query
* | SELECT log(100,5) -
Result: 2.8613531161467867.
mod function
The mod function returns the remainder when x is divided by y.
Syntax
mod(x, y)Parameters
|
Parameter |
Description |
|
x |
A value of type tinyint, smallint, integer, real, bigint, double, or decimal. |
|
y |
A value of type tinyint, smallint, integer, real, bigint, double, or decimal. |
Return value type
The return value has the same data type as the input parameters.
Examples
Calculate the remainder of 100 divided by 30.
-
Query statement
* | SELECT mod(100,30) -
Query and analysis result: Returns 10.
Nan function
The nan function returns NaN (Not a Number).
Syntax
nan()Return value type
Double.
Examples
Returns a NaN value.
-
Query statement
* | SELECT nan() -
Query results: The return value is NaN.
Pi function
This function returns the value of π, accurate to 15 decimal places.
Syntax
pi()Return value type
Returns a double value.
Examples
This example returns the value of π.
-
Query and analysis statement
* | SELECT pi() -
Query and analysis results: 3.141592653589793.
Pow function
The pow function returns x raised to the power of y. It is an alias for the power function.
Syntax
pow(x, y)Parameters
|
Parameter |
Description |
|
x |
A double value. |
|
y |
A double value. |
Return value type
Returns a double value.
Examples
Raise 2 to the power of 5.
-
Query statement
* | SELECT pow(2,5) -
Query and analysis results: 32.0.
Power function
Returns x raised to the power of y.
Syntax
power(x, y)Parameters
|
Parameter |
Description |
|
x |
A double value. |
|
y |
A double value. |
Return value type
A double value.
Examples
Calculate 2 to the power of 5.
-
Query and analysis statement
* | SELECT power(2,5) -
Query and analysis results: Returns 32.0.
Radians function
Converts an angle from degrees to radians.
Syntax
radians(x)Parameters
|
Parameter |
Description |
|
x |
Must be a |
Return value type
Returns a double.
Examples
Convert 180 degrees to radians.
-
Query statement
* | SELECT radians(180) -
Query results: 3.141592653589793
Rand function
The rand function returns a random number.
Syntax
rand()Return value type
double
Examples
The following query returns a random number.
-
Query statement
* | select rand() -
Query and analysis results: Returns a random value, such as 0.8742241064002435.
Random function
The random function returns a random number in the range [0,x).
Syntax
-
Returns a random double-precision value in the range [0, 1.0).
random() -
Returns a random integer in the range [0, x).
random(x)
Parameters
|
Parameter |
Description |
|
x |
A tinyint, smallint, integer, or bigint value. |
Return value type
Returns a value with the same data type as the input parameter.
Examples
Generate a random number in the range [0, 100).
-
Query statement
* | select random(100) -
Query results: Returns a random integer, for example, 44.
Round function
The round function rounds x. If you specify n, the function rounds the value to n decimal places. If you omit n, it rounds x to the nearest integer.
Syntax
-
Rounds x to the nearest integer.
round(x) -
Rounds x to n decimal places.
round(x, n)
Parameters
|
Parameter |
Description |
|
x |
A tinyint, smallint, integer, or bigint value. |
|
n |
The number of decimal places to retain. |
Return value type
The return value has the same data type as the x parameter.
Examples
This example compares today's page views (PVs) with yesterday's and expresses the change as a percentage.
-
Query statement
* | SELECT diff [1] AS today, round((diff [3] -1.0) * 100, 2) AS growth FROM (SELECT compare(pv, 86400) as diff FROM (SELECT COUNT(*) as pv FROM website_log)) -
The query returns today as 1564075.0 and growth as -22.11.
sign function
The sign function returns the sign of x as 1, 0, or -1.
Syntax
sign(x)Parameters
|
Parameter |
Description |
|
x |
The numeric expression to evaluate. Its data type can be integer, smallint, tinyint, real, double, bigint, or decimal(p,s).
|
Return value type
Returns the same data type as the input parameter.
Examples
Calculate the sign of the number 10.
-
Query statement
* | SELECT sign(10) -
Query result: The returned value is 1.
Sin function
This function calculates the sine of x.
Syntax
sin(x)Parameters
|
Parameter |
Description |
|
x |
A double value. |
Return value type
Returns a double value.
Examples
This example calculates the sine of 90 degrees.
-
Query and analysis statement
* | select sin(pi()/2) -
Query and analysis results: Returns 1.0.
Sqrt function
Calculates the square root of x.
Syntax
sqrt(x)Parameters
|
Parameter |
Description |
|
x |
A double value. |
Return value type
Returns a double value.
Examples
Calculate the square root of 100.
-
Query and analysis statement
* | select sqrt(100) -
Query and analysis results: The result is 10.0.
Tan function
The tan function calculates the tangent of x.
Syntax
tan(x)Parameters
|
Parameter |
Description |
|
x |
A double-precision value. |
Return value type
A double-precision value.
Examples
Calculate the tangent of 30 degrees.
-
Query statement
* | SELECT tan(pi()/6) -
Query results: 0.5773502691896257.
Tanh function
The tanh function calculates the hyperbolic tangent of x.
Syntax
tanh(x)Parameters
|
Parameter |
Description |
|
x |
Must be a |
Return value type
Returns a double value.
Examples
This example calculates the hyperbolic tangent of π/6 radians (30 degrees).
-
Query
* | SELECT tanh(pi()/6) -
Query result: The query returns 0.4804727781564516.
To_base function
This function converts the bigint x to its string representation in base y.
Syntax
to_base(x, y)Parameters
|
Parameter |
Description |
|
x |
The bigint to convert. |
|
y |
The base for the output string. The value must be a bigint from 2 to 36, inclusive. |
Return value type
Returns a varchar value.
Examples
Convert 180 to a binary string.
-
Query statement
* | SELECT to_base(180, 2) -
Query result: 10110100
Truncate function
The truncate function removes the fractional part of x.
Syntax
truncate(x)Parameters
|
Parameter |
Description |
|
x |
A double value. |
Return value type
Returns a double value.
Examples
Remove the fractional part of 11.11.
-
Query statement
* | SELECT truncate(11.11) -
Query result: 11.0.
width_bucket function
The width_bucket function returns the bucket number for a value x.
Syntax
-
Divides a numeric range into a specified number of equal-width buckets and returns the bucket number for x.
width_bucket(x, bound1, bound2, numBuckets) -
Returns the bucket number for x based on a specified array of bucket boundaries.
width_bucket(x, bins)
Parameters
|
Parameter |
Description |
|
x |
The value to be bucketed. This value must be of the double type. |
|
bound1 |
Specifies the lower limit of the numeric range. |
|
bound2 |
Specifies the upper limit of the numeric range. |
|
numBuckets |
The number of buckets. The value must be an integer greater than 0. |
|
bins |
Use bins, an array of the double type, to specify the range of the bucket. |
Return value type
Returns a value of the bigint type.
-
If x is within the specified range, the bucket to which x belongs is returned.
-
If x is below the lower limit, the function returns 0.
-
If x is greater than or equal to the upper limit, the function returns numBuckets+1.
Examples
-
Example 1: Divide the numeric range [10, 80) into 7 equal-width buckets and return the bucket number for each value in the request_time field.
-
Query statement
* | SELECT request_time, width_bucket(request_time, 10, 80,7) AS numBuckets -
Query and analysis results: The query returns the request_time and numBuckets columns. These columns show the corresponding bucket number for each request time. For example, if request_time is 26.0, numBuckets is 2, and if request_time is 49.0, numBuckets is 4.
-
-
Example 2: Use an array to define the boundaries for buckets and return the bucket number for each value in the request_time field.
-
Query statement
* | SELECT request_time, width_bucket(request_time, array[10,20,30,40,50,60,70,80]) AS numBuckets -
Query and analysis results: The query returns the request_time and numBuckets columns. These columns show the bucket number assigned to each request time based on the custom boundary array. For example, if request_time is 26.0, numBuckets is 2, and if request_time is 54.0, numBuckets is 5.
-