All Products
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 resultsabs

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 resultsacos

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 resultsacos

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 resultsatan

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 resultsatan2

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 resultscbrt

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 resultsceiling

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 resultsceiling

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 resultscos

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 resultscosh

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 resultscosine_similarity

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 resultsdegrees

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

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 resultsexp

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.

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 resultsceiling

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 resultsfrom_base

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 resultsln

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 resultsinfinity

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 resultsis_nan

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 resultslog2

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 resultslog10

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 resultslog

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 resultsmod

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 resultsnan

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 resultspi

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 resultspow

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 resultspow

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 resultsradians

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 resultsrand

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 resultsrandom

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 resultsround

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 resultssign

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 resultssin

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 resultssqrt

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 resultstan

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 resultstanh

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 resultsto_base

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 resultstruncate

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 width_bucket

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