Impala mathematical functions
Mathematical functions, or arithmetic functions, perform numeric calculations that are typically more complex than basic addition, subtraction, multiplication, and division. For example, these functions include trigonometric, logarithmic, and base conversion operations.
Related information:
- INT
- BIGINT
- SMALLINT
- TINYINT
- DOUBLE
- FLOAT
- DECIMAL
Functions that perform bitwise operations are explained in Impala bit functions.
Function reference:
Impala supports the following mathematical functions:
- ABS
- ACOS
- ASIN
- ATAN
- ATAN2
- BIN
- CEIL, CEILING, DCEIL
- CONV
- COS
- COSH
- COT
- DEGREES
- E
- EXP
- FACTORIAL
- FLOOR, DFLOOR
- FMOD
- FNV_HASH
- GREATEST
- HEX
- IS_INF
- IS_NAN
- LEAST
- LN
- LOG
- LOG10
- LOG2
- MAX_INT, MAX_TINYINT, MAX_SMALLINT, MAX_BIGINT
- MIN_INT, MIN_TINYINT, MIN_SMALLINT, MIN_BIGINT
- MOD
- MURMUR_HASH
- NEGATIVE
- PI
- PMOD
- POSITIVE
- POW, POWER, DPOW, FPOW
- PRECISION
- QUOTIENT
- RADIANS
- RAND, RANDOM
- ROUND, DROUND
- SCALE
- SIGN
- SIN
- SINH
- SQRT
- TAN
- TANH
- TRUNCATE, DTRUNC, TRUNC
- UNHEX
- WIDTH_BUCKET
- ABS(numeric_type a)
-
Purpose: Returns the absolute value of the argument.
Return type: Same as the input value
Usage notes: Use this function to ensure all return values are positive. This is different than the
positive()
function, which returns its argument unchanged (even if the argument was negative). - ACOS(DOUBLE a)
-
Purpose: Returns the arccosine of the argument.
Return type:
DOUBLE
- ASIN(DOUBLE a)
-
Purpose: Returns the arcsine of the argument.
Return type:
DOUBLE
- ATAN(DOUBLE a)
-
Purpose: Returns the arctangent of the argument.
Return type:
DOUBLE
- ATAN(DOUBLE a, DOUBLE b)
-
Purpose: Returns the arctangent of the two arguments, with the signs of the
arguments used to determine the quadrant of the result.
Return type:
DOUBLE
- BIN(BIGINT a)
-
Purpose: Returns the binary representation of an integer value, that is, a
string of 0 and 1 digits.
Return type:
STRING
- CEIL(DOUBLE a), CEIL(DECIMAL(p,s) a), CEILING(DOUBLE a), CEILING(DECIMAL(p,s) a), DCEIL(DOUBLE a), DCEIL(DECIMAL(p,s) a)
-
Purpose: Returns the smallest integer that is greater than or equal to the
argument.
Return type: Same as the input type
- CONV(BIGINT n, INT from_base, INT to_base), CONV(STRING s, INT from_base, INT to_base)
-
Purpose: Returns a string representation of the first argument converted from
from_base
toto_base
. The first argument can be specified as a number or a string. For example,conv(100, 2, 10)
andconv('100', 2, 10)
both return'4'
.Return type:
STRING
Usage notes:
If
to_base
is negative, the first argument is treated as signed, and otherwise, it is treated as unsigned. For example:-
conv(-17, 10, -2)
returns'-10001'
,-17
in base 2. -
conv(-17, 10, 10)
returns'18446744073709551599'
.-17
is interpreted as an unsigned, 2^64-17, and then the value is returned in base 10.
The function returns
NULL
when the following illegal arguments are specified:-
Any argument is
NULL
. -
from_base
orto_base
is below-36
or above36
. -
from_base
orto_base
is-1
,0
, or1
. -
The first argument represents a positive number and
from_base
is a negative number.
If the first argument represents a negative number and
from_base
is a negative number, the function returns0
.If the first argument represents a number larger than the maximum
bigint
, the function returns:-
The string representation of -1 in
to_base
ifto_base
is negative. -
The string representation of 18446744073709551615' (2^64 - 1) in
to_base
ifto_base
is positive.
If the first argument does not represent a valid number in
from_base
, e.g. 3 in base 2 or '1a23' in base 10, the digits in the first argument are evaluated from left-to-right and used if a valid digit infrom_base
. The invalid digit and the digits to the right are ignored.For example:-
conv(445, 5, 10)
is converted toconv(44, 5, 10)
and returns'24'
. -
conv('1a23', 10, 16)
is converted toconv('1', 10 , 16)
and returns'1'
.
-
- COS(DOUBLE a)
-
Purpose: Returns the cosine of the argument.
Return type:
DOUBLE
- COSH(DOUBLE a)
-
Purpose: Returns the hyperbolic cosine of the argument.
Return type:
DOUBLE
- COT(DOUBLE a)
-
Purpose: Returns the cotangent of the argument.
Return type:
DOUBLE
Added in: Impala 2.3.0
- DEGREES(DOUBLE a)
-
Purpose: Converts argument value from radians to degrees.
Return type:
DOUBLE
- E()
-
Purpose: Returns the mathematical constant e.
Return type:
DOUBLE
- EXP(DOUBLE a), DEXP(DOUBLE a)
-
Purpose: Returns the mathematical constant e raised to the power of the
argument.
Return type:
DOUBLE
- FACTORIAL(integer_type a)
-
Purpose: Computes the factorial of an integer value. It works with any integer
type.
Added in: Impala 2.3.0
Usage notes: You can use either the
factorial()
function or the!
operator. The factorial of 0 is 1. Likewise, thefactorial()
function returns 1 for any negative value. The maximum positive value for the input argument is 20; a value of 21 or greater overflows the range for aBIGINT
and causes an error.Return type:
BIGINT
Added in: Impala 2.3.0
select factorial(5); +--------------+ | factorial(5) | +--------------+ | 120 | +--------------+ select 5!; +-----+ | 5! | +-----+ | 120 | +-----+
- FLOOR(DOUBLE a), FLOOR(DECIMAL(p,s) a), DFLOOR(DOUBLE a), DFLOOR(DECIMAL(p,s) a)
-
Purpose: Returns the largest integer that is less than or equal to the
argument.
Return type: Same as the input type
- FMOD(DOUBLE a, DOUBLE b), FMOD(FLOAT a, FLOAT b)
-
Purpose: Returns the modulus of a floating-point number.
Return type:
FLOAT
orDOUBLE
, depending on type of argumentsAdded in: Impala 1.1.1
Usage notes:
Because this function operates on
DOUBLE
orFLOAT
values, it is subject to potential rounding errors for values that cannot be represented precisely. Prefer to use whole numbers, or values that you know can be represented precisely by theDOUBLE
orFLOAT
types.Examples:
The following examples show equivalent operations with the
fmod()
function and the%
arithmetic operator, for values not subject to any rounding error.select fmod(10,3); +-------------+ | fmod(10, 3) | +-------------+ | 1 | +-------------+ select fmod(5.5,2); +--------------+ | fmod(5.5, 2) | +--------------+ | 1.5 | +--------------+ select 10 % 3; +--------+ | 10 % 3 | +--------+ | 1 | +--------+ select 5.5 % 2; +---------+ | 5.5 % 2 | +---------+ | 1.5 | +---------+
The following examples show operations with the
fmod()
function for values that cannot be represented precisely by theDOUBLE
orFLOAT
types, and thus are subject to rounding error.fmod(9.9,3.0)
returns a value slightly different than the expected 0.9 because of rounding.fmod(9.9,3.3)
returns a value quite different from the expected value of 0 because of rounding error during intermediate calculations.select fmod(9.9,3.0); +--------------------+ | fmod(9.9, 3.0) | +--------------------+ | 0.8999996185302734 | +--------------------+ select fmod(9.9,3.3); +-------------------+ | fmod(9.9, 3.3) | +-------------------+ | 3.299999713897705 | +-------------------+
- FNV_HASH(type v)
-
Purpose: Returns a consistent 64-bit value derived from the input argument, for
convenience of implementing hashing logic in an application.
Return type:
BIGINT
Usage notes:
You might use the return value in an application where you perform load balancing, bucketing, or some other technique to divide processing or storage.
Because the result can be any 64-bit value, to restrict the value to a particular range, you can use an expression that includes the
ABS()
function and the%
(modulo) operator. For example, to produce a hash value in the range 0-9, you could use the expressionABS(FNV_HASH(x)) % 10
.This function implements the same algorithm that Impala uses internally for hashing, on systems where the CRC32 instructions are not available.
This function implements the Fowler–Noll–Vo hash function, in particular the FNV-1a variation. This is not a perfect hash function: some combinations of values could produce the same result value. It is not suitable for cryptographic use.
Similar input values of different types could produce different hash values, for example the same numeric value represented as
SMALLINT
orBIGINT
,FLOAT
orDOUBLE
, orDECIMAL(5,2)
orDECIMAL(20,5)
.Examples:
[localhost:21000] > create table h (x int, s string); [localhost:21000] > insert into h values (0, 'hello'), (1,'world'), (1234567890,'antidisestablishmentarianism'); [localhost:21000] > select x, fnv_hash(x) from h; +------------+----------------------+ | x | fnv_hash(x) | +------------+----------------------+ | 0 | -2611523532599129963 | | 1 | 4307505193096137732 | | 1234567890 | 3614724209955230832 | +------------+----------------------+ [localhost:21000] > select s, fnv_hash(s) from h; +------------------------------+---------------------+ | s | fnv_hash(s) | +------------------------------+---------------------+ | hello | 6414202926103426347 | | world | 6535280128821139475 | | antidisestablishmentarianism | -209330013948433970 | +------------------------------+---------------------+ [localhost:21000] > select s, abs(fnv_hash(s)) % 10 from h; +------------------------------+-------------------------+ | s | abs(fnv_hash(s)) % 10.0 | +------------------------------+-------------------------+ | hello | 8 | | world | 6 | | antidisestablishmentarianism | 4 | +------------------------------+-------------------------+
For short argument values, the high-order bits of the result have relatively low entropy:
[localhost:21000] > create table b (x boolean); [localhost:21000] > insert into b values (true), (true), (false), (false); [localhost:21000] > select x, fnv_hash(x) from b; +-------+---------------------+ | x | fnv_hash(x) | +-------+---------------------+ | true | 2062020650953872396 | | true | 2062020650953872396 | | false | 2062021750465500607 | | false | 2062021750465500607 | +-------+---------------------+
Added in: Impala 1.2.2
- 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 ...])
-
Purpose: Returns the largest value from a list of expressions.
Return type: same as the initial argument value, except that integer values are promoted to
BIGINT
and floating-point values are promoted toDOUBLE
; useCAST()
when inserting into a smaller numeric column - HEX(BIGINT a), HEX(STRING a)
-
Purpose: Returns the hexadecimal representation of an integer value, or of the
characters in a string.
Return type:
STRING
- IS_INF(DOUBLE a)
-
Purpose: Tests whether a value is equal to the special value
inf
, signifying infinity.Return type:
BOOLEAN
Usage notes:
Infinity and NaN can be specified in text data files as
inf
andnan
respectively, and Impala interprets them as these special values. They can also be produced by certain arithmetic expressions; for example,1/0
returnsInfinity
andpow(-1, 0.5)
returnsNaN
. Or you can cast the literal values, such asCAST('nan' AS DOUBLE)
orCAST('inf' AS DOUBLE)
. - IS_NAN(DOUBLE a)
-
Purpose: Tests whether a value is equal to the special value
NaN
, signifyingnot a number
.Return type:
BOOLEAN
Usage notes:
Infinity and NaN can be specified in text data files as
inf
andnan
respectively, and Impala interprets them as these special values. They can also be produced by certain arithmetic expressions; for example,1/0
returnsInfinity
andpow(-1, 0.5)
returnsNaN
. Or you can cast the literal values, such asCAST('nan' AS DOUBLE)
orCAST('inf' AS DOUBLE)
. - 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 ...])
-
Purpose: Returns the smallest value from a list of expressions.
Return type: same as the initial argument value, except that integer values are promoted to
BIGINT
and floating-point values are promoted toDOUBLE
; useCAST()
when inserting into a smaller numeric column - LN(DOUBLE a), DLOG1(DOUBLE a)
-
Purpose: Returns the natural logarithm of the argument.
Return type:
DOUBLE
- LOG(DOUBLE base, DOUBLE a)
-
Purpose: Returns the logarithm of the second argument to the specified base.
Return type:
DOUBLE
- LOG10(DOUBLE a), DLOG10(DOUBLE a)
-
Purpose: Returns the logarithm of the argument to the base 10.
Return type:
DOUBLE
- LOG2(DOUBLE a)
-
Purpose: Returns the logarithm of the argument to the base 2.
Return type:
DOUBLE
- MAX_INT(), MAX_TINYINT(), MAX_SMALLINT(), MAX_BIGINT()
-
Purpose: Returns the largest value of the associated integral type.
Return type: The same as the integral type being checked.
Usage notes: Use the corresponding
min_
andmax_
functions to check if all values in a column are within the allowed range, before copying data or altering column definitions. If not, switch to the next higher integral type or to aDECIMAL
with sufficient precision. - MIN_INT(), MIN_TINYINT(), MIN_SMALLINT(), MIN_BIGINT()
-
Purpose: Returns the smallest value of the associated integral type (a negative
number).
Return type: The same as the integral type being checked.
Usage notes: Use the corresponding
min_
andmax_
functions to check if all values in a column are within the allowed range, before copying data or altering column definitions. If not, switch to the next higher integral type or to aDECIMAL
with sufficient precision. - MOD(numeric_type a, same_type b)
-
Purpose: Returns the modulus of a number. Equivalent to the
%
arithmetic operator. Works with any size integer type, any size floating-point type, andDECIMAL
with any precision and scale.Return type: Same as the input value
Added in: Impala 2.20
Usage notes:
Because this function works with
DECIMAL
values, prefer it overfmod()
when working with fractional values. It is not subject to the rounding errors that makefmod()
problematic with floating-point numbers.Query plans shows the
MOD()
function as the%
operator.Examples:
The following examples show how the
mod()
function works for whole numbers and fractional values, and how the%
operator works the same way. In the case ofmod(9.9,3)
, the type conversion for the second argument results in the first argument being interpreted asDOUBLE
, so to produce an accurateDECIMAL
result requires casting the second argument or writing it as aDECIMAL
literal, 3.0.select mod(10,3); +-------------+ | mod(10, 3) | +-------------+ | 1 | +-------------+ select mod(5.5,2); +--------------+ | mod(5.5, 2) | +--------------+ | 1.5 | +--------------+ select 10 % 3; +--------+ | 10 % 3 | +--------+ | 1 | +--------+ select 5.5 % 2; +---------+ | 5.5 % 2 | +---------+ | 1.5 | +---------+ select mod(9.9,3.3); +---------------+ | mod(9.9, 3.3) | +---------------+ | 0.0 | +---------------+ select mod(9.9,3); +--------------------+ | mod(9.9, 3) | +--------------------+ | 0.8999996185302734 | +--------------------+ select mod(9.9, cast(3 as decimal(2,1))); +-----------------------------------+ | mod(9.9, cast(3 as decimal(2,1))) | +-----------------------------------+ | 0.9 | +-----------------------------------+ select mod(9.9,3.0); +---------------+ | mod(9.9, 3.0) | +---------------+ | 0.9 | +---------------+
- MURMUR_HASH(type v)
-
Purpose: Returns a consistent 64-bit value derived from the input argument, for
convenience of implementing MurmurHash2 non-cryptographic hash function.
Return type:
BIGINT
Usage notes:
You might use the return value in an application where you perform load balancing, bucketing, or some other technique to divide processing or storage. This function provides a good performance for all kinds of keys such as number, ascii string and UTF-8. It can be recommended as general-purpose hashing function.
Regarding comparison of murmur_hash with fnv_hash, murmur_hash is based on Murmur2 hash algorithm and fnv_hash function is based on FNV-1a hash algorithm. Murmur2 and FNV-1a can show very good randomness and performance compared with well known other hash algorithms, but Murmur2 slightly show better randomness and performance than FNV-1a.
Similar input values of different types could produce different hash values, for example the same numeric value represented as
SMALLINT
orBIGINT
,FLOAT
orDOUBLE
, orDECIMAL(5,2)
orDECIMAL(20,5)
.Examples:
[localhost:21000] > create table h (x int, s string); [localhost:21000] > insert into h values (0, 'hello'), (1,'world'), (1234567890,'antidisestablishmentarianism'); [localhost:21000] > select x, murmur_hash(x) from h; +------------+----------------------+ | x | murmur_hash(x) | +------------+----------------------+ | 0 | 6960269033020761575 | | 1 | -780611581681153783 | | 1234567890 | -5754914572385924334 | +------------+----------------------+ [localhost:21000] > select s, murmur_hash(s) from h; +------------------------------+----------------------+ | s | murmur_hash(s) | +------------------------------+----------------------+ | hello | 2191231550387646743 | | world | 5568329560871645431 | | antidisestablishmentarianism | -2261804666958489663 | +------------------------------+----------------------+
For short argument values, the high-order bits of the result have relatively higher entropy than fnv_hash:
[localhost:21000] > create table b (x boolean); [localhost:21000] > insert into b values (true), (true), (false), (false); [localhost:21000] > select x, murmur_hash(x) from b; +-------+----------------------+ | x | murmur_hash(x) | +-------+---------------------++ | true | -5720937396023583481 | | true | -5720937396023583481 | | false | 6351753276682545529 | | false | 6351753276682545529 | +-------+--------------------+-+
Added in: Impala 2.12.0
- NEGATIVE(numeric_type a)
-
Purpose: Returns the argument with the sign reversed; returns a positive value
if the argument was already negative.
Return type: Same as the input value
Usage notes: Use
-abs(a)
instead if you need to ensure all return values are negative. - PI()
-
Purpose: Returns the constant pi.
Return type:
double
- PMOD(BIGINT a, BIGINT b), PMOD(DOUBLE a, DOUBLE b)
-
Purpose: Returns the positive modulus of a number. Primarily
for Hive SQL compatibility.
Return type:
INT
orDOUBLE
, depending on type of argumentsExamples:
The following examples show how the
fmod()
function sometimes returns a negative value depending on the sign of its arguments, and thepmod()
function returns the same value asfmod()
, but sometimes with the sign flipped.select fmod(-5,2); +-------------+ | fmod(-5, 2) | +-------------+ | -1 | +-------------+ select pmod(-5,2); +-------------+ | pmod(-5, 2) | +-------------+ | 1 | +-------------+ select fmod(-5,-2); +--------------+ | fmod(-5, -2) | +--------------+ | -1 | +--------------+ select pmod(-5,-2); +--------------+ | pmod(-5, -2) | +--------------+ | -1 | +--------------+ select fmod(5,-2); +-------------+ | fmod(5, -2) | +-------------+ | 1 | +-------------+ select pmod(5,-2); +-------------+ | pmod(5, -2) | +-------------+ | -1 | +-------------+
- POSITIVE(numeric_type a)
-
Purpose: Returns the original argument unchanged (even if the argument is
negative).
Return type: Same as the input value
Usage notes: Use
abs()
instead if you need to ensure all return values are positive. - POW(DOUBLE a, double p), POWER(DOUBLE a, DOUBLE p), DPOW(DOUBLE a, DOUBLE p), FPOW(DOUBLE a, DOUBLE p)
-
Purpose: Returns the first argument raised to the power of the second argument.
Return type:
DOUBLE
- PRECISION(numeric_expression)
-
Purpose: Computes the precision (number of decimal digits) needed to represent
the type of the argument expression as a
DECIMAL
value.Usage notes:
Typically used in combination with the
scale()
function, to determine the appropriateDECIMAL(precision,scale)
type to declare in aCREATE TABLE
statement orCAST()
function.Return type:
INT
Examples:
The following examples demonstrate how to check the precision and scale of numeric literals or other numeric expressions. Impala represents numeric literals in the smallest appropriate type. 5 is aTINYINT
value, which ranges from -128 to 127, therefore 3 decimal digits are needed to represent the entire range, and because it is an integer value there are no fractional digits. 1.333 is interpreted as aDECIMAL
value, with 4 digits total and 3 digits after the decimal point.[localhost:21000] > select precision(5), scale(5); +--------------+----------+ | precision(5) | scale(5) | +--------------+----------+ | 3 | 0 | +--------------+----------+ [localhost:21000] > select precision(1.333), scale(1.333); +------------------+--------------+ | precision(1.333) | scale(1.333) | +------------------+--------------+ | 4 | 3 | +------------------+--------------+ [localhost:21000] > with t1 as ( select cast(12.34 as decimal(20,2)) x union select cast(1 as decimal(8,6)) x ) select precision(x), scale(x) from t1 limit 1; +--------------+----------+ | precision(x) | scale(x) | +--------------+----------+ | 24 | 6 | +--------------+----------+
- QUOTIENT(BIGINT numerator, BIGINT denominator), QUOTIENT(DOUBLE numerator, DOUBLE denominator)
-
Purpose: Returns the first argument divided by the second argument, discarding
any fractional part. Avoids promoting integer arguments to
DOUBLE
as happens with the/
SQL operator. Also includes an overload that acceptsDOUBLE
arguments, discards the fractional part of each argument value before dividing, and again returnsBIGINT
. With integer arguments, this function works the same as theDIV
operator.Return type:
BIGINT
- RADIANS(DOUBLE a)
-
Purpose: Converts argument value from degrees to radians.
Return type:
DOUBLE
- RAND(), RAND(BIGINT seed), RANDOM(), RANDOM(BIGINT seed)
-
Purpose: Returns a random value between 0 and 1. After
rand()
is called with a seed argument, it produces a consistent random sequence based on the seed value.Return type:
DOUBLE
Usage notes: Currently, the random sequence is reset after each query, and multiple calls to
rand()
within the same query return the same value each time. For different number sequences that are different for each query, pass a unique seed value to each call torand()
. For example,select rand(unix_timestamp()) from ...
Examples:
The following examples show how
rand()
can produce sequences of varying predictability, so that you can reproduce query results involving random values or generate unique sequences of random values for each query. Whenrand()
is called with no argument, it generates the same sequence of values each time, regardless of the ordering of the result set. Whenrand()
is called with a constant integer, it generates a different sequence of values, but still always the same sequence for the same seed value. If you pass in a seed value that changes, such as the return value of the expressionunix_timestamp(now())
, each query will use a different sequence of random values, potentially more useful in probability calculations although more difficult to reproduce at a later time. Therefore, the final two examples with an unpredictable seed value also include the seed in the result set, to make it possible to reproduce the same random sequence later.select x, rand() from three_rows; +---+-----------------------+ | x | rand() | +---+-----------------------+ | 1 | 0.0004714746030380365 | | 2 | 0.5895895192351144 | | 3 | 0.4431900859080209 | +---+-----------------------+ select x, rand() from three_rows order by x desc; +---+-----------------------+ | x | rand() | +---+-----------------------+ | 3 | 0.0004714746030380365 | | 2 | 0.5895895192351144 | | 1 | 0.4431900859080209 | +---+-----------------------+ select x, rand(1234) from three_rows order by x; +---+----------------------+ | x | rand(1234) | +---+----------------------+ | 1 | 0.7377511392057646 | | 2 | 0.009428468537250751 | | 3 | 0.208117277924026 | +---+----------------------+ select x, rand(1234) from three_rows order by x desc; +---+----------------------+ | x | rand(1234) | +---+----------------------+ | 3 | 0.7377511392057646 | | 2 | 0.009428468537250751 | | 1 | 0.208117277924026 | +---+----------------------+ select x, unix_timestamp(now()), rand(unix_timestamp(now())) from three_rows order by x; +---+-----------------------+-----------------------------+ | x | unix_timestamp(now()) | rand(unix_timestamp(now())) | +---+-----------------------+-----------------------------+ | 1 | 1440777752 | 0.002051228658320023 | | 2 | 1440777752 | 0.5098743483004506 | | 3 | 1440777752 | 0.9517714925817081 | +---+-----------------------+-----------------------------+ select x, unix_timestamp(now()), rand(unix_timestamp(now())) from three_rows order by x desc; +---+-----------------------+-----------------------------+ | x | unix_timestamp(now()) | rand(unix_timestamp(now())) | +---+-----------------------+-----------------------------+ | 3 | 1440777761 | 0.9985985015512437 | | 2 | 1440777761 | 0.3251255333074953 | | 1 | 1440777761 | 0.02422675025846192 | +---+-----------------------+-----------------------------+
- ROUND(DOUBLE a), ROUND(DOUBLE a, INT d), ROUND(DECIMAL a, int_type d), DROUND(DOUBLE a), DROUND(DOUBLE a, INT d), DROUND(DECIMAL(p,s) a, int_type d)
-
Purpose: Rounds a floating-point value. By default (with a single argument),
rounds to the nearest integer. Values ending in .5 are rounded up for positive
numbers, down for negative numbers (that is, away from zero). The optional second
argument specifies how many digits to leave after the decimal point; values greater
than zero produce a floating-point return value rounded to the requested number of
digits to the right of the decimal point.
Return type: Same as the input type
- SCALE(numeric_expression)
-
Purpose: Computes the scale (number of decimal digits to the right of the
decimal point) needed to represent the type of the argument expression as a
DECIMAL
value.Usage notes:
Typically used in combination with the
precision()
function, to determine the appropriateDECIMAL(precision,scale)
type to declare in aCREATE TABLE
statement orCAST()
function.Return type:
int
Examples:
The following examples demonstrate how to check the precision and scale of numeric literals or other numeric expressions. Impala represents numeric literals in the smallest appropriate type. 5 is aTINYINT
value, which ranges from -128 to 127, therefore 3 decimal digits are needed to represent the entire range, and because it is an integer value there are no fractional digits. 1.333 is interpreted as aDECIMAL
value, with 4 digits total and 3 digits after the decimal point.[localhost:21000] > select precision(5), scale(5); +--------------+----------+ | precision(5) | scale(5) | +--------------+----------+ | 3 | 0 | +--------------+----------+ [localhost:21000] > select precision(1.333), scale(1.333); +------------------+--------------+ | precision(1.333) | scale(1.333) | +------------------+--------------+ | 4 | 3 | +------------------+--------------+ [localhost:21000] > with t1 as ( select cast(12.34 as decimal(20,2)) x union select cast(1 as decimal(8,6)) x ) select precision(x), scale(x) from t1 limit 1; +--------------+----------+ | precision(x) | scale(x) | +--------------+----------+ | 24 | 6 | +--------------+----------+
- SIGN(DOUBLE a)
-
Purpose: Returns -1, 0, or 1 to indicate the signedness of the argument value.
Return type:
INT
- SIN(DOUBLE a)
-
Purpose: Returns the sine of the argument.
Return type:
DOUBLE
- SINH(DOUBLE a)
-
Purpose: Returns the hyperbolic sine of the argument.
Return type:
DOUBLE
- SQRT(DOUBLE a), DSQRT(DOUBLE a)
-
Purpose: Returns the square root of the argument.
Return type:
DOUBLE
- TAN(DOUBLE a)
-
Purpose: Returns the tangent of the argument.
Return type:
DOUBLE
- TANH(DOUBLE a)
-
Purpose: Returns the hyperbolic tangent of the argument.
Return type:
DOUBLE
- TRUNCATE(DOUBLE_or_DECIMAL a[, digits_to_leave]), DTRUNC(DOUBLE_or_DECIMAL a[, digits_to_leave]), TRUNC(DOUBLE_or_DECIMAL a[, digits_to_leave])
-
Purpose: Removes some or all fractional digits from a numeric value.
Arguments: With a single floating-point argument, removes all fractional digits, leaving an integer value. The optional second argument specifies the number of fractional digits to include in the return value, and only applies when the argument type is
DECIMAL
. A second argument of 0 truncates to a whole integer value. A second argument of negative N sets N digits to 0 on the left side of the decimalScale argument: The scale argument applies only when truncating
DECIMAL
values. It is an integer specifying how many significant digits to leave to the right of the decimal point. A scale argument of 0 truncates to a whole integer value. A scale argument of negative N sets N digits to 0 on the left side of the decimal point.TRUNCATE()
,DTRUNC()
, andTRUNC()
are aliases for the same function.Return type: Same as the input type
Added in: The
TRUNC()
alias was added in Impala 2.10.0.Usage notes:
You can also pass a
DOUBLE
argument, orDECIMAL
argument with optional scale, to theDTRUNC()
orTRUNCATE
functions. Using theTRUNC()
function for numeric values is common with other industry-standard database systems, so you might find suchTRUNC()
calls in code that you are porting to Impala.The
TRUNC()
function also has a signature that applies toTIMESTAMP
values.Examples:
The following examples demonstrate the
TRUNCATE()
andDTRUNC()
signatures for this function:select truncate(3.45); +----------------+ | truncate(3.45) | +----------------+ | 3 | +----------------+ select truncate(-3.45); +-----------------+ | truncate(-3.45) | +-----------------+ | -3 | +-----------------+ select truncate(3.456,1); +--------------------+ | truncate(3.456, 1) | +--------------------+ | 3.4 | +--------------------+ select dtrunc(3.456,1); +------------------+ | dtrunc(3.456, 1) | +------------------+ | 3.4 | +------------------+ select truncate(3.456,2); +--------------------+ | truncate(3.456, 2) | +--------------------+ | 3.45 | +--------------------+ select truncate(3.456,7); +--------------------+ | truncate(3.456, 7) | +--------------------+ | 3.4560000 | +--------------------+
The following examples demonstrate using
TRUNC()
withDECIMAL
orDOUBLE
values, and with an optional scale argument forDECIMAL
values. (The behavior is the same for theTRUNCATE()
andDTRUNC()
aliases also.)create table t1 (d decimal(20,7)); -- By default, no digits to the right of the decimal point. insert into t1 values (1.1), (2.22), (3.333), (4.4444), (5.55555); select trunc(d) from t1 order by d; +----------+ | trunc(d) | +----------+ | 1 | | 2 | | 3 | | 4 | | 5 | +----------+ -- 1 digit to the right of the decimal point. select trunc(d,1) from t1 order by d; +-------------+ | trunc(d, 1) | +-------------+ | 1.1 | | 2.2 | | 3.3 | | 4.4 | | 5.5 | +-------------+ -- 2 digits to the right of the decimal point, -- including trailing zeroes if needed. select trunc(d,2) from t1 order by d; +-------------+ | trunc(d, 2) | +-------------+ | 1.10 | | 2.22 | | 3.33 | | 4.44 | | 5.55 | +-------------+ insert into t1 values (9999.9999), (8888.8888); -- Negative scale truncates digits to the left -- of the decimal point. select trunc(d,-2) from t1 where d > 100 order by d; +--------------+ | trunc(d, -2) | +--------------+ | 8800 | | 9900 | +--------------+ -- The scale of the result is adjusted to match the -- scale argument. select trunc(d,2), precision(trunc(d,2)) as p, scale(trunc(d,2)) as s from t1 order by d; +-------------+----+---+ | trunc(d, 2) | p | s | +-------------+----+---+ | 1.10 | 15 | 2 | | 2.22 | 15 | 2 | | 3.33 | 15 | 2 | | 4.44 | 15 | 2 | | 5.55 | 15 | 2 | | 8888.88 | 15 | 2 | | 9999.99 | 15 | 2 | +-------------+----+---+
create table dbl (d double); insert into dbl values (1.1), (2.22), (3.333), (4.4444), (5.55555), (8888.8888), (9999.9999); -- With double values, there is no optional scale argument. select trunc(d) from dbl order by d; +----------+ | trunc(d) | +----------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 8888 | | 9999 | +----------+
- UNHEX(STRING a)
-
Purpose: Returns a string of characters with ASCII values corresponding to
pairs of hexadecimal digits in the argument.
Return type:
STRING
- WIDTH_BUCKET(DECIMAL expr, DECIMAL min_value, DECIMAL max_value, INT num_buckets)
-
Purpose: Returns the bucket number in which the
expr
value would fall in the histogram where its range betweenmin_value
andmax_value
is divided intonum_buckets
buckets of identical sizes.