List of Modules and Functions in delika SQL

Preview: This feature in preview state and might change or have limited support.

Notation

global indicates that the function is a global function, which can be called without module name.
... in argument list indicates there can be zero or more arguments.
[, arg] in argument list indicates the argument arg can be omitted.

AGGREGATE Module

Function in AGGREGATE module (aggregate function) are used with GROUP BY clause or as a window function. Aggregate function can be used as window function with or without OVER clause.

`AGGREGATE.AVG(x)`

global

Retuns the average (arithmetic mean) of input values.

x: values to calculate the average

`AGGREGATE.COUNT(x)`

global

Returns the number of non-null input values.

x: values to count

`AGGREGATE.MAX(x)`

global

Returns the maximum value of input values.

x: values to calculate the maximum

`AGGREGATE.MIN(x)`

global

Returns the minimum value of input values.

x: values to calculate the minimum

`AGGREGATE.SUM(x)`

global

Returns the sum of input values.

x: values to calculate the sum

CONDITION Module

CONDITION module handles conditionnal expressions including NULL.

`CONDITION.COALESCE(x, y, ...)`

global

Returns the first non-null value in the argument list.

x, y, ...: values

`CONDITION.NULLIF(x, y)`

global

Returns NULL if the first argument equals the second argument, otherwise returns the first argument.

x, y: values

DATETIME Module

DATETIME module handles datetime opeartions.

`DATETIME.DATETIME(year, month, day[, hour, minute, second[, millisecond]])`

Constructs datetime values.

year: year part
month: month part (1-12)
day: day part (1-31)
hour: hour part (0-23)
minute: minute part (0-59)
second: second part (0-59)
millisecond: millisecond part (0-999)

`DATETIME.DIFF_DAY(x, y)`

Returns y - x in days.

x, y: datetime values

`DATETIME.DIFF_HOUR(x, y)`

Returns y - x in hours.

x, y: datetime values

`DATETIME.DIFF_MINUTE(x, y)`

Returns y - x in minutes.

x, y: datetime values

`DATETIME.DIFF_MONTH(x, y)`

Returns y - x in months.

x, y: datetime values

`DATETIME.DIFF_SECOND(x, y)`

Returns y - x in seconds.

x, y: datetime values

`DATETIME.DIFF_YEAR(x, y)`

Returns y - x in years.

x, y: datetime values

`DATETIME.EXTRACT_DAY(x)`

Extracts the day part of x.

x: datetime values

`DATETIME.EXTRACT_HOUR(x)`

Extracts the hour part of x.

x: datetime values

`DATETIME.EXTRACT_MINUTE(x)`

Extracts the minute part of x.

x: datetime values

`DATETIME.EXTRACT_MONTH(x)`

Extracts the month part of x.

x: datetime values

`DATETIME.EXTRACT_SECOND(x)`

Extracts the second part of x.

x: datetime values

`DATETIME.EXTRACT_YEAR(x)`

Extracts the year part of x.

x: datetime values

`DATETIME.NOW()`

Returns the current timestamp.

`DATETIME.TRUNCATE_DAY(x)`

Returns datetime truncated to the day part.

x: datetime values

`DATETIME.TRUNCATE_HOUR(x)`

Returns datetime truncated to the hour part.

x: datetime values

`DATETIME.TRUNCATE_MINUTE(x)`

Returns datetime truncated to the minute part.

x: datetime values

`DATETIME.TRUNCATE_MONTH(x)`

Returns datetime truncated to the month part.

x: datetime values

`DATETIME.TRUNCATE_SECOND(x)`

Returns datetime truncated to the second part.

x: datetime values

`DATETIME.TRUNCATE_YEAR(x)`

Returns datetime truncated to the year part.

x: datetime values

MATH Module

MATH module contains mathematical functions.

`MATH.ABS(x)`

global

Returns the absolute value.

x: values

`MATH.ACOS(x)`

global

Returns the arc cosine value.

x: values

`MATH.ASIN(x)`

global

Returns the arc sine value.

x: values

`MATH.ATAN(x)`

global

Returns the arc tangent value.

x: values

`MATH.ATAN2(x, y)`

global

Returns the arc tangent value of y / x.

x: denominators
y: numerators

`MATH.CEIL(x)`

global

Returns the rounded up to the nearest integer.

x: values

`MATH.COS(x)`

global

Returns the cosine value.

x: values

`MATH.EXP(x)`

global

Returns the exponential function value where the base is Euler's number (e = 2.71828...).

x: values

`MATH.FLOOR(x)`

global

Returns the rounded down to the nearest integer.

x: values

`MATH.LN(x)`

global

Returns the natural logarithm.

x: values

`MATH.LOG(base, x)`

global

Returns the logarithm.

base: bases
x: values

`MATH.MOD(x, y)`

global

Returns the modulus of dividing x by y.

x: dividends
y: divisors

`MATH.PI()`

Returns $ \pi $ (= 3.14159...).

`MATH.POW(x, y)`

global

Returns x raised to the power of y.

x: bases
y: exponents

`MATH.RAND()`

global

Returns a pseudo-random value in the range [0, 1).

`MATH.SIGN(x)`

global

Returns the signum (1 for positive, -1 for negative, and 0 for zero).

x: values

`MATH.SIN(x)`

global

Returns the sine value.

x: values

`MATH.SQRT(x)`

global

Returns the square root.

x: values

`MATH.TAN(x)`

global

Returns the tangent value.

x: values

POSITION Module

POSITION module contains window functions used to specify rank of rows.

`POSITION.DENSE_RANK()`

global

Returns the rank of each value in a group.

`POSITION.LAG(x[, offset])`

global

Return the value at offset rows before the current row.

x: values
offset: offset to shift (default to 1)

`POSITION.LEAD(x[, offset])`

global

Return the value at offset rows after the current row.

x: values
offset: offset to shift (default to 1)

`POSITION.NTILE(n)`

global

Partitions the rows into at most n buckets.

n: the number of buckets

`POSITION.RANK()`

global

Returns the rank of each row in a group.

`POSITION.ROW_NUMBER()`

global

Returns the sequential number for each row in a group.

STRING module

STRING module contains functions to manipulate string values.

`STRING.CONCAT(x, y, ...)`

global

Returns the concatenation of string values.

x, y, ...: values

`STRING.LENGTH(x)`

global

Returns the character length of string values.

x: values

`STRING.LOWER(x)`

global

Converts string values into lower case.

x: values

`STRING.LPAD(x, size, padding)`

Adjusts string values in length using padding chacacters from the left.

x: values
size: preferred character length of the result strings
padding: characters to pad

`STRING.NFC(x)`

Normalizes string values in form C (NFC).

x: values

`STRING.NFD(x)`

Normalizes string values in form D (NFD).

x: values

`STRING.NFKC(x)`

Normalizes string values in form KC (NFKC).

x: values

`STRING.NFKD(x)`

Normalizes string values in form KD (NFKD).

x: values

`STRING.RPAD(x, size, padding)`

Adjusts string values in length using padding chacacters from the right.

x: values
size: preferred character length of the result strings
padding: characters to pad

`STRING.UPPER(x)`

global

Converts string values into upper case.

x: values