Help Center/ DataArts Fabric/ Developer Guide/ SQL Syntax Reference/ Functions and Operators/ Numeric Operation Functions and Operators/ Numeric Operation Functions
Updated on 2025-09-18 GMT+08:00
View PDF
Share

Numeric Operation Functions

abs(x)

Description: Absolute value.

Return type: same as the input.

Example:

1
2
3
4
5
 
SELECT abs(-17.4);
 abs
------
 17.4
(1 row)

acos(x)

Description: Arc cosine.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT acos(-1);
       acos       
------------------
 3.14159265358979
(1 row)

asin(x)

Description: Arc sine.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT asin(0.5);
       asin       
------------------
 0.523598775598299
(1 row)

atan(x)

Description: Arc tangent.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT atan(1);
       atan       
------------------
 0.785398163397448
(1 row)

atan2(y, x)

Description: Arc tangent of y/x.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT atan2(2, 1);
      atan2
------------------
 1.10714871779409
(1 row)

bitand(integer, integer)

Description: Bitwise AND operation of two integers.

Return type: bigint.

Example:

1
2
3
4
5
 
SELECT bitand(127, 63);
 bitand 
--------
     63
(1 row)

cbrt(double precision)

Description: Cube root.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT cbrt(27.0);
 cbrt
------
    3
(1 row)

ceil(double precision or numeric)

Description: Smallest integer not less than the parameter.

Return type: same as the input.

Example:

1
2
3
4
5
 
SELECT ceil(-42.8);
 ceil 
------
  -42
(1 row)

ceiling(double precision or numeric)

Description: Smallest integer not less than the parameter (alias for ceil).

Return type: same as the input.

Example:

1
2
3
4
5
 
SELECT ceiling(-95.3);
 ceiling
---------
     -95
(1 row)

cos(x)

Description: Cosine.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT cos(-3.1415927);
        cos        
-------------------
 -0.999999999999999
(1 row)

cot(x)

Description: Cotangent.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT cot(1);
       cot
------------------
 0.642092615934331
(1 row)

degrees(double precision)

Description: Converts radians to degrees.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT degrees(0.5);
     degrees
------------------
 28.6478897565412
(1 row)

div(y numeric, x numeric)

Description: Integer quotient of y divided by x.

Return type: numeric.

Example:

1
2
3
4
5
 
SELECT div(9,4);
 div
-----
   2
(1 row)

exp(double precision or numeric)

Description: Exponential.

Return type: same as the input.

Example:

1
2
3
4
5
 
SELECT exp(1.0);
        exp         
--------------------
 2.7182818284590452
(1 row)

floor(double precision or numeric)

Description: Largest integer not greater than the parameter.

Return type: same as the input.

Example:

1
2
3
4
5
 
SELECT floor(-42.8);
 floor 
-------
   -43
(1 row)

radians(double precision)

Description: Converts degrees to radians.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT radians(45.0);
     radians
------------------
 0.785398163397448
(1 row)

random()

Description: Random number ranging from 0.0 to 1.0.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT random();
      random
------------------
 0.696101832669228
(1 row)

rand()

Description: Random number ranging from 0.0 to 1.0. This function is compatible with MySQL. This function is only supported by clusters of version 8.2.0 or later.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT rand();
      rand
------------------
 0.930654047057033
(1 row)

ln(double precision or numeric)

Description: Natural logarithm.

Return type: same as the input.

Example:

1
2
3
4
5
 
SELECT ln(2.0);
        ln         
-------------------
 0.6931471805599453
(1 row)

log(double precision or numeric)

Description: Logarithm to base 10.

  • In ORA and TD compatibility modes, it behaves as a logarithm to base 10.
  • In MySQL-compatible mode, it behaves as a natural logarithm.

Return type: same as the input.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
-- ORA compatibility mode
SELECT log(100.0);
        log         
--------------------
 2.0000000000000000
(1 row)
-- TD compatibility mode
SELECT log(100.0);
        log
--------------------
 2.0000000000000000
(1 row)
-- MySQL compatibility mode
SELECT log(100.0);
        log
--------------------
 4.6051701859880914
(1 row)

log(b numeric, x numeric)

Description: Logarithm with b as the base.

Return type: numeric.

Example:

1
2
3
4
5
 
SELECT log(2.0, 64.0);
        log         
--------------------
 6.0000000000000000
(1 row)

mod(x,y)

Description: Remainder (modulo) of x/y. If x is 0, 0 is returned. If y is 0, x is returned.

Return type: same as the parameter type.

Example:

1
2
3
4
5
 
SELECT mod(9,4);
 mod 
-----
   1
(1 row)

1
2
3
4
5
 
SELECT mod(9,0);
 mod 
-----
   9
(1 row)

pi()

Description: π constant.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT pi();
        pi
------------------
 3.14159265358979
(1 row)

power(a double precision, b double precision)

Description: a raised to the power of b.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT power(9.0, 3.0);
        power         
----------------------
 729.0000000000000000
(1 row)

round(double precision or numeric)

Description: Closest integer to the input parameter.

Return type: same as the input.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
SELECT round(42.4);
 round 
-------
    42
(1 row)

SELECT round(42.6);
 round 
-------
    43
(1 row)

When calling the round function, numeric types will round towards zero, while (on most computers) real and double-precision types will result in the nearest even number.

round(v numeric, s int)

Description: Retains s decimal places, rounding the next digit.

Return type: numeric.

Example:

1
2
3
4
5
 
SELECT round(42.4382, 2);
 round
-------
 42.44
(1 row)

setseed(double precision)

Description: Sets the seed for subsequent random() calls (from –1.0 to 1.0).

Return type: void.

Example:

1
2
3
4
5
 
SELECT setseed(0.54823);
 setseed
---------

(1 row)

sign(double precision or numeric)

Description: Outputs the sign of this parameter.

Return type: -1 indicates a negative number, 0 indicates 0, and 1 indicates a positive number.

Example:

1
2
3
4
5
 
SELECT sign(-8.4);
 sign 
------
   -1
(1 row)

sin(x)

Description: Sine.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT sin(1.57079);
       sin        
------------------
 0.999999999979986
(1 row)

sqrt(double precision or numeric)

Description: Square root.

Return type: same as the input.

Example:

1
2
3
4
5
 
SELECT sqrt(2.0);
       sqrt        
-------------------
 1.414213562373095
(1 row)

tan(x)

Description: Tangent.

Return type: double precision.

Example:

1
2
3
4
5
 
SELECT tan(20);
       tan        
------------------
 2.23716094422474
(1 row)

trunc(double precision or numeric)

Description: Truncates (takes the integer part).

Return type: same as the input.

Example:

1
2
3
4
5
 
SELECT trunc(42.8);
 trunc 
-------
    42
(1 row)

trunc(v numeric, s int)

Description: Truncates to s decimal places.

Return type: numeric.

Example:

1
2
3
4
5
 
SELECT trunc(42.4382, 2);
 trunc
-------
 42.43
(1 row)

truncate(v numeric, s int)

Description: Truncates v to s decimal places. v can be of any precision, integer, or floating-point type. The return value type is the same as that of the input parameter v. When s is negative, the integer part is truncated. This function is only supported by clusters of version 8.2.0 or later.

Return type: numeric.

Example:

1
2
3
4
5
 
SELECT truncate(42.4382, 2);
 truncate
-------
 42.4300
(1 row)

width_bucket(operand numeric, b1 numeric, b2 numeric, count int)

Description: Sets the minimum value, maximum value, and number of groups to construct a specified number of equally sized groups, returning the group number into which the specified field value falls. b1 is the minimum value of the group range, b2 is the maximum value of the group range, and count is the number of groups.

Return type: integer.

Example:

1
2
3
4
5
 
SELECT width_bucket(5.35, 0.024, 10.06, 5);
 width_bucket
--------------
            3
(1 row)

width_bucket(operand double precision, b1 double precision, b2 double precision, count int)

Description: Sets the minimum value, maximum value, and number of groups to construct a specified number of equally sized groups, returning the group number into which the specified field value falls. b1 is the minimum value of the group range, b2 is the maximum value of the group range, and count is the number of groups.

Return type: integer.

Example:

1
2
3
4
5
 
SELECT width_bucket(5.35, 0.024, 10.06, 5);
 width_bucket
--------------
            3
(1 row)