Numeric Functions
ABS
Description
Returns the absolute value of an expression.
ABS (expression)Parameters
expression - may be a column name, a result of another function, or a math operation
Example
Calculate an absolute value:
SELECT transfer_id, ABS (price) from Transfers;ACOS
Description
Calculates the arc cosine, returns a double.
ACOS (expression)Parameters
expression - may be a column name, a result of another function, or a math operation
Example
Calculate the arc cos value:
SELECT acos(angle) FROM Triangles;ASIN
Description
Calculates the arc sine, returns a double.
ASIN (expression)Parameters
expression - may be a column name, a result of another function, or a math operation
Example
Calculate an arc sine:
SELECT asin(angle) FROM Triangles;ATAN
Description
Calculates the arc tangent, returns a double.
ATAN (expression)Parameters
expression - may be a column name, a result of another function, or a math operation
Example
Calculate an arc tangent:
SELECT atan(angle) FROM Triangles;COS
Description
Calculates the trigonometric cosine, returns a double.
COS (expression)Parameters
expression - may be a column name, a result of another function, or a math operation
Example
Calculate a cosine:
SELECT COS(angle) FROM Triangles;COSH
Description
Calculates the hyperbolic cosine, returns a double.
COSH (expression)Parameters
expression - may be a column name, a result of another function, or a math operation
Example
Calculate n hyperbolic cosine:
SELECT HCOS(angle) FROM Triangles;COT
Description
Calculates the trigonometric cotangent (1/TAN(ANGLE)), returns a double.
COT (expression)Parameters
expression - may be a column name, a result of another function, or a math operation
Example
Calculate trigonometric cotangent:
SELECT COT(angle) FROM Triangles;SIN
Description
Calculates the trigonometric sine, returns a double.
SIN (expression)Parameters
expression - may be a column name, a result of another function, or a math operation
Example
Calculate a trigonometric sine:
SELECT SIN(angle) FROM Triangles;SINH
Description
Calculates the hyperbolic sine, returns a double.
SINH (expression)Parameters
expression - may be a column name, a result of another function, or a math operation
Example
Calculate a hyperbolic sine:
SELECT SINH(angle) FROM Triangles;TAN
Description
Calculates the trigonometric tangent, returns a double.
TAN (expression)Parameters
expression - may be a column name, a result of another function, or a math operation
Example
Calculate a trigonometric tangent:
SELECT TAN(angle) FROM Triangles;TANH
Description
Calculates the hyperbolic tangent, returns a double.
TANH (expression)Parameters
expression - may be a column name, a result of another function, or a math operation
Example
Calculate a hyperbolic tangent:
SELECT TANH(angle) FROM Triangles;ATAN2
Description
Calculates the angle when converting the rectangular coordinates to the polar coordinates, returns a double.
ATAN2 (y, x)Parameters
x and y - the arguments
Example
Calculate a 2-argument arctangent:
SELECT ATAN2(X, Y) FROM Triangles;BITAND
Description
The bitwise AND operation, returns a long.
BITAND (y, x)Parameters
x and y - the arguments.
Example
Return the bitwise AND:
SELECT BITAND(X, Y) FROM Triangles;BITGET
Description
Returns true if and only if the first parameter has a bit set in the position specified by the second parameter. The second parameter is zero-indexed; the least significant bit has position 0.
BITGET (y, x)Parameters
x and y - the arguments
Example
Verify that teh third bit is 1:
SELECT BITGET(X, 3) from Triangles;BITOR
Description
The bitwise OR operation, returns a long.
BITOR (y, x)Parameters
x and y - the arguments
Example
Perform the bitwise OR operation:
SELECT BITGET(X, Y) from Triangles;BITXOR
Description
The bitwise XOR operation, returns a long.
BITXOR (y, x)Parameters
x and y - the arguments
Example
Perform the bitwise XOR operation:
SELECT BITXOR(X, Y) FROM Triangles;MOD
Description
The modulo operation, returns a long.
MOD (y, x)Parameters
x and y - the arguments
Example
Calculate MOD between two fields:
SELECT BITXOR(X, Y) FROM Triangles;CEILING
Description
Returns the smallest integer value that is greater than, or equal to, the argument. The returned value is of the same type as the argument, with the scale set to 0 the precision adjusted (if applicable).
CEIL (expression)
CEILING (expression)Parameters
expression - any valid numeric expression
Example
Calculate a ceiling price for items:
SELECT item_id, CEILING(price) FROM Items;DEGREES
Description
Converts an angle measured in radians to an approximately equivalent angle measured in degrees, returns a double.
DEGREES (expression)Parameters
expression - any valid numeric expression
Example
Convert radians to degrees:
SELECT DEGREES(X) FROM Triangles;EXP
Description
Calculates E raised to the power of x, returns a double.
EXP (expression)Parameters
expression - any valid numeric expression
Example
Calculate exp(X):
SELECT EXP(X) FROM Triangles;FLOOR
Description
Returns the largest integer value that is less than, or equal to, the argument. The returned value is of the same type as the argument, with the scale set to 0 the precision adjusted (if applicable).
FLOOR (expression)Parameters
expression - any valid numeric expression
Example
Calculate a floor price:
SELECT FLOOR(X) FROM Items;LOG
Description
Calculates the natural logarithm (base e) of a double value, returns a double.
LOG (expression)
LN (expression)Parameters
expression - any valid numeric expression
Example
Calculate a natural logarithm:
SELECT LOG(X) from Items;LOG10
Description
Calculates the base 10 logarithm of a double value, returns a double.
LOG10 (expression)Parameters
expression - any valid numeric expression
Example
Calculate a base 10 algorithm:
SELECT LOG(X) FROM Items;RADIANS
Description
Converts an angle measured in degrees to an approximately equivalent angle measured in radians, returns a double.
RADIANS (expression)Parameters
expression - any valid numeric expression
Example
Calculate RADIANS:
SELECT RADIANS(X) FROM Items;SQRT
Description
Calculates the correctly rounded positive square root of a double value, returns a double.
SQRT (expression)Parameters
expression - any valid numeric expression
Example
Calculate a square root:
SELECT SQRT(X) FROM Items;PI
Description
Returns Pi as a static final double constant.
PI (expression)Example
Calculate Pi:
SELECT PI(X) FROM Items;POWER
Description
Calculates a number raised to the power of some other number, returns a double.
POWER (X, Y)Parameters
-
x- the base -
y- the power
Example
Calculate n in the power of 2:
SELECT pow(n, 2) FROM Rows;RAND
Description
When called without a parameter, returns a pseudo random number. When called with a parameter, seeds the session’s random number generator. Returns a double between 0 (including) and 1 (excluding).
{RAND | RANDOM} ([expression])Parameters
expression - any valid numeric expression
Example
Return a random number for every play:
SELECT random() FROM Play;RANDOM_UUID
Description
Returns a new UUID with 122 pseudo random bits.
{RANDOM_UUID | UUID} ()Example
Return a random number for every Player:
SELECT UUID(),name FROM Player;ROUND
Description
Rounds to the specified number of digits, or to the nearest long (if the number of digits if not specified). Returns a numeric (the same type as the input).
ROUND ( expression [, precision] )Parameters
-
expression- any valid numeric expression -
precision- the number of digits after the decimal point to round to
Example
Convert every Player’s age to an integer:
SELECT name, ROUND(age) FROM Player;ROUNDMAGIC
Description
Rounds numbers. Has a special handling algorithm 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 function returns a double.
ROUNDMAGIC (expression)Parameters
expression - any valid numeric expression
Example
Round every Player’s age:
SELECT name, ROUNDMAGIC(AGE/3*3) FROM Player;SECURE_RAND
Description
Generates a number of cryptographically secure random numbers, returns bytes.
SECURE_RAND (int)Parameters
int - the number of digits
Example
Get a truly random number:
SELECT name, SECURE_RAND(10) FROM Player;SIGN
Description
Returns -1 if the value is smaller than zero, 0 if zero, 1 otherwise.
SIGN (expression)Parameters
expression - any valid numeric expression
Example
Get a sign for every value:
SELECT name, SIGN(VALUE) FROM Player;ENCRYPT
Description
Encrypts data using a key. The supported algorithm is AES. The block size is 16 bytes. Returns bytes.
ENCRYPT (algorithmString , keyBytes , dataBytes)Parameters
-
algorithmString- the AES algorithm -
keyBytes- the key -
dataBytes- data block size
Example
Encrypt players names:
SELECT ENCRYPT('AES', '00', STRINGTOUTF8(Name)) FROM Player;DECRYPT
Description
Decrypts data using a key. The supported algorithm is AES. The block size is 16 bytes. Returns bytes.
DECRYPT (algorithmString , keyBytes , dataBytes)Parameters
-
algorithmString- the AES algorithm -
keyBytes- the key -
dataBytes- data block size
Example
Decrypt Players' names:
SELECT DECRYPT('AES', '00', '3fabb4de8f1ee2e97d7793bab2db1116'))) FROM Player;TRUNCATE
Description
Truncates to a number of digits (to the next value closer to 0). Returns a double. When used with a timestamp, truncates the timestamp to the date (day) value. When used with a date, truncates the date to the date (day) value less the time part. When used with a timestamp as string, truncates the timestamp to a date (day) value.
{TRUNC | TRUNCATE} (\{\{numeric, digitsInt} | timestamp | date | timestampString})Parameters
-
digitsInt- the number for digits to truncate to -
timestamp- the timestamp to truncate -
date- the date to truncate to -
timestampString- the timestamp expressed as a string
Example
Truncate teg value to 2 digits:
TRUNCATE(VALUE, 2);COMPRESS
Description
Compresses the data using the specified compression algorithm. The supported algorithms are: LZF (faster but lower compression; default) and DEFLATE (higher degree of compression). Compression does not always reduce size. Very small objects and objects with little redundancy may get larger. This function returns bytes.
COMPRESS(dataBytes [, algorithmString])Parameters
-
dataBytes- the data to compress -
algorithmString- the algorithm to use for compression
Example
Compress STRINGTOUTF8 using the LZF (default) algorithm:
COMPRESS(STRINGTOUTF8('Test'))EXPAND
Description
Expands data that was previously compressed using the the COMPRESS function, returns bytes.
EXPAND(dataBytes)Parameters
dataBytes - the data to expand
Example
Converts the string to UTF8 format, compress it, expand it, and converts it back to Unicode:
UTF8TOSTRING(EXPAND(COMPRESS(STRINGTOUTF8('LZF'))))ZERO
Description
Returns the value of 0. Can be used even if numeric literals are disabled.
ZERO()Example
Return 0:
ZERO()© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.