Syntax and examples of mathematical calculation functions - Simple Log Service

This topic describes the syntax of mathematical calculation functions. This topic also provides examples on how to use the functions.

The following table describes the mathematical calculation functions supported by Simple Log Service.

Note

The following operators are supported:
+-*/%
By default, the decimal part is excluded each time you use the (/) operator to perform calculation. You can use the x*1.0/y expression to retain the decimal part each time you perform calculation.
If you want to use strings in analytic statements, you must enclose the strings in single quotation marks (''). Strings that are not enclosed or strings that are enclosed in double quotation marks ("") indicate field names or column names. For example, 'status' indicates the status string, and status or "status" indicates the status log field.

Function	Syntax	Description	Supported in SQL	Supported in SPL
abs function	abs(x)	Calculates the absolute value of x.	√	√
acos function	acos(x)	Calculates the arc cosine of x.	√	√
asin function	asin(x)	Calculates the arc sine of x.	√	√
atan function	atan(x)	Calculates the arc tangent of x.	√	√
atan2 function	atan2(x, y)	Calculates the arc tangent of x divided by y.	√	√
cbrt function	cbrt(x)	Calculates the cube root of x.	√	√
ceil function	ceil(x)	Rounds x up to the nearest integer. This function is an alias of the ceiling function.	√	√
ceiling function	ceiling(x)	Rounds x up to the nearest integer.	√	√
cos function	cos(x)	Calculates the cosine of x.	√	√
cosh function	cosh(x)	Calculates the hyperbolic cosine of x.	√	√
cosine_similarity function	cosine_similarity(x, y)	Calculates the cosine similarity between x and y.	√	×
degrees function	degrees(x)	Converts an angle in radians to its equivalent in degrees.	√	√
e function	e()	Returns the value of e, which is the base of the natural logarithm.	√	√
exp function	exp(x)	Raises e to the power of x.	√	×
floor function	floor(x)	Rounds x down to the nearest integer.	√	√
from_base function	from_base(x, y)	Converts x to a base y number.	√	√
ln function	ln(x)	Calculates the natural logarithm of x.	√	√
infinity function	infinity()	Returns a value that represents positive infinity.	√	√
is_nan function	is_nan(x)	Determines whether x is Not a Number (NaN).	√	√
log2 function	log2(x)	Calculates the base-2 logarithm of x.	√	√
log10 function	log10(x)	Calculates the base-10 logarithm of x.	√	√
log function	log(x, y)	Calculates the base-y logarithm of x.	√	×
mod function	mod(x, y)	Calculates the remainder of x divided by y.	√	√
nan function	nan()	Returns a value that is NaN.	√	√
pi function	pi()	Returns the value of π to 15 decimal places.	√	√
pow function	pow(x, y)	Raises x to the power of y. This function is an alias of the power function.	√	√
power function	power(x, y)	Raises x to the power of y.	√	√
radians function	radians(x)	Converts an angle in degrees to its equivalent in radians.	√	√
rand function	rand()	Returns a random number.	√	√
random function	random()	Returns a random number in the range [0,1).	√	√
random function	random(x)	Returns a random number in the range [0,x).	√	√
round function	round(x)	Rounds x to the nearest integer.	√	√
round function	round(x, n)	Rounds x to the nearest decimal with n decimal places.	√	√
sign function	sign(x)	Returns the sign of x. Valid values: 1, 0, and -1.	√	×
sin function	sin(x)	Calculates the sine of x.	√	√
sqrt function	sqrt(x)	Calculates the square root of x.	√	√
tan function	tan(x)	Calculates the tangent of x.	√	√
tanh function	tanh(x)	Calculates the hyperbolic tangent of x.	√	√
to_base function	to_base(x, y)	Converts x to a base y string.	√	×
truncate function	truncate(x)	Removes the fractional part of x.	√	√
width_bucket function	width_bucke(x, bound1, bound2, numBuckets)	Divides a numeric range into buckets of equal width and returns the bucket number of x.	√	×
width_bucket function	width_bucke(x, bins)	Returns the bucket number of x in the range of buckets that are specified by an array.	√	×

abs function

The abs function calculates the absolute value of x.

Syntax

abs(x)

Parameters

Parameter	Description
x	The value of this parameter is of the smallint, integer, real, tinyint, bigint, double, or decimal type.

Return value type

Same as the data type of the parameter value.

Examples

Calculate the absolute value of -25.

Query statement
```
* | select abs(-25)
```
Query and analysis results

acos function

The acos function calculates the arc cosine of x.

Syntax

acos(x)

Parameters

Parameter

Description

The value of this parameter is of the double type. Valid values: [-1,1].

If the value is out of the range [-1,1], the function returns NaN.

Return value type

The double type.

Examples

Calculate the arc cosine of the 45° angle.

Query statement
```
* | SELECT acos(pi()/4)
```
Query and analysis results

asin function

The asin function calculates the arc sine of x.

Syntax

asin(x)

Parameters

Parameter

Description

The value of this parameter is of the double type. Valid values: [-1,1].

If the value is out of the range [-1,1], the function returns NaN.

Return value type

The double type.

Examples

Calculate the arc sine of the 45° angle.

Query statement
```
* | SELECT  asin(pi()/4)
```
Query and analysis results

atan function

The atan function calculates the arc tangent of x.

Syntax

atan(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Calculate the arc tangent of the 45° angle.

Query statement
```
* | SELECT atan(pi()/4)
```
Query and analysis results

atan2 function

The atan2 function calculates the arc tangent of x divided by y.

Syntax

atan2(x, y)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.
y	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Calculate the arc tangent of the 30° angle.

Query statement
```
* | SELECT atan2(pi(),6)
```
Query and analysis results

cbrt function

The cbrt function calculates the cube root of x.

Syntax

cbrt(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Calculate the cube root of 100.

Query statement
```
* | select cbrt(100)
```
Query and analysis results

ceil function

The ceil function rounds x up to the nearest integer. This function is an alias of the ceiling function.

Syntax

ceil(x)

Parameters

Parameter

Description

The value of this parameter is of the tinyint, smallint, integer, real, bigint, double, or decimal type.

If the value of x is a positive number, the function rounds the value away from 0.
If the value of x is a negative number, the function rounds the value towards 0.

Return value type

Same as the data type of the parameter value.

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 and analysis results

ceiling function

The ceiling function rounds x up to the nearest integer.

Syntax

ceiling(x)

Parameters

Parameter

Description

The value of this parameter is of the tinyint, smallint, integer, real, bigint, double, or decimal type.

If the value of x is a positive number, the function rounds the value away from 0.
If the value of x is a negative number, the function rounds the value towards 0.

Return value type

Same as the data type of the parameter value.

Examples

Round the value of the request_time field up to the nearest integer.

Sample field
```
request_time:9.3
```

Query statement

* | SELECT ceiling(request_time) AS request_time

Query and analysis results

cos function

The cos function calculates the cosine of x.

Syntax

cos(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Calculate the cosine of the 30° angle.

Query statement
```
* | SELECT cos(pi()/6)
```
Query and analysis results

cosh function

The cosh function calculates the hyperbolic cosine of x.

Syntax

cosh(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Calculate the hyperbolic cosine of the 30° angle.

Query statement
```
* | SELECT cosh(pi()/6)
```
Query and analysis results

cosine_similarity function

The cosine_similarity function calculates the cosine similarity between x and y.

Syntax

cosine_similarity(x, y)

Parameters

Parameter	Description
x	The value of this parameter is of the map(varchar,double) type.
y	The value of this parameter is of the map(varchar,double) type.

Return value type

The double type.

Examples

Calculate the cosine similarity between two vectors.

Query statement

* | SELECT cosine_similarity(MAP(ARRAY['a'], ARRAY[1.0]), MAP(ARRAY['a'], ARRAY[2.0]))

Query and analysis results

degrees function

The degrees function converts an angle in radians to its equivalent in degrees.

Syntax

degrees(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Convert π in radians to its equivalent in degrees.

Query statement
```
* | SELECT degrees(pi())
```
Query and analysis results

e function

The e function returns the value of e, which is the base of the natural logarithm.

Syntax

e()

Return value type

The double type.

Examples

Obtain the value of e.

Query statement
```
* | SELECT e()
```
Query and analysis results

exp function

The exp function raises e to the power of x.

Syntax

exp(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Raise e to the power of 3.

Query statement
```
* | SELECT exp(3)
```
Query and analysis results

floor function

The floor function rounds x down to the nearest integer.

Syntax

floor(x)

Parameters

Parameter

Description

The value of this parameter is of the tinyint, smallint, integer, real, bigint, double, or decimal type.

If the value of x is a positive number, the function rounds the value towards 0.
If the value of x is a negative number, the function rounds the value away from 0.

Return value type

The double type.

Examples

Round 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

from_base function

The from_base function converts x to a base y number.

Syntax

from_base(x, y)

Parameters

Parameter	Description
x	The value of this parameter is of the varchar type.
y	The value of this parameter is of the bigint type. The value specifies a numeral system. Valid values: [2,36].

Return value type

The bigint type.

Examples

Convert the string 1101 to a number.

Query statement
```
* | SELECT  from_base('1101',2)
```
Query and analysis results

ln function

The ln function calculates the natural logarithm of x.

Syntax

ln(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type. The value must be greater than 0.

Return value type

The double type.

Examples

Calculate the natural logarithm of 2.

Query statement
```
* | SELECT ln(2)
```
Query and analysis results

infinity function

The infinity function returns a value that represents positive infinity.

Syntax

infinity()

Return value type

The double type.

Examples

Obtain a value that represents positive infinity.

Query statement
```
* | SELECT infinity()
```
Query and analysis results

is_nan function

The is_nan function determines whether x is NaN. If yes, the function returns true.

Syntax

is_nan(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The Boolean type.

Examples

Check whether the value of the status field is NaN.

Query statement
```
* | SELECT is_nan(status)
```
Query and analysis results

log2 function

The log2 function calculates the base-2 logarithm of x.

Syntax

log2(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Calculate the base-2 logarithm of 100.

Query statement
```
* | SELECT log2(100)
```
Query and analysis results

log10 function

The log10 function calculates the base-10 logarithm of x.

Syntax

log10(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Calculate the base-10 logarithm of 100.

Query statement
```
* | SELECT log10(100)
```
Query and analysis results

log function

The log function calculates the base-y logarithm of x.

Syntax

log(x, y)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.
y	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Calculate the base-5 logarithm of 100.

Query statement
```
* | SELECT log(100,5)
```
Query and analysis results

mod function

The mod function calculates the remainder of x divided by y.

Syntax

mod(x, y)

Parameters

Parameter	Description
x	The value of this parameter is of the tinyint, smallint, integer, real, bigint, double, or decimal type.
y	The value of this parameter is of the tinyint, smallint, integer, real, bigint, double, or decimal type.

Return value type

Same as the data type of the parameter value.

Examples

Calculate the remainder of 100 divided by 30.

Query statement
```
* | SELECT mod(100,30)
```
Query and analysis results

nan function

The nan function returns a value that is NaN.

Syntax

nan()

Return value type

The double type.

Examples

Obtain a value that is NaN.

Query statement
```
* | SELECT nan()
```
Query and analysis results

pi function

The pi function returns the value of π to 15 decimal places.

Syntax

pi()

Return value type

The double type.

Examples

Obtain the value of π to 15 decimal places.

Query statement
```
* | SELECT pi()
```
Query and analysis results

pow function

The pow function raises x to the power of y. This function is an alias of the power function.

Syntax

pow(x, y)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.
y	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Raise 2 to the power of 5.

Query statement
```
* | SELECT pow(2,5)
```
Query and analysis results

power function

The power function raises x to the power of y.

Syntax

power(x, y)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.
y	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Raise 2 to the power of 5.

Query statement
```
* | SELECT power(2,5)
```
Query and analysis results

radians function

The radians function converts an angle in degrees to its equivalent in radians.

Syntax

radians(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Convert the 180° angle in degrees to its equivalent in radians.

Query statement
```
* | SELECT radians(180)
```
Query and analysis results

rand function

The rand function returns a random number.

Syntax

rand()

Return value type

The double type.

Examples

Obtain a random number.

Query statement
```
* | select rand()
```
Query and analysis results

random function

The random function returns a random number in the range [0,x).

Syntax

If you use the following syntax, the function returns a random number in the range [0,1).
```
random()
```
If you use the following syntax, the function returns a random number in the range [0,x).
```
random(x)
```

Parameters

Parameter	Description
x	The value of this parameter is of the tinyint, smallint, integer, or bigint type.

Return value type

Same as the data type of the parameter value.

Examples

Obtain a random number in the range [0,100).

Query statement
```
* | select random(100)
```
Query and analysis results

round function

The round function rounds x to the nearest integer or decimal. If n is specified, the function retains n decimal places. If n is not specified, the function rounds x to the nearest integer.

Syntax

If you use the following syntax, the function rounds x to the nearest integer.
```
round(x)
```
If you use the following syntax, the function rounds x to the nearest decimal with n decimal places.
```
round(x, n)
```

Parameters

Parameter	Description
x	The value of this parameter is of the tinyint, smallint, integer, or bigint type.
n	This parameter specifies the number of decimal places that you want the function to retain.

Return value type

Same as the data type of the parameter value.

Examples

Compare the number of page views (PVs) of the current day with the number of PVs of the previous day. Then, present the comparison result 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))

Query and analysis results

sign function

The sign function returns the sign of x. Valid values: 1, 0, and -1.

Syntax

sign(x)

Parameters

Parameter

Description

The value of this parameter is of the integer, smallint, tinyint, real, double, bigint, or decimal(p,s) type.

If x is a positive number, the function returns 1.
If x is 0, the function returns 0.
If x is a negative number, the function return -1.

Return value type

Same as the data type of the parameter value.

Examples

Obtain the sign of 10.

Query statement
```
* | SELECT sign(10)
```
Query and analysis results

sin function

The sin function calculates the sine of x.

Syntax

sin(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Calculate the sine of the 90° angle.

Query statement
```
* | select sin(pi()/2)
```
Query and analysis results

sqrt function

The sqrt function calculates the square root of x.

Syntax

sqrt(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Calculate the square root of 100.

Query statement
```
* | select sqrt(100)
```
Query and analysis results

tan function

The tan function calculates the tangent of x.

Syntax

tan(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Calculate the tangent of the 30° angle.

Query statement
```
* | SELECT tan(pi()/6)
```
Query and analysis results

tanh function

The tanh function calculates the hyperbolic tangent of x.

Syntax

tanh(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Calculate the hyperbolic tangent of the 30° angle.

Query statement
```
* | SELECT tanh(pi()/6)
```
Query and analysis results

to_base function

The to_base function converts x to a base y string.

Syntax

to_base(x, y)

Parameters

Parameter	Description
x	The value of this parameter is of the bigint type.
y	The value of this parameter is of the bigint type. The value specifies a numeral system. Valid values: [2,36].

Return value type

The varchar type.

Examples

Convert 180 to a binary string.

Query statement
```
* | SELECT  to_base(180, 2)
```
Query and analysis results

truncate function

The truncate function removes the fractional part of x.

Syntax

truncate(x)

Parameters

Parameter	Description
x	The value of this parameter is of the double type.

Return value type

The double type.

Examples

Remove the fractional part of 11.11.

Query statement
```
* | SELECT  truncate(11.11)
```
Query and analysis results

width_bucket function

The width_bucket function returns the bucket number of x.

Syntax

If you use the following syntax, the function divides a numeric range into buckets of equal width and returns the bucket number of x.
```
width_bucket(x, bound1, bound2, numBuckets)
```
If you use the following syntax, the function returns the bucket number of x in the range of buckets that are specified by an array.
```
width_bucket(x, bins)
```

Parameters

Parameter	Description
x	The value of this parameter is of the double type.
bound1	This parameter specifies the lower limit of the numeric range.
bound2	This parameter specifies the upper limit of the numeric range.
numBuckets	This parameter specifies the number of buckets. The value must be an integer greater than 0.
bins	This parameter specifies the range of buckets. The value is an array of the double type.

Return value type

The bigint type.

Note

If x is within the range, the function returns the bucket number of x.
If x is below the lower limit, the function returns 0.
If x is above the upper limit, the function returns numBuckets+1.

Examples

Example 1: Divide the range [10,80) into 7 buckets. Then, obtain the bucket number for each value of the request_time field.
- Query statement
```
* | SELECT request_time, width_bucket(request_time, 10, 80,7) AS numBuckets
```
- Query and analysis results
Example 2: Use an array to specify the range of 7 buckets. Then, obtain the bucket number for each value of 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