On this page

Show all

Help Center/ MapReduce Service/ Component Operation Guide (LTS)/ Using HetuEngine/ Common HetuEngine SQL Syntax/ HetuEngine SQL Functions and Operators/ Mathematical Functions and Operators

Mathematical Functions and Operators

Updated on 2024-12-13 GMT+08:00

View PDF

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(b, x) → 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.35
```
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
```
trunc(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
```

NOTICE:

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. To solve this problem, the Wilson Score Interval is used.

Click to enlarge

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 = $\text{[math]}$

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:

Click to enlarge

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

Parent topic: HetuEngine SQL Functions and Operators

Previous topic: Conversion Functions

Next topic: Bitwise Functions

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot