Mathematical Functions and Operators

Mathematical Operator

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 

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