Mathematical Function
Last Updated：2021-04-13
Palo supports the following mathematical functions:
1.sin
2.asin
3.tan
4.atan
5.cos
6.acos
7.abs
8.bin
9.ceil
10.floor
11.conv
12.degrees
13.e
14.exp
15.mod
16.fmod
17.pmod
18.greatest
19.least
20.hex
21.unhex
22.ln
23.dlog1
24.log
25.negative
26.positive
27.pi
28.pow
29.radians
30.rand
31.round
32.sign
33.sqrt
34.truncate
SIN
Description
sin(double a)- Function: return the sine value of a
- Return type: double type
Example
mysql> select sin(1), sin(0.5 * pi());
+--------------------+-----------------+
| sin(1.0) | sin(0.5 * pi()) |
+--------------------+-----------------+
| 0.8414709848078965 | 1 |
+--------------------+-----------------+Keywords
sinASIN
Description
asin(double a)- Function: arc-sin function, a must be between -1 and 1.
- Return type: double type
Example
mysql> select asin(0.8414709848078965), asin(2);
+--------------------------+-----------+
| asin(0.8414709848078965) | asin(2.0) |
+--------------------------+-----------+
| 1 | nan |
+--------------------------+-----------+Keywords
asinTAN
Description
tan(double a)- Function: arctangent value function
- Return type: double type
Example
mysql> select tan(pi()/4);
+---------------------+
| tan(pi() / 4.0) |
+---------------------+
| 0.99999999999999989 |
+---------------------+Keywords
tanATAN
Description
atan(double a)- Function: arctangent value function
- Return type: double type
Example
mysql> SELECT ATAN(1.5574077246549023),ATAN(0);
+--------------------------+---------+
| ATAN(1.5574077246549023) | ATAN(0) |
+--------------------------+---------+
| 1 | 0 |
+--------------------------+---------+Keywords
atanCOS
Description
cos(double a)- Function: return the cosine value of parameters
- Return type: double type
Example
mysql> select cos(1), cos(0), cos(pi());
+---------------------+----------+-----------+
| cos(1.0) | cos(0.0) | cos(pi()) |
+---------------------+----------+-----------+
| 0.54030230586813977 | 1 | -1 |
+---------------------+----------+-----------+Keywords
cosACOS
Description
acos(double a)- Function: arc-cosine function, a must be between -1 and 1.
- Return type: double type
Example
mysql> select acos(2), acos(1), acos(-1);
+-----------+-----------+--------------------+
| acos(2.0) | acos(1.0) | acos(-1.0) |
+-----------+-----------+--------------------+
| nan | 0 | 3.1415926535897931 |
+-----------+-----------+--------------------+Keywords
acosABS
Description
abs(numeric a)- Function: return the absolute value of the parameter
- Return type: numeric type
Example
mysql> select abs(-1.2);
+-----------+
| abs(-1.2) |
+-----------+
| 1.2 |
+-----------+
mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
| 10 |
+----------+Keywords
absBIN
Description
bin(bigint a)- Function: return the binary representation of an integer (that is, a sequence of zeros and ones)
- Return type: string type
Example
mysql> select bin(10);
+---------+
| bin(10) |
+---------+
| 1010 |
+---------+Keywords
binCEIL,CEILING,DCEIL
Description
ceil(double a)
ceiling(double a)
dceil(double a)- Function: return the smallest integer greater than or equal to the parameter
- Return type: int type
Example
mysql> select dceil(1.2), ceiling(1.2), ceil(1.2);
+------------+--------------+-----------+
| dceil(1.2) | ceiling(1.2) | ceil(1.2) |
+------------+--------------+-----------+
| 2 | 2 | 2 |
+------------+--------------+-----------+Keywords
dceil, ceiling, ceilFLOOR
Description
floor(double a)
dfloor(double a)- Function: return the max integer less than or equal to this parameter
- Return type: int type
Example
mysql> select floor(2.9);
+------------+
| floor(2.9) |
+------------+
| 2 |
+------------+
mysql> select dfloor(2.9);
+-------------+
| dfloor(2.9) |
+-------------+
| 2 |
+-------------+Keywords
floor, dfloorCONV
Description
conv(bigint num, int from_base, int to_base)
conv(string num,int from_base, int to_base)- Function: it is a function for converting number systems, which returns the string form of an integer in a specific number system. The input parameters can be integer strings. To convert the return value of a function into an integer,
CASTfunction can be used. - Return type: string type
Example
mysql> select conv(64,10,8);
+-----------------+
| conv(64, 10, 8) |
+-----------------+
| 100 |
+-----------------+
mysql> select cast(conv('fe', 16, 10) as int) as "transform_string_to_int";
+-------------------------+
| transform_string_to_int |
+-------------------------+
| 254 |
+-------------------------+Keywords
convDEGREES
Description
degrees(double a)- Function: convert radians into angles
- Return type: double type
Example
mysql> select degrees(pi());
+---------------+
| degrees(pi()) |
+---------------+
| 180 |
+---------------+Keywords
degreesE
Description
e()- Function: return the mathematical constant e
- Return type: double type
Example
mysql> select e();
+--------------------+
| e() |
+--------------------+
| 2.7182818284590451 |
+--------------------+Keywords
eEXP,DEXP
Description
exp(double a)
dexp(double a)- Function: return the a power of e
- Return type: double type
Example
mysql> select exp(2);
+------------------+
| exp(2.0) |
+------------------+
| 7.38905609893065 |
+------------------+
mysql> select dexp(2);
+------------------+
| dexp(2.0) |
+------------------+
| 7.38905609893065 |
+------------------+Keywords
exp, dexpMOD
Description
mod(numeric_type a, same_type b)- Function: return the remainder of a divided by b, equivalent to % arithmetic operator.
- Return type: same with the input type
Example
mysql> select mod(10,3);
+------------+
| mod(10, 3) |
+------------+
| 1 |
+------------+
mysql> select mod(5.5,2);
+-------------+
| mod(5.5, 2) |
+-------------+
| 1.5 |
+-------------+Keywords
modFMOD
Description
fmod(double a, double b)
fmod(float a, float b)- Function: return the remainder of a divided by b, which is equivalent to % arithmetic operator
- Return type: float or double type
Example
mysql> select fmod(10,3);
+-----------------+
| fmod(10.0, 3.0) |
+-----------------+
| 1 |
+-----------------+
mysql> select fmod(5.5,2);
+----------------+
| fmod(5.5, 2.0) |
+----------------+
| 1.5 |
+----------------+Keywords
fmodPMOD
Description
pmod(int a, int b)
pmod(double a, double b)- Function: positive complementary function
- Return type: int type or double type (depending on input parameters)
Example
mysql> select pmod(3, 2), pmod(1.1, 2);
+------------+------------+
| pmod(3, 2) | pmod(1, 2) |
+------------+------------+
| 1 | 1 |
+------------+------------+Keywords
pmodGREATEST
Description
greatest(bigint a[, bigint b ...])
greatest(double a[, double b ...])
greatest(decimal(p,s) a[, decimal(p,s) b ...])
greatest(string a[, string b ...])
greatest(timestamp a[, timestamp b ...])- Function: return the maximum value in the list
- Return type: same with the parameter type
Example
mysql> select greatest(1,2,3);
+-------------------+
| greatest(1, 2, 3) |
+-------------------+
| 3 |
+-------------------+
mysql> select greatest("a", "b", "c");
+-------------------------+
| greatest('a', 'b', 'c') |
+-------------------------+
| c |
+-------------------------+Keywords
greatestLEAST
Description
least(bigint a[, bigint b ...])
least(double a[, double b ...])
least(decimal(p,s) a[, decimal(p,s) b ...])
least(string a[, string b ...])
least(timestamp a[, timestamp b ...])- Function: return the minimum value in the list
- Return type: same with the parameter type
Example
mysql> select least(1,2,3);
+----------------+
| least(1, 2, 3) |
+----------------+
| 1 |
+----------------+
mysql> select least("a", "b", "c");
+----------------------+
| least('a', 'b', 'c') |
+----------------------+
| a |
+----------------------+Keywords
leastHEX
Description
hex(bigint a)
hex(string a)- Function: return the hexadecimal representation of an integer or each character in a string.
- Return type: string type
Example
mysql> select hex('abc');
+------------+
| hex('abc') |
+------------+
| 616263 |
+------------+
mysql> select unhex(616263);
+---------------+
| unhex(616263) |
+---------------+
| abc |
+---------------+Keywords
hexUNHEX
Description
unhex(string a)- Function: convert a string in hexadecimal format to its original format
- Return type: string type
Example
mysql> select hex('abc');
+------------+
| hex('abc') |
+------------+
| 616263 |
+------------+
mysql> select unhex(616263);
+---------------+
| unhex(616263) |
+---------------+
| abc |
+---------------+Keywords
unhexLN
Description
ln(double a)- Function: return the natural logarithm of 2
- Return type: double type
Example
mysql> select ln(2);
+---------------------+
| ln(2.0) |
+---------------------+
| 0.69314718055994529 |
+---------------------+Keywords
lnDLOG1
Description
dlog1(double a)- Function: return the natural logarithmic form of the parameter
- Return type: double type
Example
mysql> select dlog1(2);
+---------------------+
| dlog1(2.0) |
+---------------------+
| 0.69314718055994529 |
+---------------------+Keywords
dlog1LOG,LOG10,DLOG10,LOG2
Description
log(double base, double a)- Function: return the logarithmic value of log with base as base number and a as exponent.
- Return type: double type
log10(double a)
dlog10(double a)- Function: return the logarithmic value of log with 10 as base number and a as exponent.
- Return type: double type
log2(double a)- Function: return the logarithmic value of log with 2 as base number and a as exponent.
- Return type: double type
Example
mysql> select log(2, 65536);
+-------------------+
| log(2.0, 65536.0) |
+-------------------+
| 16 |
+-------------------+
mysql> select log10(2);
+--------------------+
| log10(2.0) |
+--------------------+
| 0.3010299956639812 |
+--------------------+
mysql> select dlog10(2);
+--------------------+
| dlog10(2.0) |
+--------------------+
| 0.3010299956639812 |
+--------------------+
mysql> select log2(2);
+-----------+
| log2(2.0) |
+-----------+
| 1 |
+-----------+Keywords
log, log10, dlog, log2NEGATIVE
Description
negative(int a)
negative(double a)- Function: take the negative sign bit of parameter a, and return a positive value if the parameter is negative value
- Return type: return the int type or double type according to the input parameter type
- Instructions for use: if you need to ensure that all return values are negative, you can use
-abs(a)function.
Example
mysql> select negative(1.0);
+---------------+
| negative(1.0) |
+---------------+
| -1 |
+---------------+
1 row in set (0.02 sec)
mysql> select negative(-1);
+--------------+
| negative(-1) |
+--------------+
| 1 |
+--------------+Keywords
negativePOSITIVE
Description
positive(int a)- Function: return the original value of parameters, even if the parameter is negative.
- Return type: int type
- Instructions for use: If you need to ensure that all return values are positive, you can use the
abs()function.
Example
mysql> select positive(-1), positive(1);
+--------------+-------------+
| positive(-1) | positive(1) |
+--------------+-------------+
| -1 | 1 |
+--------------+-------------+Keywords
positivePI
Description
pi()- Function: return the constant Pi
- Return type: double type
Example
mysql> select pi();
+--------------------+
| pi() |
+--------------------+
| 3.1415926535897931 |
+--------------------+Keywords
piPOW,POWER,DPOW,FPOW
Description
pow(double a, double p)
power(double a, double p)
dpow(double a, double p)
fpow(double a, double p)- Function: return the p power of a
- Return type: double type
Example
mysql> select pow(2, 10), power(2, 10), dpow(2, 10), fpow(2, 10);
+----------------+------------------+-----------------+-----------------+
| pow(2.0, 10.0) | power(2.0, 10.0) | dpow(2.0, 10.0) | fpow(2.0, 10.0) |
+----------------+------------------+-----------------+-----------------+
| 1024 | 1024 | 1024 | 1024 |
+----------------+------------------+-----------------+-----------------+Keywords
POW, POWER, DPOW, FPOWRADIANS
Description
radians(double a)- Function: convert radians into angles
- Return type: double type
Example
mysql> select radians(90);
+--------------------+
| radians(90.0) |
+--------------------+
| 1.5707963267948966 |
+--------------------+Keywords
radiansRAND,RANDOM
Description
rand()
random()- Function: return a random value from 0 to 1.
- Return type: double
Example
mysql> select rand(), rand(), random();
+---------------------+---------------------+---------------------+
| rand() | rand() | random() |
+---------------------+---------------------+---------------------+
| 0.39794450929180808 | 0.34321919244300736 | 0.38788449829415106 |
+---------------------+---------------------+---------------------+Keywords
rand, randomROUND
Description
round(double a)
round(double a, int d)- Function: it is a function for rounding. If it contains only one parameter, the function returns the nearest integer to the value. If it contains 2 parameters, the second parameter is the number of digits retained after the decimal point.
- Return type: if the parameter is floating point type, then the function will return bigint. If the second parameter is greater than 1, then the function will return double type.
Example
mysql> select round(100.456, 2);
+-------------------+
| round(100.456, 2) |
+-------------------+
| 100.46 |
+-------------------+Keywords
roundSIGN
Description
sign(double a)- Function: If a is an integer or 0, it will return 1; If a is negative, it will return -1
- Return type: int type
Example
mysql> select sign(-1), sign(1.2);
+------------+-----------+
| sign(-1.0) | sign(1.2) |
+------------+-----------+
| -1 | 1 |
+------------+-----------+Keywords
signSQRT,DSQRT
Description
sqrt(double a)
dsqrt(double a)- Function: return the square root of a
- Return type: double type
Example
mysql> select sqrt(4), dsqrt(10);
+-----------+--------------------+
| sqrt(4.0) | dsqrt(10.0) |
+-----------+--------------------+
| 2 | 3.1622776601683795 |
+-----------+--------------------+Keywords
sqrt, dsqrtTRUNCATE
Description
truncate(double num, int len)- Function: intercept num and retain the specified decimal places of len
- Return type: double type
Example
select truncate(1.1234, 2);
+---------------------+
| truncate(1.1234, 2) |
+---------------------+
| 1.12 |
+---------------------+Keywords
truncate