Numeric Functions
ABS
ABS (expression)Parameters
-
expression- may be a column name, a result of another function, or a math operation.
Description
Returns the absolute value of an expression.
Example
Calculate an absolute value:
SELECT transfer_id, ABS (price) from Transfers;ACOS
ACOS (expression)Parameters
-
expression- may be a column name, a result of another function, or a math operation.
Description
Calculates the arc cosine. This method returns a double.
Example
Get arc cos value:
SELECT acos(angle) FROM Triangles;ASIN
ASIN (expression)Parameters
-
expression- may be a column name, a result of another function, or a math operation.
Description
Calculates the arc sine. This method returns a double.
Example
Calculate an arc sine:
SELECT asin(angle) FROM Triangles;ATAN
ATAN (expression)Parameters
-
expression- may be a column name, a result of another function, or a math operation.
Description
Calculates the arc tangent. This method returns a double.
Example
Get an arc tangent:
SELECT atan(angle) FROM Triangles;COS
COS (expression)Parameters
-
expression- may be a column name, a result of another function, or a math operation.
Description
Calculates the trigonometric cosine. This method returns a double.
Example
Get a cosine:
SELECT COS(angle) FROM Triangles;COSH
COSH (expression)Parameters
-
expression- may be a column name, a result of another function, or a math operation.
Description
Calculates the hyperbolic cosine. This method returns a double.
Example
Get an hyperbolic cosine:
SELECT HCOS(angle) FROM Triangles;COT
COT (expression)Parameters
-
expression- may be a column name, a result of another function, or a math operation.
Description
Calculates the trigonometric cotangent (1/TAN(ANGLE)). This method returns a double.
Example
Gets a trigonometric cotangent:
SELECT COT(angle) FROM Triangles;SIN
SIN (expression)Parameters
-
expression- may be a column name, a result of another function, or a math operation.
Description
Calculates the trigonometric sine. This method returns a double.
Example
Get a trigonometric sine:
SELECT SIN(angle) FROM Triangles;SINH
SINH (expression)Parameters
-
expression- may be a column name, a result of another function, or a math operation.
Description
Calculates the hyperbolic sine. This method returns a double.
Example
Get a hyperbolic sine:
SELECT SINH(angle) FROM Triangles;TAN
TAN (expression)Parameters
-
expression- may be a column name, a result of another function, or a math operation.
Description
Calculates the trigonometric tangent. This method returns a double.
Example
Get a trigonometric tangent:
SELECT TAN(angle) FROM Triangles;TANH
TANH (expression)Parameters
-
expression- may be a column name, a result of another function, or a math operation.
Description
Calculates the hyperbolic tangent. This method returns a double.
Example
Get a hyperbolic tangent:
SELECT TANH(angle) FROM Triangles;ATAN2
ATAN2 (y, x)Parameters
-
x and y- the arguments.
Description
Calculates the angle when converting the rectangular coordinates to polar coordinates. This method returns a double.
Example
Get a hyperbolic tangent:
SELECT ATAN2(X, Y) FROM Triangles;BITAND
BITAND (y, x)Parameters
-
x and y- the arguments.
Description
The bitwise AND operation. This method returns a long.
Example
SELECT BITAND(X, Y) FROM Triangles;BITGET
BITGET (y, x)Parameters
-
x and y- the arguments.
Description
Returns true if and only if the first parameter has a bit set in the position specified by the second parameter. This method returns a boolean. The second parameter is zero-indexed; the least significant bit has position 0.
Example
Check that 3rd bit is 1:
SELECT BITGET(X, 3) from Triangles;BITOR
BITOR (y, x)Parameters
-
x and y- the arguments.
Description
The bitwise OR operation. This method returns a long.
Example
Calculate OR between two fields:
SELECT BITGET(X, Y) from Triangles;BITXOR
BITXOR (y, x)Parameters
-
x and y- the arguments.
Description
The bitwise XOR operation. This method returns a long.
Example
Calculate XOR between two fields:
SELECT BITXOR(X, Y) FROM Triangles;MOD
MOD (y, x)Parameters
-
x and y- the arguments.
Description
The modulo operation. This method returns a long.
Example
Calculate MOD between two fields:
SELECT BITXOR(X, Y) FROM Triangles;CEILING
CEIL (expression)
CEILING (expression)Parameters
-
expression- any valid numeric expression.
Description
See also Java Math.ceil. This method returns a double.
Example
Calculate a ceiling price for items:
SELECT item_id, CEILING(price) FROM Items;DEGREES
DEGREES (expression)Parameters
-
expression- any valid numeric expression.
Description
See also Java Math.toDegrees. This method returns a double.
Example
Converts the argument value to degrees:
SELECT DEGREES(X) FROM Triangles;EXP
EXP (expression)Parameters
-
expression- any valid numeric expression.
Description
See also Java Math.exp. This method returns a double.
Example
Calculates exp:
SELECT EXP(X) FROM Triangles;FLOOR
FLOOR (expression)Parameters
-
expression- any valid numeric expression.
Description
See also Java Math.floor. This method returns a double.
Example
Calculates floor price:
SELECT FLOOR(X) FROM Items;LOG
LOG (expression)
LN (expression)Parameters
-
expression- any valid numeric expression.
Description
See also Java Math.log. This method returns a double.
Example
Calculates LOG:
SELECT LOG(X) from Items;LOG10
LOG10 (expression)Parameters
-
expression- any valid numeric expression.
Description
See also Java Math.log10 (in Java 5). This method returns a double.
Example
Calculate LOG10:
SELECT LOG(X) FROM Items;RADIANS
RADIANS (expression)Parameters
-
expression- any valid numeric expression.
Description
See also Java Math.toRadians. This method returns a double.
Example
Calculates RADIANS:
SELECT RADIANS(X) FROM Items;SQRT
SQRT (expression)Parameters
-
expression- any valid numeric expression.
Description
See also Java Math.sqrt. This method returns a double.
Example
Calculates SQRT:
SELECT SQRT(X) FROM Items;PI
PI (expression)Parameters
-
expression- any valid numeric expression.
Description
See also Java Math.PI. This method returns a double.
Example
Calculates PI:
SELECT PI(X) FROM Items;POWER
POWER (X, Y)Parameters
-
expression- any valid numeric expression.
Description
See also Java Math.pow. This method returns a double.
Example
Calculate the power of 2:
SELECT pow(2, n) FROM Rows;RAND
{RAND | RANDOM} ([expression])Parameters
-
expression- any valid numeric expression seeds the session’s random number generator.
Description
Calling the function without a parameter returns the next a pseudo random number. Calling it with a parameter seeds the session’s random number generator. This method returns a double between 0 (including) and 1 (excluding).
Example
Gets a random number for every play:
SELECT random() FROM Play;RANDOM_UUID
{RANDOM_UUID | UUID} ()Description
Returns a new UUID with 122 pseudo random bits.
Example
Gets random number for every Player:
SELECT UUID(),name FROM Player;ROUND
ROUND ( expression [, precision] )Parameters
-
expression- any valid numeric expression. -
precision- the number of digits after the decimal to round to. Rounds to the nearest long if the number of digits if not set.
Description
Rounds to a number of digits, or to the nearest long if the number of digits if not set. This method returns a numeric (the same type as the input).
Example
Convert every Player’s age to an integer number:
SELECT name, ROUND(age) FROM Player;ROUNDMAGIC
ROUNDMAGIC (expression)Parameters
-
expression- any valid numeric expression.
Description
This function is good for rounding numbers, but it can be slow. It has special handling for numbers around 0. Only numbers smaller than or equal to +/-1000000000000 are supported. The value is converted to a String internally, and then the last 4 characters are checked. '000x' becomes '0000' and '999x' becomes '999999', which is rounded automatically. This method returns a double.
Example
Round every Player’s age:
SELECT name, ROUNDMAGIC(AGE/3*3) FROM Player;SECURE_RAND
SECURE_RAND (int)Parameters
-
int- specifies the number of digits.
Description
Generate a number of cryptographically secure random numbers. This method returns bytes.
Example
Get a truly random number:
SELECT name, SECURE_RAND(10) FROM Player;SIGN
SIGN (expression)Parameters
-
expression- any valid numeric expression.
Description
Return -1 if the value is smaller 0, 0 if zero, and otherwise 1.
Example
Get a sign for every value:
SELECT name, SIGN(VALUE) FROM Player;ENCRYPT
ENCRYPT (algorithmString , keyBytes , dataBytes)Parameters
-
algorithmString- sets a supported AES algorithm. -
keyBytes- sets a key. -
dataBytes- sets data.
Description
Encrypt data using a key. The supported algorithm is AES. The block size is 16 bytes. This method returns bytes.
Example
Encrypt players name:
SELECT ENCRYPT('AES', '00', STRINGTOUTF8(Name)) FROM Player;DECRYPT
DECRYPT (algorithmString , keyBytes , dataBytes)Parameters
-
algorithmString- sets a supported AES algorithm. -
keyBytes- sets a key. -
dataBytes- sets data.
Description
Decrypts data using a key. The supported algorithm is AES. The block size is 16 bytes. This method returns bytes.
Example
Decrypt Players' names:
SELECT DECRYPT('AES', '00', '3fabb4de8f1ee2e97d7793bab2db1116'))) FROM Player;TRUNCATE
{TRUNC | TRUNCATE} (\{\{numeric, digitsInt} | timestamp | date | timestampString})Description
Truncates to a number of digits (to the next value closer to 0). This method returns a double. When used with a timestamp, truncates a timestamp to a date (day) value. When used with a date, truncates a date to a date (day) value less time part. When used with a timestamp as string, truncates a timestamp to a date (day) value.
Example
TRUNCATE(VALUE, 2);COMPRESS
COMPRESS(dataBytes [, algorithmString])Parameters
-
dataBytes- data to compress. -
algorithmString- an algorithm to use for compression.
Description
Compress the data using the specified compression algorithm. Supported algorithms are: LZF (faster but lower compression; default), and DEFLATE (higher compression). Compression does not always reduce size. Very small objects and objects with little redundancy may get larger. This method returns bytes.
Example
COMPRESS(STRINGTOUTF8('Test'))EXPAND
EXPAND(dataBytes)Parameters
-
dataBytes- data to expand.
Description
Expand data that was compressed using the COMPRESS function. This method returns bytes.
Example
UTF8TOSTRING(EXPAND(COMPRESS(STRINGTOUTF8('Test'))))ZERO
ZERO()Description
Return the value 0. This function can be used even if numeric literals are disabled.
Example
ZERO()Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.