ISNULL(arg1)

Returns a Boolean value indicating whether a specified expression contains no valid data (null).

IF(cond, value when true, value when false)

Evaluates a condition. Argument2 and 3 have to have the same data type.

Sample SQL: case country when 'Italy' then 'OK' else 'NAH' end

IFNULL(arg1,arg2)

== IF(ISNULL(arg1), arg2, arg1)

== COALESCE(arg1,arg2)

NULLIF(arg1,arg2)

Returns the first expression if the two expressions are not equal.

TOSTRING(arg1)

==  CAST(arg AS VARCHAR)

TODATE(arg1)

==  CAST(arg AS DATE)

PARSE(arg1,arg2)

Argument1 is literal, argument2 is literal string, e.g. "STRING".

For argument2, allowed values are: STRING, NUMBER, DATE, BOOLEAN, DATETIME, LOB, LONG

SQL0 - SQL5(arg1-arg6)

An SQL query with formula: SQL<n>("SQL expression with <n> inputs represented by %s placeholders", field1, ..., field n)
Example: SQL1("date_part('month',%s)",InvoiceDate)

CEILING(arg1)

The nearest integer greater than or equal to the argument.

FLOOR(arg1)

The nearest integer less than or equal to the argument.

ROUND(arg1,arg2)

Rounds to the specified number of decimals: round(42.4382, 2) = 42.44

SUM(arg1)

Sum of the expression across all input values.

AVG(arg1)

Average of the expression across all input values.

MIN(arg1)

Minimum value of the expression across all input values.

MAX(arg1)

Maximum value of the expression across all input values.

COUNT(arg1)

Number of input rows for which the value of the expression is not null.

COUNT_DISTINCT(arg1)

Number of all distinct non-null values specified by the expression.

EVERY(arg1)

ANY(arg1)

PERCENTILE_DISC(arg1,arg2)

precentile_disc(expr, fraction): Discrete percentile: The first input value whose position in the ordering equals or exceeds the specified fraction

PERCENTILE_CONT(arg1,arg2)

precentile_cont(expr, fraction): Continuous percentile: The first input value whose position in the ordering equals or exceeds the specified fraction. Interpolating between adjacent input items if needed.

corr(Y,X)

Correlation coefficient

covar_pop(Y,X)

Population covariance

covar_samp(Y,X)

Sample covariance

regr_avgx(Y,X)

Average of the independent variable (sum(X)/N)

regr_avgy(Y,X)

Average of the dependent variable (sum(Y)/N)

regr_count(Y, X)

Number of input rows in which both expressions are non-null

regr_intercept(Y,X)

Y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs

regr_r2(Y,X)

Square of the correlation coefficient

regr_slope(Y,X)

Slope of the least-squares-fit linear equation determined by the (X, Y) pairs

regr_sxx(Y,X)

Sum(X^2) - sum(X)^2/N ("sum of squares" of the independent variable)

regr_sxy(Y,X)

Sum(X*Y) - sum(X) * sum(Y)/N ("sum of products" of independent times dependent variable)

regr_syy(Y,X)

Sum(Y^2) - sum(Y)^2/N ("sum of squares" of the dependent variable)

stddev_pop(expr)

Population standard deviation of the input values

stddev_samp(expr)

Sample standard deviation of the input values

var_pop(expr)

Population variance of the input values (square of the population standard deviation)

var_samp(expr)

Sample variance of the input values (square of the population standard deviation)

PERCENT_RANK(arg1)

Relative rank of the current row: (rank - 1) / (total rows - 1)

CUME_DIST(arg1)

Relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)

NTILE(arg1,arg2)

ntile(expr, num_buckets): Integer ranging from 1 to max num_buckets, dividing the partition as equally as possible

BIN_IDX(arg1,arg2)

bin_idx(expr, num_buckets): Index of bin from 1 to max num_buckets, with the equiwidth bins ranging from min(expr) to max(expr)

BIN_ABS(arg1,arg2)

bin_abs(expr, num_buckets): Mid-point of bin, dividing the min(expr) - max(expr) range into equal width num_buckets

BIN_PCT(arg1,arg2)

bin_pct(expr, num_buckets): Mid-point of bin as percentage, dividing the min(expr) - max(expr) range into equal width num_buckets