This topic describes the syntax of mathematical calculation functions. This topic also provides examples on how to use the functions.
- The following operators are supported:
+ - * / %
- If you want to use strings in analytic statements, you must enclose the strings in single quotation marks (''). Strings that are not enclosed or are enclosed in double quotation marks ("") are considered field names or column names. For example, 'status' is considered the status string, and status or "status" is considered a log field whose name is status.
| Function | Syntax | Description |
|---|---|---|
| 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.
The ceil 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.
The pow 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(x) | Returns a random number in the range [0,x). | |
| round function | round(x) | Rounds x to the nearest integer. |
| 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_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 |
|---|---|
| x | 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 |
|---|---|
| x | 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. The ceil function is an alias of the ceiling function.
Syntax
ceil(x)Parameters
| Parameter | Description |
|---|---|
| x | 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
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 |
|---|---|
| x | 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
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 |
|---|---|
| x | The value of this parameter is of the tinyint, smallint, integer, real, bigint, double,
or decimal type.
|
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 ceiling(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 it is, 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 values.
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. The pow 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
- The following random function returns a random number in the range [0,1).
random() - The following random 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
- The following round function rounds x to the nearest integer.
round(x) - The following round 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 | The value of 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
- 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 |
|---|---|
| x | The value of this parameter is of the integer, smallint, tinyint, real, double, bigint,
or decimal(p,s) type.
|
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
- The following width_bucket function divides a numeric range into buckets of equal
width and returns the bucket number of x.
width_bucket(x,bound1,bound2,numBuckets) - The following width_bucket 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 | The value of this parameter specifies the lower limit of the numeric range. |
| bound2 | The value of this parameter specifies the upper limit of the numeric range. |
| numBuckets | The value of this parameter specifies the number of buckets. The value must be an integer greater than 0. |
| bins | The value of this parameter specifies the range of buckets. The value is an array of the double type. |
Return value type
- 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
- Query statement
- 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
- Query statement