Updated on 2022-11-18 GMT+08:00

Mathematical Functions and Operators

Mathematical Operator

Operator

Description

+

Add

-

Deduct

*

Multiple

/

Divide

%

Remainder

Mathematical Functions

  • abs(x) → [same as input]

    Returns the absolute value of x.

    SELECT abs(-17.4);-- 17.4 
  • bin(bigint x) -> string

    Returns x in binary format.

    select bin(5); --101
  • bround(double x) -> double

    Banker's rounding:

    • 1 to 4: rounding down
    • 6 to 9: rounding up
    • The number before 5 is even: rounding down
    • The number before 5 is odd: rounding up
    select bround(3.5); -- 4.0
    select bround(2.5); -- 2.0
    select bround(3.4); -- 3.0
  • bround(double x, int y) -> double

    Banker's rounding with y decimal places reserved.

    select bround(8.35,1); --8.4
    select bround(8.355,2); --8.36
  • ceil(x) → [same as input]

    Same as ceiling()

    SELECT ceil(-42.8); -- -42

    ceiling(x) → [same as input]

    Returns the rounded-up value of x.

    SELECT ceiling(-42.8); -- -42 
  • conv(bigint num, int from_base, int to_base)
  • conv(string num, int from_base, int to_base)

    Converts num, for example, from decimal to binary.

    select conv('123',10,2); -- 1111011
  • rand() → double

    Returns a random decimal number between 0 and 1.

    select rand();--  0.049510824616263105
  • cbrt(x) → double

    Returns the cube root of x.

    SELECT cbrt(27.0); -- 3
  • e() → double

    Returns the Euler constant.

    select e();-- 2.718281828459045 
  • exp(x) → double

    Returns the value of e raised to the power of x.

    select exp(1);--2.718281828459045 
  • factorial(int x) -> bigint

    Returns the factorial of x. The value range of x is [0, 20].

    select factorial(4); --24
  • floor(x) → [same as input]

    Returns the nearest integer rounded off from x.

    SELECT floor(-42.8);-- -43
  • from_base(string, radix) → bigint

    Converts a specified number system to bigint. For example, converts the ternary number 200 to a decimal number.

    select from_base('200',3);--18  
  • hex(bigint|string|binary x) -> string

    Returns a hexadecimal number as a string if x is of the int or binary type. If x is a string, converts each character of the string to a hexadecimal representation and returns a string.

    select hex(68); -- 44
    select hex('AE'); -- 4145
  • to_base(x, radix) → varchar

    Converts an integer into a character string in the radix system. For example, converts the decimal number 18 to a ternary number.

    select to_base(18,3);-- 200  
  • ln(x) → double

    Returns the natural logarithm of x.

    select ln(10);--2.302585092994046
    select ln(e());--1.0
  • log2(x) → double

    Returns the logarithm of x to base 2.

    select log2(4);-- 2.0
  • log10(x) → double

    Returns the logarithm of x to base 10.

    select log10(1000);-- 3.0
  • log(x, b) → double

    Returns the logarithm of x to base b.

    select log(3,81); -- 4.0
  • mod(n, m) → [same as input]

    Returns the modulus of n divided by m.

    select mod(40,7) ;-- 5
    select mod(-40,7);  -- -5
  • pi() → double

    Returns pi.

    select pi();--3.141592653589793
  • pmod(int x,int y) -> int
  • pmod(double x,double y) -> double

    Returns the positive value of the remainder after division of x by y.

    select pmod(8,3); --2
    Select pmod(8.35,2.0); --0.34999999999999964
  • pow(x, p) → double

    Same as power().

    select pow(3.2,3);-- 32.76800000000001
  • power(x,p)

    Returns the value of x raised to the power of p.

    select power(3.2,3);-- 32.76800000000001  
  • radians(x) → double

    Converts the angle x to a radian.

     select radians(57.29577951308232);-- 1.0
  • degrees(x) → double

    Converts an angle x (represented by a radian) into an angle.

    select degrees(1);-- 57.29577951308232
  • round(x) → [same as input]

    Return the integer that is rounded to the nearest integer of x.

    select round(8.57);-- 9
  • round(x, d) → [same as input]

    x is rounded off to d decimal places.

    select round(8.57,1);-- 8.60
  • shiftleft(tinyint|smallint|int x, int y) -> int
  • shiftleft(bigint x, int y) -> bigint

    Returns the value of x shifted leftwards by y positions.

    select shiftleft(8,2);--32
  • shiftright(tinyint|smallint|int a, int b) -> int
  • shiftright(bigint a, int b) -> bigint

    Returns the value of x shifted rightwards by y positions.

    select shiftright(8,2);--2
  • shiftrightunsigned(tinyint|smallint|int x, int y) -> int
  • shiftrightunsigned(bigint x, int y) -> bigint

    Shifts to the right by bit without symbols, and returns the value of x shifted rightwards by y positions. Returns an int if x is tinyint, smallint, or int. Returns a bigint if x is bigint.

    select shiftrightunsigned(8,3); -- 1
  • sign(x) → [same as input]

    Returns the symbol function of x.

    • If x is equal to 0, 0 is returned.
    • If x is less than 0, the value –1 is returned.
    • If x is greater than 0, 1 is returned.
    select sign(-32.133);-- -1
    select sign(32.133); -- 1
    select sign(0);--0

    For parameters of the double type:

    • If the parameter is NaN, NaN is returned.
    • If the parameter is +∞, 1 is returned.
    • If the parameter is -∞, -1 is returned.
    select sign(NaN());--NaN
    select sign(Infinity());-- 1.0
    select  sign(-infinity());-- -1.0
  • sqrt(x) → double

    Returns the square root of x.

    select sqrt(100); -- 10.0
  • truncate(number,num_digits)
    • Number indicates the number to be truncated, and Num_digits indicates the decimal places retained.
    • The default value of Num_digits is 0.
    • The truncate() function does not round off the result.
    select truncate(10.526); -- 10
    select truncate(10.526,2); --  10.520
  • trunk(number,num_digits)

    See truncate(number,num_digits).

  • unhex(string x) -> binary

    Returns the reciprocal of a hexadecimal number.

    select unhex('123'); --^A#
  • width_bucket(x, bound1, bound2, n) → bigint

    Returns the number of containers x in the equi-width histogram with the specified bound1 and bound2 boundaries and n buckets.

    select value,width_bucket(value,1,5000,10) from (values (1),(100),(500),(1000),(2000),(2500),(3000),(4000),(4500),(5000),(8000)) as t(value);
    value | _col1 
    -------|-------
         1 |     1 
       100 |     1 
       500 |     1 
      1000 |     2 
      2000 |     4 
      2500 |     5 
      3000 |     6 
      4000 |     8 
      4500 |     9 
      5000 |    11 
      8000 |    11
    (11 rows)
  • width_bucket(x, bins) → bigint

    Returns the number of bins of x based on the bin specified by the array bin. The bins parameter must be a double-precision array and is assumed to be in ascending order.

    select width_bucket(x,array [1.00,2.89,3.33,4.56,5.87,15.44,20.78,30.77]) from (values (3),(4)) as t(x);
     _col0 
    -------
         2 
         3 
    (2 rows)
  • quotient(BIGINT numerator, BIGINT denominator)→bigint

    Returns the value of the left number divided by the right number. Part of the decimal part is discarded.

    select quotient(25,4);-- 6

Random

  • rand() → double

    Same as random()

  • random() → double

    Returns a pseudo-random value in the range of 0.0 <= x < 1.0.

    select random();-- 0.021847965885988363
    select random();-- 0.5894438037549372
  • random(n) → [same as input]

    Returns a pseudo-random number between 0 and n (excluding n).

    select random(5);-- 2

random(n) contains the following data types: tinyint, bigint, smallint and integer.

Statistical Function

The binomial distribution confidence interval has multiple calculation formulas, and the most common one is ["normal interval"]. However,it is applicable only to a case in which there are a relatively large quantity of samples (np > 5 and n(1 p) > 5). For a small sample, the accuracy is poor. Therefore, the Wilson interval is used.

z —— normal distribution, average value + z x standard deviation confidence. z = 1.96, confidence level: 95%

Take, for example, the collecting of positive rate. pos indicates the number of positive reviews; n indicates the total number of reviews; and phat indicates the positive review rate.

z = 1.96

phat= 1.0* pos/n

z1=phat + z * z/(2 * n)

z2 =

m = (1 + z * z/n)

Lower limit (z1-z2)/m, upper limit (z1+z2)/m

  • wilson_interval_lower(successes, trials, z) → double

    Returns the lower bound of the Wilson score interval for the Bernoulli test process. The confidence value is specified by the z-score z.

    select wilson_interval_lower(1, 5, 1.96);-- 0.036223160969787456
  • wilson_interval_upper(successes, trials, z) → double

    Returns the upper bound of the Wilson score interval for the Bernoulli test process. The confidence value is specified by the z-score z.

     select wilson_interval_upper(1, 5, 1.96);--  0.6244717358814612 
  • cosine_similarity(x, y) → double

    Returns the cosine similarity between sparse vectors x and y.

    SELECT cosine_similarity (MAP(ARRAY['a'],ARRAY[1.0]),MAP(ARRAY['a'],ARRAY[2.0]));-- 1.0

Cumulative Distribution Function

  • beta_cdf(a, b, v) → double

Use the given a and b parameters to calculate the cumulative distribution function (P (N <v; a, b)) of the beta distribution. Parameters a and b must be positive real numbers, and the value v must be a real number. The value v must be within the interval [0, 1].

A cumulative distribution function formula of beta distribution is also referred to as an incomplete beta function ratio (which is usually represented by Ix), and corresponds to the following formula:

 select beta_cdf(3,4,0.0004); --  1.278848368599041E-9 
  • inverse_beta_cdf(a, b, p) → double

    The inverse operation of the beta cumulative distribution function, given the a and b parameters of the cumulative probability p: P (N < n). Parameters a and b must be positive real numbers, and p must be within the range of [0,1].

    select inverse_beta_cdf(2, 5, 0.95) ;--0.5818034093775719 
  • inverse_normal_cdf(mean, sd, p) → double

    Given the cumulative probability (p): P (N < n) related mean and standard deviation, calculate the inverse of the normal cumulative distribution function. The average value must be a real value, and the standard deviation must be a positive real value. The probability p must be in the interval (0, 1).

    select inverse_normal_cdf(2, 5, 0.95);-- 10.224268134757361
  • normal_cdf(mean, sd, v) → double

    Calculate the value of the normal distribution function based on the average value and standard deviation. P(N<v; mean,sd). The average value and v must be real values, and the standard deviation must be positive real values.

     select normal_cdf(2, 5, 0.95);--  0.4168338365175577

Trigonometric Function

The parameters of all trigonometric functions are expressed in radians. Refer to the unit conversion functions degrees() and radians().

  • acos(x) → double

    Calculates the arc cosine value.

    SELECT acos(-1);-- 3.14159265358979
  • asin(x) → double

    Calculates the arc sine value.

    SELECT asin(0.5);-- 0.5235987755982989
  • atan(x) → double

    Returns the arc tangent value of x.

    SELECT atan(1);-- 0.7853981633974483
  • atan2(y, x) → double

    Return the arc tangent value of y/x.

    SELECT atan2(2,1);-- 1.1071487177940904
  • cos(x) → double

    Returns the cosine value of x.

    SELECT cos(-3.1415927);-- -0.9999999999999989 
  • cosh(x) → double

    Returns the hyperbolic cosine value of x.

    SELECT cosh(3.1415967);-- 11.592000006553231
  • sin(x) → double

    Returns the sine value of x.

    SELECT sin(1.57079);--  0.9999999999799858 
  • tan(x) → double

    Returns the tangent value of x.

    SELECT tan(20);-- 2.23716094422474
  • tanh(x) → double

    Returns the hyperbolic tangent value of x.

    select tanh(3.1415927);-- 0.9962720765661324 

Floating-Point Function

  • infinity() → double

    Returns a constant representing positive infinity.

    select infinity();-- Infinity
  • is_finite(x) → boolean

    Checks whether x is a finite value.

    select is_finite(infinity());-- false
    select is_finite(50000);--true
  • is_infinite(x) → boolean

    Determines whether x is infinite.

    select is_infinite(infinity());-- true
    select is_infinite(50000);--false
  • is_nan(x) → boolean

    Checks whether x is a non-digit character.

    -- The input value must be of the double type.
    select is_nan(null); -- NULL
    select is_nan(nan()); -- true
    select is_nan(45);-- false
  • nan() → double

    Returns a constant representing a non-numeric number.

    select nan(); -- NaN