This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Numeric Functions

1: ABS
2: ACOS
3: ADD
4: ASIN
5: ATAN
6: ATAN2
7: CBRT
8: CEIL
9: CEILING
10: COS
11: COT
12: CRC32
13: DEGREES
14: DIV
15: DIV0
16: DIVNULL
17: EXP
18: FACTORIAL
19: FLOOR
20: INTDIV
21: LN
22: LOG(b, x)
23: LOG(x)
24: LOG10
25: LOG2
26: MINUS
27: MOD
28: MODULO
29: NEG
30: NEGATE
31: PI
32: PLUS
33: POW
34: POWER
35: RADIANS
36: RAND()
37: RAND(n)
38: ROUND
39: SIGN
40: SIN
41: SQRT
42: SUBTRACT
43: TAN
44: TRUNCATE

This section provides reference information for the numeric functions in PlaidCloud Lakehouse.

1 - ABS

Returns the absolute value of x.

Analyze Syntax

func.abs( <x> )

Analyze Examples

func.abs((- 5))

┌─────────────────┐
│ func.abs((- 5)) │
├─────────────────┤
│               5 │
└─────────────────┘

SQL Syntax

ABS( <x> )

SQL Examples

SELECT ABS(-5);

┌────────────┐
│ abs((- 5)) │
├────────────┤
│          5 │
└────────────┘

2 - ACOS

Returns the arc cosine of x, that is, the value whose cosine is x. Returns NULL if x is not in the range -1 to 1.

Analyze Syntax

func.abs( <x> )

Analyze Examples

func.abs(1)

┌──────────────┐
│ func.acos(1) │
├──────────────┤
│            0 │
└──────────────┘

SQL Syntax

ACOS( <x> )

SQL Examples

SELECT ACOS(1);

┌─────────┐
│ acos(1) │
├─────────┤
│       0 │
└─────────┘

3 - ADD

Alias for PLUS.

4 - ASIN

Returns the arc sine of x, that is, the value whose sine is x. Returns NULL if x is not in the range -1 to 1.

Analyze Syntax

func.asin( <x> )

Analyze Examples

func.asin(0.2)

┌────────────────────┐
│   func.asin(0.2)   │
├────────────────────┤
│ 0.2013579207903308 │
└────────────────────┘

SQL Syntax

ASIN( <x> )

SQL Examples

SELECT ASIN(0.2);

┌────────────────────┐
│      asin(0.2)     │
├────────────────────┤
│ 0.2013579207903308 │
└────────────────────┘

5 - ATAN

Returns the arc tangent of x, that is, the value whose tangent is x.

Analyze Syntax

func.atan( <x> )

Analyze Examples

func.atan(-2)

┌─────────────────────┐
│  func.atan((- 2))   │
├─────────────────────┤
│ -1.1071487177940906 │
└─────────────────────┘

SQL Syntax

ATAN( <x> )

SQL Examples

SELECT ATAN(-2);

┌─────────────────────┐
│     atan((- 2))     │
├─────────────────────┤
│ -1.1071487177940906 │
└─────────────────────┘

6 - ATAN2

Returns the arc tangent of the two variables x and y. It is similar to calculating the arc tangent of y / x, except that the signs of both arguments are used to determine the quadrant of the result. ATAN(y, x) is a synonym for ATAN2(y, x).

Analyze Syntax

func.atan2( <y, x> )

Analyze Examples

func.atan2((- 2), 2)

┌─────────────────────┐
│ func.atan2((- 2), 2)│
├─────────────────────┤
│ -0.7853981633974483 │
└─────────────────────┘

SQL Syntax

ATAN2( <y, x> )

SQL Examples

SELECT ATAN2(-2, 2);

┌─────────────────────┐
│   atan2((- 2), 2)   │
├─────────────────────┤
│ -0.7853981633974483 │
└─────────────────────┘

7 - CBRT

Returns the cube root of a nonnegative number x.

Analyze Syntax

func.cbrt( <x> )

Analyze Examples

func.cbrt(27)

┌───────────────┐
│ func.cbrt(27) │
├───────────────┤
│             3 │
└───────────────┘

SQL Syntax

CBRT( <x> )

SQL Examples

SELECT CBRT(27);

┌──────────┐
│ cbrt(27) │
├──────────┤
│        3 │
└──────────┘

8 - CEIL

Rounds the number up.

Analyze Syntax

func.ceil( <x> )

Analyze Examples

func.ceil((- 1.23))

┌─────────────────────┐
│ func.ceil((- 1.23)) │
├─────────────────────┤
│                  -1 │ 
└─────────────────────┘

SQL Syntax

CEIL( <x> )

Aliases

CEILING

SQL Examples

SELECT CEILING(-1.23), CEIL(-1.23);

┌────────────────────────────────────┐
│ ceiling((- 1.23)) │ ceil((- 1.23)) │
├───────────────────┼────────────────┤
│                -1 │             -1 │
└────────────────────────────────────┘

9 - CEILING

Alias for CEIL.

10 - COS

Returns the cosine of x, where x is given in radians.

Analyze Syntax

func.cos( <x> )

Analyze Examples

func.cos(func.pi())

┌─────────────────────┐
│ func.cos(func.pi()) │
├─────────────────────┤
│                  -1 │
└─────────────────────┘

SQL Syntax

COS( <x> )

SQL Examples

SELECT COS(PI());

┌───────────┐
│ cos(pi()) │
├───────────┤
│        -1 │
└───────────┘

11 - COT

Returns the cotangent of x, where x is given in radians.

Analyze Syntax

func.cot( <x> )

Analyze Examples

func.cot(12) 

┌─────────────────────┐
│    func.cot(12)     │
├─────────────────────┤
│ -1.5726734063976895 │
└─────────────────────┘

SQL Syntax

COT( <x> )

SQL Examples

SELECT COT(12);

┌─────────────────────┐
│       cot(12)       │
├─────────────────────┤
│ -1.5726734063976895 │
└─────────────────────┘

12 - CRC32

Returns the CRC32 checksum of x, where 'x' is expected to be a string and (if possible) is treated as one if it is not.

Analyze Syntax

func.crc32( '<x>' )

Analyze Examples

func.crc32('databend')

┌────────────────────────┐
│ func.crc32('databend') │
├────────────────────────┤
│             1177678456 │
└────────────────────────┘

SQL Syntax

CRC32( '<x>' )

SQL Examples

SELECT CRC32('databend');

┌───────────────────┐
│ crc32('databend') │
├───────────────────┤
│        1177678456 │
└───────────────────┘

13 - DEGREES

Returns the argument x, converted from radians to degrees, where x is given in radians.

Analyze Syntax

func.degrees( <x> )

Analyze Examples

func.degrees(func.pi())

┌─────────────────────────┐
│ func.degrees(func.pi()) │
├─────────────────────────┤
│                     180 │
└─────────────────────────┘

SQL Syntax

DEGREES( <x> )

SQL Examples

SELECT DEGREES(PI());

┌───────────────┐
│ degrees(pi()) │
├───────────────┤
│           180 │
└───────────────┘

14 - DIV

Returns the quotient by dividing the first number by the second one, rounding down to the closest smaller integer. Equivalent to the division operator //.

SQL Syntax

func.div(<numerator>, <denominator>)

Analyze Examples

# Equivalent to the division operator "//"
func.div(6.1, 2)

┌───────────────────────────────┐
│ func.div(6.1, 2) │ (6.1 // 2) │
├──────────────────┼────────────┤
│                3 │          3 │
└───────────────────────────────┘

# Error when divided by 0
error: APIError: ResponseError with 1006: divided by zero while evaluating function `div(6.1, 0)`

Analyze Syntax

<number1> DIV <number2>

Aliases

INTDIV

SQL Examples

-- Equivalent to the division operator "//"
SELECT 6.1 DIV 2, 6.1//2;

┌──────────────────────────┐
│ (6.1 div 2) │ (6.1 // 2) │
├─────────────┼────────────┤
│           3 │          3 │
└──────────────────────────┘

SELECT 6.1 DIV 2, INTDIV(6.1, 2), 6.1 DIV NULL;

┌───────────────────────────────────────────────┐
│ (6.1 div 2) │ intdiv(6.1, 2) │ (6.1 div null) │
├─────────────┼────────────────┼────────────────┤
│           3 │              3 │ NULL           │
└───────────────────────────────────────────────┘

-- Error when divided by 0
root@localhost:8000/default> SELECT 6.1 DIV 0;
error: APIError: ResponseError with 1006: divided by zero while evaluating function `div(6.1, 0)`

15 - DIV0

import FunctionDescription from '@site/src/components/FunctionDescription';

Returns the quotient by dividing the first number by the second one. Returns 0 if the second number is 0.

Analyze Syntax

func.div0(<numerator>, <denominator>)

Analyze Examples

func.div0(20, 6), func.div0(20, 0), func.div0(20, null)

┌─────────────────────────────────────────────────────────────┐
│  func.div0(20, 6)  │ func.div0(20, 0) │ func.div0(20, null) │
├────────────────────┼──────────────────┼─────────────────────┤
│ 3.3333333333333335 │                0 │ NULL                │
└─────────────────────────────────────────────────────────────┘

SQL Syntax

DIV0(<number1>, <number2>)

SQL Examples

SELECT
  DIV0(20, 6),
  DIV0(20, 0),
  DIV0(20, NULL);

┌───────────────────────────────────────────────────┐
│     div0(20, 6)    │ div0(20, 0) │ div0(20, null) │
├────────────────────┼─────────────┼────────────────┤
│ 3.3333333333333335 │           0 │ NULL           │
└───────────────────────────────────────────────────┘

16 - DIVNULL

import FunctionDescription from '@site/src/components/FunctionDescription';

Returns the quotient by dividing the first number by the second one. Returns NULL if the second number is 0 or NULL.

Analyze Syntax

func.divnull(<numerator>, <denominator>)

Analyze Examples

func.divnull(20, 6), func.divnull(20, 0), func.divnull(20, null)

┌───────────────────────────────────────────────────────────────────┐
│ func.divnull(20, 6)│ func.divnull(20, 0) │ func.divnull(20, null) │
├────────────────────┼─────────────────────┼────────────────────────┤
│ 3.3333333333333335 │ NULL                │ NULL                   │
└───────────────────────────────────────────────────────────────────┘

SQL Syntax

DIVNULL(<number1>, <number2>)

SQL Examples

SELECT
  DIVNULL(20, 6),
  DIVNULL(20, 0),
  DIVNULL(20, NULL);

┌─────────────────────────────────────────────────────────┐
│   divnull(20, 6)   │ divnull(20, 0) │ divnull(20, null) │
├────────────────────┼────────────────┼───────────────────┤
│ 3.3333333333333335 │ NULL           │ NULL              │
└─────────────────────────────────────────────────────────┘

17 - EXP

Returns the value of e (the base of natural logarithms) raised to the power of x.

Analyze Syntax

func.exp( <x> )

Analyze Examples

func.exp(2)

┌──────────────────┐
│   func.exp(2)    │
├──────────────────┤
│ 7.38905609893065 │
└──────────────────┘

SQL Syntax

EXP( <x> )

SQL Examples

SELECT EXP(2);

┌──────────────────┐
│      exp(2)      │
├──────────────────┤
│ 7.38905609893065 │
└──────────────────┘

18 - FACTORIAL

Returns the factorial logarithm of x. If x is less than or equal to 0, the function returns 0.

Analyze Syntax

func.factorial( <x> )

Analyze Examples

func.factorial(5)

┌───────────────────┐
│ func.factorial(5) │
├───────────────────┤
│               120 │
└───────────────────┘

SQL Syntax

FACTORIAL( <x> )

SQL Examples

SELECT FACTORIAL(5);

┌──────────────┐
│ factorial(5) │
├──────────────┤
│          120 │
└──────────────┘

19 - FLOOR

Rounds the number down.

Analyze Syntax

func.floor( <x> )

Analyze Examples

func.floor(1.23)

┌──────────────────┐
│ func.floor(1.23) │
├──────────────────┤
│                1 │
└──────────────────┘

SQL Syntax

FLOOR( <x> )

SQL Examples

SELECT FLOOR(1.23);

┌─────────────┐
│ floor(1.23) │
├─────────────┤
│           1 │
└─────────────┘

20 - INTDIV

Alias for DIV.

21 - LN

Returns the natural logarithm of x; that is, the base-e logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL.

Analyze Syntax

func.ln( <x> )

Analyze Examples

func.ln(2)

┌────────────────────┐
│     func.ln(2)     │
├────────────────────┤
│ 0.6931471805599453 │
└────────────────────┘

SQL Syntax

LN( <x> )

SQL Examples

SELECT LN(2);

┌────────────────────┐
│        ln(2)       │
├────────────────────┤
│ 0.6931471805599453 │
└────────────────────┘

22 - LOG(b, x)

Returns the base-b logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL.

Analyze Syntax

func.log( <b, x> )

Analyze Examples

func.log(2, 65536)

┌────────────────────┐
│ func.log(2, 65536) │
├────────────────────┤
│                 16 │
└────────────────────┘

SQL Syntax

LOG( <b, x> )

SQL Examples

SELECT LOG(2, 65536);

┌───────────────┐
│ log(2, 65536) │
├───────────────┤
│            16 │
└───────────────┘

23 - LOG(x)

Returns the natural logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL.

Analyze Syntax

 func.log( <x> )

Analyze Examples

 func.log(2)

┌────────────────────┐
│    func.log(2)     │
├────────────────────┤
│ 0.6931471805599453 │
└────────────────────┘

SQL Syntax

LOG( <x> )

SQL Examples

SELECT LOG(2);

┌────────────────────┐
│       log(2)       │
├────────────────────┤
│ 0.6931471805599453 │
└────────────────────┘

24 - LOG10

Returns the base-10 logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL.

Analyze Syntax

func.log10( <x> )

Analyze Examples

func.log10(100)

┌─────────────────┐
│ func.log10(100) │
├─────────────────┤
│               2 │
└─────────────────┘

SQL Syntax

LOG10( <x> )

SQL Examples

SELECT LOG10(100);

┌────────────┐
│ log10(100) │
├────────────┤
│          2 │
└────────────┘

25 - LOG2

Returns the base-2 logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL.

Analyze Syntax

func.log2( <x> )

Analyze Examples

func.log2(65536)

┌──────────────────┐
│ func.log2(65536) │
├──────────────────┤
│               16 │
└──────────────────┘

SQL Syntax

LOG2( <x> )

SQL Examples

SELECT LOG2(65536);

┌─────────────┐
│ log2(65536) │
├─────────────┤
│          16 │
└─────────────┘

26 - MINUS

Negates a numeric value.

Analyze Syntax

func.minus( <x> )

Analyze Examples

func.minus(func.pi())

┌─────────────────────────┐
│  func.minus(func.pi())  │
├─────────────────────────┤
│      -3.141592653589793 │
└─────────────────────────┘

SQL Syntax

MINUS( <x> )

Aliases

SQL Examples

SELECT MINUS(PI()), NEG(PI()), NEGATE(PI()), SUBTRACT(PI());

┌───────────────────────────────────────────────────────────────────────────────────┐
│     minus(pi())    │      neg(pi())     │    negate(pi())    │   subtract(pi())   │
├────────────────────┼────────────────────┼────────────────────┼────────────────────┤
│ -3.141592653589793 │ -3.141592653589793 │ -3.141592653589793 │ -3.141592653589793 │
└───────────────────────────────────────────────────────────────────────────────────┘

27 - MOD

Alias for MODULO.

28 - MODULO

Returns the remainder of x divided by y. If y is 0, it returns an error.

Analyze Syntax

func.modulo( <x>, <y> )

Analyze Examples

func.modulo(9, 2)

┌───────────────────┐
│ func.modulo(9, 2) │
├───────────────────┤
│                 1 │
└───────────────────┘

SQL Syntax

MODULO( <x>, <y> )

Aliases

SQL Examples

SELECT MOD(9, 2), MODULO(9, 2);

┌──────────────────────────┐
│ mod(9, 2) │ modulo(9, 2) │
├───────────┼──────────────┤
│         1 │            1 │
└──────────────────────────┘

29 - NEG

Alias for MINUS.

30 - NEGATE

Alias for MINUS.

31 - PI

Returns the value of π as a floating-point value.

Analyze Syntax

func.pi()

Analyze Examples

func.pi()

┌───────────────────┐
│     func.pi()     │
├───────────────────┤
│ 3.141592653589793 │
└───────────────────┘

SQL Syntax

PI()

SQL Examples

SELECT PI();

┌───────────────────┐
│        pi()       │
├───────────────────┤
│ 3.141592653589793 │
└───────────────────┘

32 - PLUS

Calculates the sum of two numeric or decimal values.

Analyze Syntax

func.plus(<number1>, <number2>)

Analyze Examples

func.plus(1, 2.3)

┌────────────────────┐
│  func.plus(1, 2.3) │
├────────────────────┤
│ 3.3                │
└────────────────────┘

SQL Syntax

PLUS(<number1>, <number2>)

Aliases

SQL Examples

SELECT ADD(1, 2.3), PLUS(1, 2.3);

┌───────────────────────────────┐
│  add(1, 2.3)  │  plus(1, 2.3) │
├───────────────┼───────────────┤
│ 3.3           │ 3.3           │
└───────────────────────────────┘

33 - POW

Returns the value of x to the power of y.

Analyze Syntax

func.pow( <x, y> )

Analyze Examples

func.pow(-2, 2)

┌────────────────────┐
│ func.pow((- 2), 2) │
├────────────────────┤
│                  4 │ 
└────────────────────┘

SQL Syntax

POW( <x, y> )

Aliases

POWER

SQL Examples

SELECT POW(-2, 2), POWER(-2, 2);

┌─────────────────────────────────┐
│ pow((- 2), 2) │ power((- 2), 2) │
├───────────────┼─────────────────┤
│             4 │               4 │
└─────────────────────────────────┘

34 - POWER

Alias for POW.

35 - RADIANS

Returns the argument x, converted from degrees to radians.

Analyze Syntax

func.radians( <x> )

Analyze Examples

func.radians(90)

┌────────────────────┐
│  func.radians(90)  │
├────────────────────┤
│ 1.5707963267948966 │
└────────────────────┘

SQL Syntax

RADIANS( <x> )

SQL Examples

SELECT RADIANS(90);

┌────────────────────┐
│     radians(90)    │
├────────────────────┤
│ 1.5707963267948966 │
└────────────────────┘

36 - RAND()

Returns a random floating-point value v in the range 0 <= v < 1.0. To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j − i)).

Analyze Syntax

func.rand()

Analyze Examples

func.rand()

┌────────────────────┐
│    func.rand()     │
├────────────────────┤
│ 0.5191511074382174 │
└────────────────────┘

SQL Syntax

RAND()

SQL Examples

SELECT RAND();

┌────────────────────┐
│       rand()       │
├────────────────────┤
│ 0.5191511074382174 │
└────────────────────┘

37 - RAND(n)

Returns a random floating-point value v in the range 0 <= v < 1.0. To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j − i)). Argument n is used as the seed value. For equal argument values, RAND(n) returns the same value each time , and thus produces a repeatable sequence of column values.

Analyze Syntax

func.rand( <n>)

Analyze Examples

func.rand(1)

┌────────────────────┐
│    func.rand(1)    │
├────────────────────┤
│ 0.7133693869548766 │
└────────────────────┘

SQL Syntax

RAND( <n>)

SQL Examples

SELECT RAND(1);

┌────────────────────┐
│       rand(1)      │
├────────────────────┤
│ 0.7133693869548766 │
└────────────────────┘

38 - ROUND

Rounds the argument x to d decimal places. The rounding algorithm depends on the data type of x. d defaults to 0 if not specified. d can be negative to cause d digits left of the decimal point of the value x to become zero. The maximum absolute value for d is 30; any digits in excess of 30 (or -30) are truncated.

When using this function's result in calculations, be aware of potential precision issues due to its return data type being DOUBLE, which may affect final accuracy:

SELECT ROUND(4/7, 4) - ROUND(3/7, 4); -- Result: 0.14280000000000004
SELECT ROUND(4/7, 4)::DECIMAL(8,4) - ROUND(3/7, 4)::DECIMAL(8,4); -- Result: 0.1428

Analyze Syntax

func.round( <x, d> )

Analyze Examples

func.round(0.123, 2)

┌──────────────────────┐
│ func.round(0.123, 2) │
├──────────────────────┤
│                 0.12 │
└──────────────────────┘

SQL Syntax

ROUND( <x, d> )

SQL Examples

SELECT ROUND(0.123, 2);

┌─────────────────┐
│ round(0.123, 2) │
├─────────────────┤
│ 0.12            │
└─────────────────┘

39 - SIGN

Returns the sign of the argument as -1, 0, or 1, depending on whether x is negative, zero, or positive or NULL if the argument was NULL.

Analyze Syntax

func.sign( <x> )

Analyze Examples

func.sign(0)

┌──────────────┐
│ func.sign(0) │
├──────────────┤
│            0 │
└──────────────┘

SQL Syntax

SIGN( <x> )

SQL Examples

SELECT SIGN(0);

┌─────────┐
│ sign(0) │
├─────────┤
│       0 │
└─────────┘

40 - SIN

Returns the sine of x, where x is given in radians.

Analyze Syntax

func.sin( <x> )

Analyze Examples

func.sin(90)

┌────────────────────┐
│    func.sin(90)    │
├────────────────────┤
│ 0.8939966636005579 │
└────────────────────┘

SQL Syntax

SIN( <x> )

SQL Examples

SELECT SIN(90);

┌────────────────────┐
│       sin(90)      │
├────────────────────┤
│ 0.8939966636005579 │
└────────────────────┘

41 - SQRT

Returns the square root of a nonnegative number x. Returns Nan for negative input.

Analyze Syntax

func.sqrt( <x> )

Analyze Examples

func.sqrt(4)

┌──────────────┐
│ func.sqrt(4) │
├──────────────┤
│            2 │
└──────────────┘

SQL Syntax

SQRT( <x> )

SQL Examples

SELECT SQRT(4);

┌─────────┐
│ sqrt(4) │
├─────────┤
│       2 │
└─────────┘

42 - SUBTRACT

Alias for MINUS.

43 - TAN

Returns the tangent of x, where x is given in radians.

Analyze Syntax

func.tan( <x> )

Analyze Examples

func.tan(90)

┌────────────────────┐
│    func.tan(90)    │
├────────────────────┤
│ -1.995200412208242 │
└────────────────────┘

SQL Syntax

TAN( <x> )

SQL Examples

SELECT TAN(90);

┌────────────────────┐
│       tan(90)      │
├────────────────────┤
│ -1.995200412208242 │
└────────────────────┘

44 - TRUNCATE

Returns the number x, truncated to d decimal places. If d is 0, the result has no decimal point or fractional part. d can be negative to cause d digits left of the decimal point of the value x to become zero. The maximum absolute value for d is 30; any digits in excess of 30 (or -30) are truncated.

Analyze Syntax

func.truncate( <x, d> )

Analyze Examples

func.truncate(1.223, 1)

┌─────────────────────────┐
│ func.truncate(1.223, 1) │
├─────────────────────────┤
│                     1.2 │
└─────────────────────────┘

SQL Syntax

TRUNCATE( <x, d> )

SQL Examples

SELECT TRUNCATE(1.223, 1);

┌────────────────────┐
│ truncate(1.223, 1) │
├────────────────────┤
│ 1.2                │
└────────────────────┘