Search
Document Center

# Simple Log Service:Mathematical calculation functions

Last Updated:Mar 01, 2024

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. 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.

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.

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.

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.

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.

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. 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 x 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.

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.

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.

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.

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()``

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.

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. 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.

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].

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.

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()``

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.

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.

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.

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()``

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()``

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.

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.

The double type.

### Examples

Raise 2 to the power of 5.

• Query statement

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

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.

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()``

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 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

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 x 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.

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.

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.

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.

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.

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 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

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