Loading...

Functions#

Constants#

E

This class is utilized to represent SQL strings, with vDataColumn, for instance, inheriting from this class.

INF

This class is utilized to represent SQL strings, with vDataColumn, for instance, inheriting from this class.

NAN

This class is utilized to represent SQL strings, with vDataColumn, for instance, inheriting from this class.

PI

This class is utilized to represent SQL strings, with vDataColumn, for instance, inheriting from this class.

TAU

This class is utilized to represent SQL strings, with vDataColumn, for instance, inheriting from this class.

Analytics#

avg(expr)

Computes the average (arithmetic mean) of an expression over a group of rows.

bool_and(expr)

Processes Boolean values and returns a Boolean value result.

bool_or(expr)

Processes Boolean values and returns a Boolean value result.

bool_xor(expr)

Processes Boolean values and returns a Boolean value result.

conditional_change_event(expr)

Assigns an event window number to each row, starting from 0, and increments by 1 when the result of evaluating the argument expression on the current row differs from that on the previous row.

conditional_true_event(expr)

Assigns an event window number to each row, starting from 0, and increments the number by 1 when the result of the boolean argument expression evaluates true.

count(expr)

Returns as a BIGINT the number of rows in each group where the expression is not NULL.

lag(expr[, offset])

Returns the value of the input expression at the given offset before the current row within a window.

lead(expr[, offset])

Returns values from the row after the current row within a window, letting you access more than one row in a table at the same time.

max(expr)

Returns the greatest value of an expression over a group of rows.

median(expr)

Computes the approximate median of an expression over a group of rows.

min(expr)

Returns the smallest value of an expression over a group of rows.

nth_value(expr, row_number)

Returns the value evaluated at the row that is the nth row of the window (counting from 1).

quantile(expr, number)

Computes the approximate percentile of an expression over a group of rows.

rank()

Within each window partition, ranks all rows in the query results set according to the order specified by the window's ORDER BY clause.

row_number()

Assigns a sequence of unique numbers, starting from 1, to each row in a window partition.

std(expr)

Evaluates the statistical sample standard deviation for each member of the group.

sum(expr)

Computes the sum of an expression over a group of rows.

var(expr)

Evaluates the sample variance for each row of the group.

Conditional#

case_when(*args)

Returns the conditional statement of the input arguments.

decode(expr, *args)

Compares the expressions to each search value.

Date#

date(expr)

Converts the input value to a DATE data type.

day(expr)

Returns the day of the month as an integer.

dayofweek(expr)

Returns the day of the week as an integer, where Sunday is day 1.

dayofyear(expr)

Returns the day of the year as an integer, where January 1 is day 1.

extract(expr, field)

Extracts a sub-field, such as year or hour, from a date/time expression.

getdate()

Returns the current statement's start date and time as a TIMESTAMP value.

getutcdate()

Returns the current statement's start date and time at TIME ZONE 'UTC' as a TIMESTAMP value.

hour(expr)

Returns the hour portion of the specified date as an integer, where 0 is 00:00 to 00:59.

interval(expr)

Converts the input value to a INTERVAL data type.

microsecond(expr)

Returns the microsecond portion of the specified date as an integer.

minute(expr)

Returns the minute portion of the specified date as an integer.

month(expr)

Returns the month portion of the specified date as an integer.

overlaps(start0, end0, start1, end1)

Evaluates two time periods and returns true when they overlap, false otherwise.

quarter(expr)

Returns calendar quarter of the specified date as an integer, where the January-March quarter is 1.

round_date(expr[, precision])

Rounds the specified date or time.

second(expr)

Returns the seconds portion of the specified date as an integer.

timestamp(expr)

Converts the input value to a TIMESTAMP data type.

week(expr)

Returns the week of the year for the specified date as an integer, where the first week begins on the first Sunday on or preceding January 1.

year(expr)

Returns an integer that represents the year portion of the specified date.

Math#

apply(func, *args, **kwargs)

Applies any Vertica function on the input expressions.

abs(expr)

Absolute Value.

acos(expr)

Trigonometric Inverse Cosine.

asin(expr)

Trigonometric Inverse Sine.

atan(expr)

Trigonometric Inverse Tangent.

atan2(quotient, divisor)

Trigonometric Inverse Tangent of the arithmetic dividend of the arguments.

cbrt(expr)

Cube Root.

ceil(expr)

Ceiling Function.

comb(n, k)

Number of ways to choose k items from n items.

cos(expr)

Trigonometric Cosine.

cosh(expr)

Hyperbolic Cosine.

cot(expr)

Trigonometric Cotangent.

degrees(expr)

Converts Radians to Degrees.

distance(lat0, lon0, lat1, lon1[, radius])

Returns the distance (in kilometers) between two points.

exp(expr)

Exponential.

factorial(expr)

Factorial.

floor(expr)

Floor Function.

gamma(expr)

Gamma Function.

hash(*args)

Calculates a hash value over the function arguments.

isfinite(expr)

Returns True if the expression is finite.

isinf(expr)

Returns True if the expression is infinite.

isnan(expr)

Returns True if the expression is NaN.

lgamma(expr)

Natural Logarithm of the expression Gamma.

ln(expr)

Natural Logarithm.

log(expr[, base])

Logarithm.

radians(expr)

Converts Degrees to Radians.

round(expr[, places])

Rounds the expression.

sign(expr)

Sign of the expression.

sin(expr)

Trigonometric Sine.

sinh(expr)

Hyperbolic Sine.

sqrt(expr)

Arithmetic Square Root.

tan(expr)

Trigonometric Tangent.

tanh(expr)

Hyperbolic Tangent.

trunc(expr[, places])

Truncates the expression.

Null Handling#

coalesce(expr, *args)

Returns the value of the first non-null expression in the list.

nullifzero(expr)

Evaluates to NULL if the value in the expression is 0.

zeroifnull(expr)

Evaluates to 0 if the expression is NULL.

Random#

random()

Returns a Random Number.

randomint(n)

Returns a Random Number from 0 through n - 1.

seeded_random(random_state)

Returns a Seeded Random Number using the input random state.

Regular Expression#

regexp_count(expr, pattern[, position])

Returns the number of times a regular expression matches a string.

regexp_ilike(expr, pattern)

Returns true if the string contains a match for the regular expression.

regexp_instr(expr, pattern[, position, ...])

Returns the starting or ending position in a string where a regular expression matches.

regexp_like(expr, pattern)

Returns true if the string matches the regular expression.

regexp_replace(expr, target, replacement[, ...])

Replace all occurrences of a substring that match a regular expression with another substring.

regexp_substr(expr, pattern[, position, ...])

Returns the substring that matches a regular expression within a string.

String#

length(expr)

Returns the length of a string.

lower(expr)

Returns a VARCHAR value containing the argument converted to lowercase letters.

substr(expr, position[, extent])

Returns VARCHAR or VARBINARY value representing a substring of a specified string.

upper(expr)

Returns a VARCHAR value containing the argument converted to uppercase letters.

edit_distance(expr1, expr2)

Calculates and returns the Levenshtein distance between two strings.

soundex(expr)

Returns Soundex encoding of a varchar strings as a four character string.

soundex_matches(expr1, expr2)

Generates and compares Soundex encodings of two strings, and returns a count of the matching characters (ranging from 0 for no match to 4 for an exact match).

jaro_distance(expr1, expr2)

Calculates and returns the Jaro distance between two strings.

jaro_winkler_distance(expr1, expr2)

Calculates and returns the Jaro-Winkler distance between two strings.