Mathematical functions

• 1: ABS
• 2: ACOS
• 3: ASIN
• 4: ATAN
• 5: ATAN2
• 6: CBRT
• 7: CEILING
• 8: COS
• 9: COSH
• 10: COT
• 11: DEGREES
• 12: DISTANCE
• 13: DISTANCEV
• 14: EXP
• 15: FLOOR
• 16: HASH
• 17: LN
• 18: LOG
• 19: LOG10
• 20: MOD
• 21: PI
• 22: POWER
• 23: RADIANS
• 24: RANDOM
• 25: RANDOMINT
• 26: RANDOMINT_CRYPTO
• 27: ROUND
• 28: SIGN
• 29: SIN
• 30: SINH
• 31: SQRT
• 32: TAN
• 33: TANH
• 34: TRUNC
• 35: WIDTH_BUCKET

1 - ABS

Returns the absolute value of the argument. The return value has the same data type as the argument..

Behavior type

Syntax

ABS ( expression )

Parameters

expression

Is a value of type INTEGER or DOUBLE PRECISION

Examples

SELECT ABS(-28.7);
 abs
------
 28.7
(1 row)

2 - ACOS

Returns a DOUBLE PRECISION value representing the trigonometric inverse cosine of the argument.

Behavior type

Syntax

ACOS ( expression )

Parameters

expression

Is a value of type DOUBLE PRECISION

Examples

SELECT ACOS (1);
 acos
------
    0
(1 row)

3 - ASIN

Returns a DOUBLE PRECISION value representing the trigonometric inverse sine of the argument.

Behavior type

Syntax

ASIN ( expression )

Parameters

expression

Is a value of type DOUBLE PRECISION

Examples

SELECT ASIN(1);
      asin
-----------------
 1.5707963267949
(1 row)

4 - ATAN

Returns a DOUBLE PRECISION value representing the trigonometric inverse tangent of the argument.

Behavior type

Syntax

ATAN ( expression )

Parameters

expression

DOUBLE PRECISION value

Examples

SELECT ATAN(1);
       atan
-------------------
 0.785398163397448
(1 row)

5 - ATAN2

Returns a DOUBLE PRECISION value representing the trigonometric inverse tangent of the arithmetic dividend of the arguments.

Behavior type

Syntax

ATAN2 ( quotient, divisor )

Parameters

quotient

Is an expression of type DOUBLE PRECISION representing the quotient

divisor

Is an expression of type DOUBLE PRECISION representing the divisor

Examples

SELECT ATAN2(2,1);
      ATAN2
------------------
 1.10714871779409
(1 row)

6 - CBRT

Returns the cube root of the argument. The return value has the type DOUBLE PRECISION.

Behavior type

Syntax

CBRT ( expression )

Parameters

expression

Value of type DOUBLE PRECISION

Examples

SELECT CBRT(27.0);
 cbrt
------
    3
(1 row)

7 - CEILING

Rounds up the returned value up to the next whole number. For example, given arguments of 5.01 and 5.99, CEILING returns 6. CEILING is the opposite of FLOOR, which rounds down the returned value.

Behavior type

Syntax

CEIL[ING] ( expression )

Parameters

expression

Resolves to an INTEGER or DOUBLE PRECISION value.

Examples

=> SELECT CEIL(-42.8);
 CEIL
------
  -42
(1 row)
SELECT CEIL(48.01);
 CEIL
------
   49
(1 row)

8 - COS

Returns a DOUBLE PRECISION value tat represents the trigonometric cosine of the passed parameter.

Behavior type

Syntax

COS ( expression )

Parameters

expression

An expression of type DOUBLE PRECISION

Examples

SELECT COS(-1);
       COS
------------------
 0.54030230586814
(1 row)

9 - COSH

Returns a DOUBLE PRECISION value that represents the hyperbolic cosine of the passed parameter.

Behavior type

Syntax

COSH ( expression )

Parameters

expression

An expression of type DOUBLE PRECISION

Examples

=> SELECT COSH(-1);
       COSH
------------------
 1.54308063481524

10 - COT

Returns a DOUBLE PRECISION value representing the trigonometric cotangent of the argument.

Behavior type

Syntax

COT ( expression )

Parameters

expression

Is a value of type DOUBLE PRECISION

Examples

SELECT COT(1);
        cot
-------------------
 0.642092615934331
(1 row)

11 - DEGREES

Converts an expression from radians to fractional degrees, or from degrees, minutes, and seconds to fractional degrees. The return value has the type DOUBLE PRECISION.

Behavior type

Syntax

DEGREES ( { radians | degrees, minutes, seconds } )

Parameters

radians

Unit of angular measure. 2*π* radians is equal to a full rotation.

degrees

Unit of angular measure, equal to 1/360 of a full rotation.

minutes

Unit of angular measurement, representing 1/60 of a degree.

seconds

Unit of angular measurement, representing 1/60 of a minute.

Examples

SELECT DEGREES(0.5);
     DEGREES
------------------
 28.6478897565412
(1 row)

SELECT DEGREES(1,2,3);
     DEGREES
------------------
 1.03416666666667
(1 row)

12 - DISTANCE

Returns the distance (in kilometers) between two points. You specify the latitude and longitude of the starting point and the ending point. You can also specify the radius of curvature for greater accuracy when using an ellipsoidal model.

Behavior type

Syntax

DISTANCE ( lat0, lon0, lat1, lon1 [, radius-of-curvature ] )

Parameters

lat0

Starting point latitude.

lon0

Starting point longitude.

lat1

Ending point latitude

lon1

Ending point longitude.

radius-of-curvature

Specifies the radius of the curvature of the earth at the midpoint between the starting and ending points. This parameter allows for greater accuracy when using an ellipsoidal earth model. If you omit this parameter, DISTANCE uses the WGS-84 average r1 radius, about 6371.009 km.

Examples

This example finds the distance in kilometers for 1 degree of longitude at latitude 45 degrees, assuming earth is spherical.

SELECT DISTANCE(45,0,45,1);
      DISTANCE
----------------------
 78.6262959272162
(1 row)

13 - DISTANCEV

Returns the distance (in kilometers) between two points using the Vincenty formula. Because the Vincenty formula includes the parameters of the WGS-84 ellipsoid model, you need not specify a radius of curvature. You specify the latitude and longitude of both the starting point and the ending point. This function is more accurate, but will be slower, than the DISTANCE function.

Behavior type

Syntax

DISTANCEV (lat0, lon0, lat1, lon1);

Parameters

lat0

Specifies the latitude of the starting point.

lon0

Specifies the longitude of the starting point.

lat1

Specifies the latitude of the ending point.

lon1

Specifies the longitude of the ending point.

Examples

This example finds the distance in kilometers for 1 degree of longitude at latitude 45 degrees, assuming earth is ellipsoidal.

SELECT DISTANCEV(45,0, 45,1);
    distanceV
------------------
 78.8463347095916
(1 row)

14 - EXP

Returns the exponential function, e to the power of a number. The return value has the same data type as the argument.

Behavior type

Syntax

EXP ( exponent )

Parameters

exponent

Is an expression of type INTEGER or DOUBLE PRECISION

Examples

SELECT EXP(1.0);
       exp
------------------
 2.71828182845905
(1 row)

15 - FLOOR

Rounds down the returned value to the previous whole number. For example, given arguments of 5.01 and 5.99, FLOOR returns 5. FLOOR is the opposite of CEILING, which rounds up the returned value.

Behavior type

Syntax

FLOOR ( expression )

Parameters

expression

Resolves to an INTEGER or DOUBLE PRECISION value.

Examples

=> SELECT FLOOR((TIMESTAMP '2005-01-17 10:00' - TIMESTAMP '2005-01-01') / INTERVAL '7');
 FLOOR
-------
     2
(1 row)

=> SELECT FLOOR(-42.8);
 FLOOR
-------
   -43
(1 row)

=> SELECT FLOOR(42.8);
 FLOOR
-------
    42
(1 row)

Although the following example looks like an INTEGER, the number on the left is 2^49 as an INTEGER, while the number on the right is a FLOAT:

=> SELECT 1<<49, FLOOR(1 << 49);
    ?column?     |      floor
-----------------+-----------------
 562949953421312 | 562949953421312
(1 row)

Compare the previous example to:

=> SELECT 1<<50, FLOOR(1 << 50);
     ?column?     |        floor
------------------+----------------------
 1125899906842624 | 1.12589990684262e+15
(1 row)

16 - HASH

Calculates a hash value over the function arguments, producing a value in the range 0 <= x < 263.

The HASH function is typically used to segment a projection over a set of cluster nodes. The function selects a specific node for each row based on the values of the row columns. The HASH function distributes data evenly across the cluster, which facilitates optimal query execution.

Behavior type

Syntax

HASH ( { * | expression[,...] } )

Arguments

* | expression[,...]

One of the following:

• * (asterisk)

  Specifies to hash all columns in the queried table.

• expression

  An expression of any data type. Functions that are included in expression must be deterministic. If specified in a projection's hash segmentation clause, each expression typically resolves to a column reference.

Examples

=> SELECT HASH(product_price, product_cost) FROM product_dimension
     WHERE product_price = '11';
        hash
---------------------
 4157497907121511878
 1799398249227328285
 3250220637492749639
(3 rows)

See also

17 - LN

Returns the natural logarithm of the argument. The return data type is the same as the argument.

Behavior type

Syntax

LN ( expression )

Parameters

expression

Is an expression of type INTEGER or DOUBLE PRECISION

Examples

SELECT LN(2);
        ln
-------------------
 0.693147180559945
(1 row)

18 - LOG

Returns the logarithm to the specified base of the argument. The data type of the return value is the same data type as the passed parameter.

Behavior type

Syntax

LOG ( [ base, ] expression )

Parameters

base

Specifies the base (default is base 10)

expression

An expression of type INTEGER or DOUBLE PRECISION

Examples

=> SELECT LOG(2.0, 64);
 LOG
-----
   6
(1 row)
SELECT LOG(100);
 LOG
-----
   2
(1 row)

19 - LOG10

Returns the base 10 logarithm of the argument, also known as the common logarithm. The data type of the return value is the same as the data type of the passed parameter.

Behavior type

Syntax

LOG10 ( expression )

Parameters

expression

An expression of type INTEGER or DOUBLE PRECISION

Examples

=> SELECT LOG10(30);
       LOG10
------------------
 1.47712125471966
(1 row)

20 - MOD

Returns the remainder of a division operation.

Behavior type

Syntax

MOD( expression1, expression2 )

Parameters

expression1

A numeric data type that specifies the dividend.

expression2

A numeric data type that specifies the divisor.

Computation rules

When computing MOD(expression1, expression2), the following rules apply:

• If either expression1 or expression2 is the null value, then the result is the null value.

• If expression2 is zero, then an exception condition is raised: data exception — division by zero.

• Otherwise, the result is the unique exact numeric value R with scale 0 (zero) such that all of the following are true:

  • R has the same sign as expression2.

  • The absolute value of R is less than the absolute value of expression1.

  • expression2 = expression1 * K + R for some exact numeric value K with scale 0 (zero).

Examples

SELECT MOD(9,4);
 mod
-----
   1
(1 row)

SELECT MOD(10,3);
 mod
-----
   1
(1 row)

SELECT MOD(-10,3);
 mod
-----
  -1
(1 row)

SELECT MOD(-10,-3);
 mod
-----
  -1
(1 row)

SELECT MOD(10,-3);
 mod
-----
   1
(1 row)

=> SELECT MOD(6.2,0);
ERROR 3117:  Division by zero

21 - PI

Returns the constant pi (P), the ratio of any circle's circumference to its diameter in Euclidean geometry The return type is DOUBLE PRECISION.

Behavior type

Syntax

PI()

Examples

SELECT PI();
        pi
------------------
 3.14159265358979
(1 row)

22 - POWER

Returns a DOUBLE PRECISION value representing one number raised to the power of another number.

Behavior type

Syntax

POW[ER] ( expression1, expression2 )

Parameters

expression1

DOUBLE PRECISION value that represents the base.

expression2

DOUBLE PRECISION value that represents the exponent.

Examples

SELECT POWER(9.0, 3.0);
 power
-------
   729
(1 row)

23 - RADIANS

Returns a DOUBLE PRECISION value representing an angle expressed in radians. You can express the input angle in DEGREES, and optionally include minutes and seconds.

Behavior type

Syntax

RADIANS (degrees [, minutes, seconds])

Parameters

degrees

A unit of angular measurement, representing 1/360 of a full rotation.

minutes

A unit of angular measurement, representing 1/60 of a degree.

seconds

A unit of angular measurement, representing 1/60 of a minute.

Examples

SELECT RADIANS(45);
      RADIANS
-------------------
 0.785398163397448
(1 row)

SELECT RADIANS (1,2,3);
      RADIANS
-------------------
 0.018049613347708
(1 row)

24 - RANDOM

Returns a uniformly-distributed random DOUBLE PRECISION value x, where 0 <= x < 1.

Typical pseudo-random generators accept a seed, which is set to generate a reproducible pseudo-random sequence. Vertica, however, distributes SQL processing over a cluster of nodes, where each node generates its own independent random sequence.

Results depending on RANDOM are not reproducible because the work might be divided differently across nodes. Therefore, Vertica automatically generates truly random seeds for each node each time a request is executed and does not provide a mechanism for forcing a specific seed.

Behavior type

Syntax

RANDOM()

Examples

In the following example, RANDOM returns a float ≥ 0 and < 1.0:

SELECT RANDOM();
      random
-------------------
 0.211625560652465
(1 row)

25 - RANDOMINT

Accepts and returns an INTEGER value. RANDOMINT(n) returns one of the n integers from 0 through n – 1.

Typical pseudo-random generators accept a seed, which is set to generate a reproducible pseudo-random sequence. Vertica, however, distributes SQL processing over a cluster of nodes, where each node generates its own independent random sequence.

Results depending on RANDOM are not reproducible because the work might be divided differently across nodes. Therefore, Vertica automatically generates truly random seeds for each node each time a request is executed and does not provide a mechanism for forcing a specific seed.

Behavior type

Syntax

RANDOMINT ( n )

Parameters

The value accepted is any positive integer (n) between the values 1 and 9,223,372,036,854,775,807.

For general information on integer data types, refer to the section, INTEGER.

Restrictions

If you provide a negative value, or if you exceed the maximum value, Vertica returns an error.

Examples

In the following example, the result is an INTEGER, which is >= 0 and < n, randomly chosen from the set {0,1,2,3,4}.

=> SELECT RANDOMINT(5);
RANDOMINT
----------
        3
(1 row)

26 - RANDOMINT_CRYPTO

Accepts and returns an INTEGER value from a set of values between 0 and the specified function argument -1. For this cryptographic random number generator, Vertica uses RAND_bytes to provide the random value.

Behavior type

Syntax

RANDOMINT_CRYPTO ( integer-expression )

Parameters

integer-expression

Resolves to a positive integer between 1 and 2⁶³ − 1, inclusive.

Examples

In the following example, RANDOMINT_CRYPTO returns an INTEGER >= 0 and less than the specified argument 5, randomly chosen from the set {0,1,2,3,4}.

=> SELECT RANDOMINT_crypto(5);
RANDOMINT_crypto
----------------
               3
(1 row)

27 - ROUND

Rounds a value to a specified number of decimal places, retaining the original precision and scale. Fractions greater than or equal to .5 are rounded up. Fractions less than .5 are rounded down (truncated).

Behavior type

Syntax

ROUND ( expression [ , places ] )

Parameters

expression

Is an expression of type NUMERIC or DOUBLE PRECISION (FLOAT).

places

An INTEGER value. When *places* is a positive integer, Vertica rounds the value to the right of the decimal point using the specified number of places. When *places* is a negative integer, Vertica rounds the value on the left side of the decimal point using the specified number of places.

Notes

Using ROUND with a NUMERIC datatype returns NUMERIC, retaining the original precision and scale.

=> SELECT ROUND(3.5);
 ROUND
-------
   4.0
(1 row)

Examples

=> SELECT ROUND(2.0, 1.0) FROM dual;
 ROUND
-------
   2.0
(1 row)
=> SELECT ROUND(12.345, 2.0);
 ROUND
--------
 12.350
(1 row)
=> SELECT ROUND(3.444444444444444);
       ROUND
-------------------
 3.000000000000000
(1 row)
=> SELECT ROUND(3.14159, 3);
  ROUND
---------
 3.14200
(1 row)
=> SELECT ROUND(1234567, -3);
  ROUND
---------
 1235000
(1 row)
=> SELECT ROUND(3.4999, -1);
 ROUND
--------
 0.0000
(1 row)

The following example creates a table with two columns, adds one row of values, and shows sample rounding to the left and right of a decimal point.

=> CREATE TABLE sampleround (roundcol1 NUMERIC, roundcol2 NUMERIC);
CREATE TABLE

=> INSERT INTO sampleround VALUES (1234567, .1234567);
 OUTPUT
--------
      1
(1 row)

=> SELECT ROUND(roundcol1,-3) AS pn3, ROUND(roundcol1,-4) AS pn4, ROUND(roundcol1,-5) AS pn5 FROM sampleround;

           pn3           |           pn4           |           pn5
-------------------------+-------------------------+-------------------------
 1235000.000000000000000 | 1230000.000000000000000 | 1200000.000000000000000
(1 row)

=> SELECT ROUND(roundcol2,3) AS p3, ROUND(roundcol2,4) AS p4, ROUND(roundcol2,5) AS p5 FROM sampleround;

        p3         |        p4         |        p5
-------------------+-------------------+-------------------
 0.123000000000000 | 0.123500000000000 | 0.123460000000000
(1 row)

28 - SIGN

Returns a DOUBLE PRECISION value of -1, 0, or 1 representing the arithmetic sign of the argument.

Behavior type

Syntax

SIGN ( expression )

Parameters

expression

Is an expression of type DOUBLE PRECISION

Examples

SELECT SIGN(-8.4);
 sign
------
   -1
(1 row)

29 - SIN

Returns a DOUBLE PRECISION value that represents the trigonometric sine of the passed parameter.

Behavior type

Syntax

SIN ( expression )

Parameters

expression

An expression of type DOUBLE PRECISION

Examples

SELECT SIN(30 * 2 * 3.14159 / 360);
        SIN
-------------------
 0.499999616987256
(1 row)

30 - SINH

Returns a DOUBLE PRECISION value that represents the hyperbolic sine of the passed parameter.

Behavior type

Syntax

SINH ( expression )

Parameters

expression

An expression of type DOUBLE PRECISION

Examples

=> SELECT SINH(30 * 2 * 3.14159 / 360);
        SINH
-------------------
 0.547852969600632

31 - SQRT

Returns a DOUBLE PRECISION value representing the arithmetic square root of the argument.

Behavior type

Syntax

SQRT ( expression )

Parameters

expression

Is an expression of type DOUBLE PRECISION

Examples

SELECT SQRT(2);
      sqrt
-----------------
 1.4142135623731
(1 row)

32 - TAN

Returns a DOUBLE PRECISION value that represents the trigonometric tangent of the passed parameter.

Behavior type

Syntax

TAN ( expression )

Parameters

expression

An expression of type DOUBLE PRECISION

Examples

=> SELECT TAN(30);
        TAN
-------------------
 -6.40533119664628
(1 row)

33 - TANH

Returns a DOUBLE PRECISION value that represents the hyperbolic tangent of the passed parameter.

Behavior type

Syntax

TANH ( expression )

Parameters

expression

An expression of type DOUBLE PRECISION

Examples

=> SELECT TANH(-1);
        TANH
-------------------
  -0.761594155955765

34 - TRUNC

Returns the expression value fully truncated (toward zero). Supplying a places argument truncates the expression to the number of decimal places you indicate.

Behavior type

Syntax

TRUNC ( expression [ , places ] )

Parameters

expression

Is an expression of type NUMERIC or DOUBLE PRECISION (FLOAT).

places

An INTEGER value. When places is a positive integer, Vertica truncates the value to the right of the decimal point. When places is a negative integer, Vertica truncates the value on the left side of the decimal point.

Notes

Using TRUNC with a NUMERIC datatype returns NUMERIC, retaining the original precision and scale.

=> SELECT TRUNC(3.5);
 TRUNC
-------
   3.0
(1 row)

Examples

=> SELECT TRUNC(42.8);
 TRUNC
-------
  42.0
(1 row)
=> SELECT TRUNC(42.4382, 2);
  TRUNC
---------
 42.4300
(1 row)

The following example creates a table with two columns, adds one row of values, and shows sample truncating to the left and right of a decimal point.

=> CREATE TABLE sampletrunc (truncol1 NUMERIC, truncol2 NUMERIC);
CREATE TABLE

=> INSERT INTO sampletrunc VALUES (1234567, .1234567);
 OUTPUT
--------
      1
(1 row)

=> SELECT TRUNC(truncol1,-3) AS p3, TRUNC(truncol1,-4) AS p4, TRUNC(truncol1,-5) AS p5 FROM sampletrunc;

           p3            |           p4            |           p5
-------------------------+-------------------------+-------------------------
 1234000.000000000000000 | 1230000.000000000000000 | 1200000.000000000000000
(1 row)

=> SELECT TRUNC(truncol2,3) AS p3, TRUNC(truncol2,4) AS p4, TRUNC(truncol2,5) AS p5 FROM sampletrunc;

        p3         |        p4         |        p5
-------------------+-------------------+-------------------
 0.123000000000000 | 0.123400000000000 | 0.123450000000000
(1 row)

35 - WIDTH_BUCKET

Constructs equiwidth histograms, in which the histogram range is divided into intervals (buckets) of identical sizes. In addition, values below the low bucket return 0, and values above the high bucket return bucket_count +1. Returns an integer value.

Behavior type

Syntax

WIDTH_BUCKET ( expression, hist_min, hist_max, bucket_count )

Parameters

expression

The expression for which the histogram is created. This expression must evaluate to a numeric or datetime value or to a value that can be implicitly converted to a numeric or datetime value. If expression evaluates to null, then the expression returns null.

hist_min

An expression that resolves to the low boundary of bucket 1. Must also evaluate to numeric or datetime values and cannot evaluate to null.

hist_max

An expression that resolves to the high boundary of bucket bucket_count. Must also evaluate to a numeric or datetime value and cannot evaluate to null.

bucket_count

An expression that resolves to a constant, indicating the number of buckets. This expression always evaluates to a positive INTEGER.

Notes

• WIDTH_BUCKET divides a data set into buckets of equal width. For example, Age = 0–20, 20–40, 40–60, 60–80. This is known as an equiwidth histogram.

• When using WIDTH_BUCKET pay attention to the minimum and maximum boundary values. Each bucket contains values equal to or greater than the base value of that bucket, so that age ranges of 0–20, 20–40, and so on, are actually 0–19.99 and 20–39.999.

• WIDTH_BUCKET accepts the following data types: (FLOAT and/or INTEGER), (TIMESTAMP and/or DATE and/or TIMESTAMPTZ), or (INTERVAL and/or TIME).

Examples

The following example returns five possible values and has three buckets: 0 [Up to 100), 1 [100–300), 2 [300–500), 3 [500–700), and 4 [700 and up):

SELECT product_description, product_cost, WIDTH_BUCKET(product_cost, 100, 700, 3);

The following example creates a nine-bucket histogram on the annual_income column for customers in Connecticut who are female doctors. The results return the bucket number to an “Income” column, divided into eleven buckets, including an underflow and an overflow. Note that if customers had an annual incomes greater than the maximum value, they would be assigned to an overflow bucket, 10:

SELECT customer_name, annual_income, WIDTH_BUCKET (annual_income, 100000, 1000000, 9) AS "Income"
FROM public.customer_dimension WHERE customer_state='CT'
AND title='Dr.' AND customer_gender='Female' AND household_id < '1000'
ORDER BY "Income";

In the following result set, the reason there is a bucket 0 is because buckets are numbered from 1 to bucket_count. Anything less than the given value of hist_min goes in bucket 0, and anything greater than the given value of hist_max goes in the bucket bucket_count+1. In this example, bucket 9 is empty, and there is no overflow. The value 12,283 is less than 100,000, so it goes into the underflow bucket.

   customer_name    | annual_income | Income
--------------------+---------------+--------
 Joanna A. Nguyen   |         12283 |      0
 Amy I. Nguyen      |        109806 |      1
 Juanita L. Taylor  |        219002 |      2
 Carla E. Brown     |        240872 |      2
 Kim U. Overstreet  |        284011 |      2
 Tiffany N. Reyes   |        323213 |      3
 Rebecca V. Martin  |        324493 |      3
 Betty . Roy        |        476055 |      4
 Midori B. Young    |        462587 |      4
 Martha T. Brown    |        687810 |      6
 Julie D. Miller    |        616509 |      6
 Julie Y. Nielson   |        894910 |      8
 Sarah B. Weaver    |        896260 |      8
 Jessica C. Nielson |        861066 |      8
(14 rows)

See also

• NTILE [analytic]