Data Lake Analytics - Deprecated:Mathematical functions

• ABS: returns the absolute value of a number.

• CBRT: returns the cube root of a number.

• CEILING/CEIL: rounds up a number to the nearest integer.

• DEGREES: converts radians into degrees.

• E: returns the constant Euler’s number.

• EXP: returns Euler’s number raised to the power of x.

• FLOOR: rounds down a number to the nearest integer.

• FROM_BASE: returns the value of the string interpreted as a base-radix number.

• LN: returns the natural logarithm of a number.

• LOG2: returns the logarithm of a number with 2 as the base.

• LOG10: returns the logarithm of a number with 10 as the base.

• LOG: returns the logarithm of x with b as the base.

• MOD: returns the remainder.

• PI: returns the constant pi.

• POWER/POW: returns x raised to the power of p.

• RADIANS: converts angle x in degrees to radians.

• RANDOM/RAND: returns a pseudo-random value.

• ROUND: returns x rounded to the nearest integer, or returns x rounded to d decimal places.

• SIGN: returns the signum function of x. The result may be -1, 0, or 1.

• SQRT: returns the square root of a number.

• TO_BASE: returns the base-radix representation of x.

• TRUNCATE: returns x rounded to an integer by dropping digits after the decimal point.

• WIDTH_BUCKET: returns the value of x in its range after the range delimited by the minimum value bound1 and maximum value bound2 is divided into n equal parts.

• ACOS: returns the arc cosine of a number.

• ASIN: returns the arc sine of a number.

• ATAN: returns the arc tangent of a number.

• ATAN2: returns the arc tangent of parameter 1 divided by parameter 2.

• COS: returns the cosine of a number.

• COSH: returns the hyperbolic cosine of a number.

• SIN: returns the sine of a number.

• TAN: returns the tangent of a number.

• TANH: returns the hyperbolic tangent of a number.

• COT: returns the cotangent function of a number.

• INFINITY: returns a constant that represents positive infinity.

• IS_FINITE: determines whether x is finite.

• IS_INFINITE: determines whether x is infinite.

• IS_NAN: determines whether x is not-a-number.

• NAN: returns the constant representing not-a-number.

• CRC32: calculates the cyclic redundancy check value and returns a 32-bit unsigned value. If the parameter is NULL, NULL is returned.

• REMAINDER: returns the remainder of one parameter divided by another parameter.

• BITAND: returns the result of the bitwise AND operation for two numeric values.

Note: All trigonometric functions are expressed in radians.

ABS

• Command Description: This command returns the absolute value of a number.

• Return Value Type: the same as the input value type.

• Example:

   select abs(-9); +-------------+ | _col0 | +-------------+ | 9 |

CBRT

cbrt(double)

• Command Description: This command returns the cube root of a number.

• Return value type: DOUBLE.

• Example:

   select cbrt(8); +-------+ | _col0 | +-------+ | 2.0 |

CEILING/CEIL

ceiling(x)

• command description: This command rounds up a number to an integer.

• Return value type: LONG.

• Example:

   select ceiling(2.3); +-------+ | _col0 | +-------+ | 3 |

DEGREES

degrees(double)

• Command Description: This command converts radians into degrees.

• Return value type: DOUBLE.

• Example:

   select degrees(1.3); +-------------------+ | _col0 | +-------------------+ | 74.48451336700703 |

E

e();

• Command Description: This command returns the constant Euler’s number.

• Return value type: DOUBLE.

• Example:

   select e(); +-------------------+ | _col0 | +-------------------+ | 2.718281828459045 |

EXP

exp(x);

• Command description: This command returns Euler’s number raised to the power of x.

• Return value type: DOUBLE.

• Example:

   select exp(2); +-------------------+ | _col0 | +-------------------+ | 7.38905609893065 |

FLOOR

floor(int) floor(bigint) floor(double)

• Command description: This command rounds down a number to an integer.

• Return value type: LONG.

• Example:

   select floor(7.8); +-------+ | _col0 | +-------+ | 7 |

FROM_BASE

from_base(string, radix);

• Command description: This command returns the value of the string interpreted as a base-radix number.

• Return value type: BIGINT.

• Example:

   SELECT from_base('0110', 2); +-------+ | _col0 | +-------+ | 6 | SELECT from_base('0110', 8); +-------+ | 72 | SELECT from_base('00a0', 16); +-------+ | _col0 | +-------+ | 160|

LN

ln(double)

• Command Description: This command returns the natural logarithm of a number.

• Return value type: DOUBLE.

• Example:

   select ln(2.718281828459045); +-------+ | _col0 | +-------+ | 1.0 |

LOG2

log2(double)

• Command Description: This command returns the logarithm of a number with 2 as the base.

• Return value type: DOUBLE.

• Example:

   select log2(8); +-------+ | _col0 | +-------+ | 3.0 |

LOG10

log10(double)

• Command Description: This command returns the logarithm of a number with 10 as the base.

• Return value type: DOUBLE.

• Example:

   select log10(100); +-------+ | _col0 | +-------+ | 2.0 |

LOG

log(x, b)

• Command description: This command returns the logarithm of x with b as the base.

• Return value type: DOUBLE.

• Example:

   select log(10,100); +-------+ | _col0 | +-------+ | 2.0 |

MOD

• Command Description: This command returns the remainder.

• Example:

   SELECT mod(cast(4.5 as tinyint), 3); +-------+ | _col0 | +-------+ | 2 | SELECT mod(cast(4.5 as smallint), 3); +-------+ | _col0 | +-------+ | 2 | SELECT mod(cast(4.5 as int), 3); +-------+ | _col0 | +-------+ | 2 | SELECT mod(cast(4.5 as bigint), 3); +-------+ | _col0 | +-------+ | 2 | SELECT mod(cast(4.5 as double), 3); +-------+ | _col0 | +-------+ | 1.5 |

PI

pi()

• Command description: This command returns the constant pi.

• Return value type: DOUBLE.

• Example:

   select pi(); +-------------------+ | _col0 | +-------------------+ | 3.141592653589793 |

POWER/POW

power(x, p)

• Command description: This command returns x raised to the power of p.

• Return value type: DOUBLE.

• Example:

   select power(1.2,3.4); +-------------------+ | _col0 | +-------------------+ | 1.858729691979481 |

RADIANS

radians(x)

• Command description: This command converts angle x in degrees to radians.

• Return value type: DOUBLE.

• Example:

   select radians(60.0); +--------------------+ | _col0 | +--------------------+ | 1.0471975511965976 |

RANDOM/RAND

random() random(n)

• Command description: This command returns a pseudo-random value.

• Return value type: DOUBLE.

• Example:

   select random(); +--------------------+ | _col0 | +--------------------+ | 0.5709993917553757 | select random(cast(3 as tinyint)); +--------------------+ | _col0 | +--------------------+ | 2 | select random(cast(3 as smallint)); +--------------------+ | _col0 | +--------------------+ | 1 | select random(cast(3 as int)); +--------------------+ | _col0 | +--------------------+ | 1 | select random(cast(3 as bigint)); +--------------------+ | _col0 | +--------------------+ | 1 |

ROUND

round(x) round(x, d)

• Command description: This command returns x rounded to the nearest integer, or returns x rounded to d decimal places.

• Return value type: BIGINT or DOUBLE

• Example:

   SELECT round(cast(4.5 as tinyint), 3); +-------+ | _col0 | +-------+ | 5 | SELECT round(cast(4.5 as smallint), 3); +-------+ | _col0 | +-------+ | 5 | SELECT round(cast(4.5 as int), 3); +-------+ | _col0 | +-------+ | 5 | SELECT round(cast(4.5 as bigint), 3); +-------+ | _col0 | +-------+ | 5 | SELECT round(cast(4.5 as double), 3); +-------+ | _col0 | +-------+ | 4.5 | SELECT round(cast(4.5 as tinyint)); +-------+ | _col0 | +-------+ | 5 | SELECT round(cast(4.5 as smallint)); +-------+ | _col0 | +-------+ | 5 | SELECT round(cast(4.5 as double)); +-------+ | _col0 | +-------+ | 5.0 |

SIGN

sign(x)

• Command Description: This command returns the signum function of x. The result may be -1, 0, or 1.

• Example:

   SELECT sign(0); +-------+ | _col0 | +-------+ | 0 | SELECT sign(34); +-------+ | _col0 | +-------+ | 1 | SELECT sign(-23); +-------+ | _col0 | +-------+ | -1 | SELECT sign(1.023); +-------+ | _col0 | +-------+ | 1.0 | SELECT sign(-1.023); +-------+ | _col0 | +-------+ | -1 |

SQRT

sqrt(double)

• Command Description: This command returns the square root of a number.

• Return value type: DOUBLE.

• Example:

   select sqrt(4); +-------+ | _col0 | +-------+ | 2.0 |

TO_BASE

to_base(x, radix)

• Command description: This command returns the base-radix representation of x.

• Return value type: VARCHAR.

• Example:

   SELECT to_base(8,8); +-------+ | _col0 | +-------+ | 10 |

TRUNCATE

truncate(double)

• Command description: This command returns x rounded to integer by dropping digits after decimal point.

• Return value type: DOUBLE.

• Example:

   select truncate(2.3); +-------+ | _col0 | +-------+ | 2.0 | select truncate(2.3456,2); +--------+ | _col0 | +--------+ | 2.3400 | SELECT truncate(cast(4.5 as tinyint), 3); +-------+ | _col0 | +-------+ | 5 | SELECT truncate(cast(4.5 as smallint), 3); +-------+ | _col0 | +-------+ | 5 | SELECT truncate(cast(4.5 as int), 3); +-------+ | _col0 | +-------+ | 5 | SELECT truncate(cast(4.5 as bigint), 3); +-------+ | _col0 | +-------+ | 5 |

WIDTH_BUCKET

width_bucket(x, bound1, bound2, n);

• Command description: This command returns the value of x in its range after the range delimited by the minimum value bound1 and maximum value bound2 is divided into n equal parts.

• Return value type: BIGINT.

• Example:

   SELECT WIDTH_BUCKET(5,3,4,5); +--------------------+ | _col0 | +--------------------+ | 6 |

Trigonometric

All trigonometric functions are expressed in radians. Please refer to 《DEGREES》 and 《RADIANS》 for unit conversion.

ACOS

acos(double)

• Command Description: This command returns the arc cosine of a number.

• Return value type: DOUBLE.

• Example:

   select acos(0.5); +--------------------+ | _col0 | +--------------------+ | 1.0471975511965979 |

ASIN

asin(double)

• Command Description: This command returns the arc sine of a number.

• Return value type: DOUBLE.

• Example:

   select asin(0.5); +--------------------+ | _col0 | +--------------------+ | 0.5235987755982989 |

ATAN

atan(double)

• Command description: This command returns the arc tangent of a number.

• Return value type: DOUBLE.

• Example:

   select atan(0.5); +--------------------+ | _col0 | +--------------------+ | 0.4636476090008061 |

ATAN2

atan2(double, double)

• Command description: This command returns the arc tangent of parameter 1 divided by parameter 2.

• Return value type: DOUBLE.

• Example:

   select atan2(0.5,0.3); +--------------------+ | _col0 | +--------------------+ | 1.0303768265243125 |

COS

cos(double)

• Command Description: This command returns the cosine of a number.

• Return value type: DOUBLE.

• Example:

   select cos(1.3); +---------------------+ | _col0 | +---------------------+ | 0.26749882862458735 |

COSH

cosh(double)

• Command Description: This command returns the hyperbolic cosine of a number.

• Return value type: DOUBLE.

• Example:

   select cosh(1.3); +--------------------+ | _col0 | +--------------------+ | 1.9709142303266285 |

SIN

sin(x);

• command description: This command returns the sine of a number.

• Return value type: DOUBLE.

• Example:

   select sin(1.3); +--------------------+ | _col0 | +--------------------+ | 0.963558185417193 |

TAN

tan(double)

• command description: This command returns the tangent of a number.

• Return value type: DOUBLE.

• Example:

   select tan(8); +--------------------+ | _col0 | +--------------------+ | -6.799711455220379 |

TANH

tanh(double)

• command description: This command returns the hyperbolic tangent of a number.

• Return value type: DOUBLE.

• Example:

   select tanh(8); +--------------------+ | _col0 | +--------------------+ | 0.9999997749296758 |

COT

cot(x);

• command description: This command returns the cotangent function of a number.

• Return value type: DOUBLE.

• Example:

   SELECT COT(12); +--------------------+ | _col0 | +--------------------+ | 1.5726734063976893 |

INFINITY

• Command Description: This command returns a constant that represents positive infinity.

• Return value type: DOUBLE.

IS_FINITE

• Command description: This command determines whether x is finite.

• Return value type: BOOLEAN.

IS_INFINITE

• Command description: This command determines whether x is infinite.

• Return value type: BOOLEAN.

IS_NAN

• Command description: This command determines whether x is not-a-number.

• Return value type: BOOLEAN.

NAN

• Command description: This command returns the constant representing not-a-number.

• Return value type: DOUBLE.

CRC32

CRC32(expr);

• Command Description: This command calculates the cyclic redundancy check value and returns a 32-bit unsigned value. If the parameter is NULL, NULL is returned.

• Return value type: DOUBLE.

• Example:

   SELECT CRC32('MySQL'); +--------------------+ | _col0 | +--------------------+ | 3259397556 | SELECT CRC32('mysql'); +--------------------+ | _col0 | +--------------------+ | 2501908538 |

REMAINDER

REMAINDER(n1, n2);

• Command description: This command returns the remainder of one parameter divided by another parameter.

• Return value type: DOUBLE/BIGINT

• Example:

   SELECT REMAINDER(3.5, 2); +--------------------+ | _col0 | +--------------------+ | -1.5 | SELECT REMAINDER(11, 4); +--------------------+ | _col0 | +--------------------+ | 4 |

BITAND

BITAND(expr1, expr2);

• Command description: This command returns the result of the bitwise AND operation for two numeric values.

  If any of the parameters in a function is not of the integer type, you need to convert the parameter into the integer type before bitwise AND operation.

• Return value type: BIGINT.

• Example:

   SELECT BITAND(5, 6); +--------------------+ | _col0 | +--------------------+ | 4 | SELECT BITAND(cast(5.3 as int),cast(6.2 as int)); +--------------------+ | _col0 | +--------------------+ |4 |