Numeric Functions in Confluent Cloud for Apache Flink

Confluent Cloud for Apache Flink® provides these built-in numeric functions to use in SQL queries:

Numeric	Trigonometry	Random number generators	Utility
ABS	ACOS	RAND	HEX
BIN	ASIN	RAND(INT)	UUID
CEILING	ATAN	RAND_INTEGER(INT)	UNHEX
E	ATAN2	RAND_INTEGER(INT1, INT2)
EXP	COS
FLOOR	COSH
LN	COT
LOG	DEGREES
LOG10	RADIANS
LOG2	SIN
PERCENTILE	SINH
PI	TAN
POWER	TANH
ROUND
SIGN
SQRT
TRUNCATE

ABS

Gets the absolute value of a number.

Syntax

ABS(numeric)

Copy

Description

The ABS function returns the absolute value of the specified NUMERIC.

Examples

-- returns 23
SELECT ABS(-23);

-- returns 23
SELECT ABS(23);

Copy

ACOS

Computes the arccosine.

Syntax

ACOS(numeric)

Copy

Description

The ACOS function returns the arccosine of the specified NUMERIC.

Examples

-- returns 1.5707963267948966
-- (approximately PI/2)
SELECT ACOS(0);

-- returns 0.0
SELECT ACOS(1);

Copy

ASIN

Computes the arcsine.

Syntax

ASIN(numeric)

Copy

Description

The ASIN function returns the arcsine of the specified NUMERIC.

Examples

-- returns 0.0
SELECT ASIN(0);

-- returns 1.5707963267948966
-- (approximately PI/2)
SELECT ASIN(1);

Copy

ATAN

Computes the arctangent.

Syntax

ATAN(numeric)

Copy

Description

The ATAN function returns the arctangent of the specified NUMERIC.

Examples

-- returns 0.0
SELECT ATAN(0);

-- returns 0.7853981633974483
-- (approximately PI/4)
SELECT ATAN2(1);

Copy

ATAN2

Computes the arctangent of a 2D point.

Syntax

ATAN2(numeric1, numeric2)

Copy

Description

Returns the arctangent of the coordinate specified by (numeric1, numeric2).

Examples

-- returns 0.0
SELECT ATAN2(0, 0);

-- returns 0.7853981633974483
-- (approximately PI/4)
SELECT ATAN2(1, 1);

Copy

BIN

Converts an INTEGER number to binary.

Syntax

BIN(int)

Copy

Description

The BIN function returns a string representation of the specified INTEGER in binary format. Returns NULL if int is NULL.

Examples

-- returns "100"
SELECT BIN(4);

-- returns "1100"
SELECT BIN(12);

Copy

CEILING

Rounds a number up.

Syntax

CEILING(numeric)

Copy

Description

The CEILING function rounds the specified NUMERIC up and returns the smallest integer that’s greater than or equal to the NUMERIC.

This function can be abbreviated to CEIL(numeric).

Examples

-- returns 24
SELECT CEIL(23.55);

-- returns -23
SELECT CEIL(-23.55);

Copy

COS

Computes the cosine of an angle.

Syntax

COS(numeric)

Copy

Description

Returns the cosine of the specified NUMERIC in radians.

Examples

-- returns 1.0
SELECT COS(0);

-- returns 6.123233995736766E-17
-- (approximately 0)
SELECT COS(PI()/2);

Copy

COSH

Computes the hyperbolic cosine.

Syntax

COT(numeric)

Copy

Description

The COSH function returns the hyperbolic cosine of the specified NUMERIC. The return value type is DOUBLE.

Example

-- returns 1.0
SELECT COSH(0);

Copy

COT

Computes the cotangent of an angle.

Syntax

COT(numeric)

Copy

Description

The COT function returns the cotangent of the specified NUMERIC in radians.

Example

-- returns 6.123233995736766E-17
-- (approximately 0)
SELECT COT(PI()/2);

Copy

DEGREES

Converts an angle in radians to degrees.

Syntax

DEGREES(numeric)

Copy

Description

The DEGREES function converts the specified NUMERIC value in radians to degrees.

Examples

-- returns 90.0
SELECT DEGREES(PI()/2);

-- returns 180.0
SELECT DEGREES(PI());

-- returns -45.0
SELECT DEGREES(-PI()/4);

Copy

E

Gets the approximate value of e.

Syntax

E()

Copy

Description

Returns a value that is closer than any other values to e, the base of the natural logarithm.

Examples

-- returns 2.718281828459045
-- which is the approximate value of e
SELECT E();

-- returns 1.0
SELECT LN(E());

Copy

EXP

Computes e raised to a power.

Syntax

EXP(numeric)

Copy

Description

The EXP function returns e, the base of the natural logarithm, raised to the power of the specified NUMERIC.

Examples

-- returns 2.718281828459045
-- which is the approximate value of e
SELECT EXP(1);

-- returns 7.38905609893065
SELECT EXP(2);

-- returns 0.36787944117144233
SELECT EXP(-1);

Copy

FLOOR

Rounds a number down.

Syntax

FLOOR(numeric)

Copy

Description

The FLOOR function rounds the specified NUMERIC down and returns the largest integer that is less than or equal to the NUMERIC.

Examples

-- returns 23
SELECT FLOOR(23.55);

-- returns -24
SELECT FLOOR(-23.55);

Copy

HEX

Converts an integer or string to hexadecimal.

Syntax

HEX(numeric)
HEX(string)

Copy

Description

The HEX function returns a string representation of an integer NUMERIC value or a STRING in hexadecimal format. Returns NULL if the argument is NULL.

Examples

-- returns "14"
SELECT HEX(20);

--  returns "64"
SELECT HEX(100);

-- returns "68656C6C6F2C776F726C64"
SELECT HEX('hello,world');

Copy

Related function

UNHEX

LN

Computes the natural log.

Syntax

LN(numeric)

Copy

Description

The LN function returns the natural logarithm (base e) of the specified NUMERIC.

Examples

-- returns 1.0
SELECT LN(E());

-- returns 0.0
SELECT LN(1);

Copy

LOG

Computes a logarithm.

Syntax

LOG(numeric1, numeric2)

Copy

Description

The LOG function returns the logarithm of numeric2 to the base of numeric1.

When called with one argument, returns the natural logarithm of numeric2.

numeric2 must be greater than 0, and numeric1 must be greater than 1.

Examples

-- returns 1.0
SELECT LOG(10, 10);

-- returns 8.0
SELECT LOG(2, 256);

-- returns 1.0
SELECT LOG(E());

Copy

LOG10

Computes the base-10 logarithm.

Syntax

LOG10(numeric)

Copy

Description

The LOG10 function returns the base-10 logarithm of the specified NUMERIC.

Examples

-- returns 1.0
SELECT LOG10(10);

-- returns 3.0
SELECT LOG(1000);

Copy

LOG2

Computes the base-2 logarithm.

Syntax

LOG2(numeric)

Copy

Description The LOG2 function returns the base-2 logarithm of the specified NUMERIC.

Examples

-- returns 1.0
SELECT LOG2(2);

-- returns 10.0
SELECT LOG2(1024);

Copy

PERCENTILE

Gets a percentile value based on a continuous distribution.

Syntax

PERCENTILE(expr, percentage[, frequency])

Copy

Arguments

• expr: A NUMERIC expression.
• percentage: A NUMERIC expression between 0 and 1, or an ARRAY of NUMERIC expressions, each between 0 and 1.
• frequency: An optional integral number greater than 0 that describes the number of times expr must be counted. The default is 1.

Returns

DOUBLE if percentage is numeric, or an ARRAY of DOUBLE if percentage is an ARRAY.

Description

The PERCENTILE function returns a percentile value based on a continuous distribution of the input column.

If no input row lies exactly at the desired percentile, the result is calculated using linear interpolation of the two nearest input values. NULL values are ignored in the calculation.

Examples

-- returns 6.0
SELECT PERCENTILE(col, 0.3) FROM (VALUES (0), (10), (10)) AS col;

-- returns 6.0
SELECT PERCENTILE(col, 0.3, freq) FROM ( VALUES (0, 1), (10, 2)) AS tab(col, freq);

-- returns [2.5,7.5]
SELECT PERCENTILE(col, ARRAY(0.25, 0.75)) FROM (VALUES (0), (10)) AS col;

-- returns 50.0
SELECT PERCENTILE(age, 0.5) FROM (VALUES 0, 50, 100) AS age;

Copy

PI

Gets the approximate value of pi.

Syntax

PI()

Copy

Description

The PI function returns a value that is closer than any other values to pi.

Examples

-- returns 3.141592653589793
-- (approximately PI)
SELECT PI();

-- returns -1.0
SELECT COS(PI());

Copy

POWER

Raises a number to a power.

Syntax

POWER(numeric1, numeric2)

Copy

Description

The POWER function returns numeric1 raised to the power of numeric2.

Examples

-- returns 1000.0
SELECT POWER(10, 3);

-- returns 256.0
SELECT POWER(2, 8);

-- returns 1.0
SELECT POWER(500, 0);

Copy

RADIANS

Converts an angle in degrees to radians.

Syntax

RADIANS(numeric)

Copy

Description

The RADIANS function converts the specified NUMERIC value in degrees to radians.

Examples

-- returns 3.141592653589793
-- (approximately PI)
SELECT RADIANS(180);

-- returns 0.7853981633974483
-- (approximately PI/4)
SELECT RADIANS(45);

Copy

RAND

Gets a random number.

Syntax

RAND()

Copy

Description

The RAND function returns a pseudorandom DOUBLE value in the range [0.0, 1.0).

Example

-- an example return value is 0.9346105267662114
SELECT RAND();

Copy

RAND(INT)

Gets a random number from a seed.

Syntax

RAND(seed INT)

Copy

Description

The RAND(INT) function returns a pseudorandom DOUBLE value in the range [0.0, 1.0) with the initial seed integer.

Two RAND functions return identical sequences of numbers if they have the same initial seed value.

Examples

-- returns 0.7321323355141605
SELECT RAND(23);

-- returns 0.7275636800328681
SELECT RAND(42);

Copy

RAND_INTEGER(INT)

Gets a pseudorandom integer.

Syntax

RAND_INTEGER(upper_bound INT)

Copy

Description

The RAND_INTEGER(INT) functions returns a pseudorandom integer value in the range [0, upper_bound).

Examples

-- returns 20
SELECT RAND_INTEGER(23);

-- returns 28
SELECT RAND_INTEGER(42);

Copy

RAND_INTEGER(INT1, INT2)

Gets a random integer in a range.

Syntax

RAND_INTEGER(seed INT, upper_bound INT)

Copy

Description

The RAND_INTEGER(INT1, INT2) function returns a pseudorandom integer value in the range [0, upper_bound) with the initial seed value seed.

Two RAND_INTEGER functions return identical sequences of numbers if they have the same initial seed and bound.

Examples

-- returns 227
SELECT RAND_INTEGER(23, 1000);

-- returns 1130
SELECT RAND_INTEGER(42, 10000);

Copy

ROUND

Rounds a number to the specified precision.

Syntax

ROUND(numeric, int)

Copy

Description

The ROUND function returns a number rounded to int decimal places for the specified NUMERIC.

Examples

-- returns 23.6
SELECT ROUND(23.58, 1);

-- returns 3.1416
SELECT ROUND(PI(), 4);

Copy

SIGN

Gets the sign of a number.

Syntax

SIGN(numeric)

Copy

Description

The SIGN function returns the signum of the specified NUMERIC.

Examples

-- returns -1.00
SELECT SIGN(-23.55);

-- returns 1.000
SELECT SIGN(606.808);

Copy

SIN

Compute the sine of an angle.

Syntax

SIN(numeric)

Copy

Description

The SIN function returns the sine of the specified NUMERIC in radians.

Examples

-- returns 1.0
SELECT SIN(PI()/2);

-- returns -1.0
SELECT SIN(-PI()/2);

Copy

SINH

Computes the hyperbolic sine.

Syntax

SINH(numeric)

Copy

Description

The SINH function returns the hyperbolic sine of the specified NUMERIC. The return type is DOUBLE.

Example

-- returns 0.0
SELECT SINH(0);

Copy

SQRT

Computes the square root of a number.

Syntax

SQRT(numeric)

Copy

Description

The SQRT function returns the square root of the specified NUMERIC, which must greater than or equal to 0.

Examples

-- returns 8.0
SELECT SQRT(64);

-- returns 10.0
SELECT SQRT(100);

-- returns 12.0
SELECT SQRT(144);

Copy

TAN

Computes the tangent of an angle.

Syntax

TAN(numeric)

Copy

Description

The TAN function returns the tangent of the specified NUMERIC in radians.

Examples

-- returns 0.0
SELECT TAN(0);

-- returns 0.9999999999999999
SELECT TAN(PI()/4);

Copy

TANH

Computes the hyperbolic tangent.

Syntax

TANH(numeric)

Copy

Description

The TANH function returns the hyperbolic tangent of the specified NUMERIC. The return type is DOUBLE.

Examples

-- returns 0.0
SELECT TANH(0);

-- returns 0.9999092042625951
SELECT TANH(5);

Copy

TRUNCATE

Truncates a number to the specified precision.

Syntax

TRUNCATE(numeric, integer)

Copy

Description

The TRUNCATE(numeric, integer) function returns the specified NUMERIC truncated to the number of decimal places specified by integer. Returns NULL if numeric or integer is NULL.

If integer is 0, the result has no decimal point or fractional part.

The integer value can be negative, which causes integer digits to the left of the decimal point to become zero.

If integer is not set, the function truncates as if integer were 0.

Examples

--  returns 42.32
SELECT TRUNCATE(42.324, 2);

-- returns 42.0
SELECT TRUNCATE(42.324);

-- returns 40
SELECT TRUNCATE(42.324, -1);

Copy

UNHEX

Converts a hexadecimal expression to BINARY.

Syntax

UNHEX(str)

Copy

Arguments

str: a hexadecimal STRING. The characters in str must be legal hexadecimal digits: 0 - 9, A - F, and a - f.

Returns

A BINARY string. If str contains any nonhexadecimal digits, or is NULL, the return value is NULL.

Description

The UNHEX function interprets each pair of characters in str as a hexadecimal number and converts it to the byte represented by the number.

If the length of str is odd, the first character is discarded, and the result is left-padded with a NULL byte.

Examples

-- returns "Flink"
SELECT DECODE(UNHEX('466C696E6B') , 'UTF-8');

-- returns NULL
SELECT UNHEX('ZZ');

Copy

Related functions

• DECODE
• HEX

UUID

Generates a UUID.

Syntax

UUID()

Copy

Description

The UUID() function returns a Universally Unique Identifier (UUID) string that conforms to the RFC 4122 type 4 specification.

The UUID is generated using a cryptographically strong pseudo-random number generator.

Examples

-- an example return value is
-- 3d3c68f7-f608-473f-b60c-b0c44ad4cc4e
SELECT UUID();

Copy

Other built-in functions

• Aggregate Functions
• Collection Functions
• Comparison Functions
• Conditional Functions
• Datetime Functions
• Hash Functions
• JSON Functions
• Model Inference Functions
• Numeric Functions
• String Functions
• Table API Functions

Related content

• User-defined Functions
• Create a User Defined Function

Note

This website includes content developed at the Apache Software Foundation under the terms of the Apache License v2.