Functions

SQL functions for data manipulation, analytics, and transformations.

---

Constants

E	This class is utilized to represent SQL strings, with VastColumn, for instance, inheriting from this class.
INF	This class is utilized to represent SQL strings, with VastColumn, for instance, inheriting from this class.
NAN	This class is utilized to represent SQL strings, with VastColumn, for instance, inheriting from this class.
PI	This class is utilized to represent SQL strings, with VastColumn, for instance, inheriting from this class.
TAU	This class is utilized to represent SQL strings, with VastColumn, 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.
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 VAST 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)	Returns the distance (in kilometers) between two points in Earth.
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_extract(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).
hamming_distance(expr1, expr2)	Calculates and returns the Hamming distance between two strings.