Apache Impala Mathematical Functions

The mathematical functions in Apache Impala come in handy during data analysis, especially when it comes to revenue or monetary calculations. These math functions are applicable to applications that use Trigonometry, Calculus, and Geometry. Arithmetic functions like ABS, CEILING, DEGREES, FLOOR, POWER, RADIANS, and SIGN return the same data type as the input value.

Let’s do some practical exercises:

Power: This function returns the value of x raised to the power of Y.
SELECT POWER(4,2);
Returns 16

Mod: The first input is divided by the second, and the remainder is returned.
SELECT MOD(25,4);
Returns 1;

Abs: This function returns the absolute value of X.
SELECT ABS(-9.666);
Returns: 9.666

Ceil: This function returns the smallest integer value that is either greater than or equal to the input value.
SELECT CEIL(9.6);
Returns 10

Floor: This function returns the largest integer value that is less than or equal to the input value.
SELECT FLOOR(9.6);
Returns 9

Least: This function returns the value with the lowest value in the provided array.
SELECT LEAST(2,6,8,11);
Returns 2

Greatest: This function returns the value with the highest value in the provided array.
SELECT GREATEST(2,6,8,11);
Returns 11

Precision & Scale: Computes the precision (number of decimal digits) needed to represent the type of the argument expression as a DECIMAL value.

SELECT PRECISION(9.00812);
Returns 6

SELECT PRECISION(9), SCALE(9);
Returns 3 and 0.

SELECT PRECISION(9.00812), SCALE(9.00812);
Returns 6 and 5.

Sign: The function SIGN returns 1 if the given input is positive, -1 if the input value is negative, and 0 if it is zero.
SELECT SIGN(-22);
Returns -1
SELECT SIGN(22);
Returns 1
SELECT SIGN(0);
Returns 0