functions_arithmetic.yaml¶

This document file is generated for functions_arithmetic.yaml. The extension URN is extension:io.substrait:functions_arithmetic.

Scalar Functions¶

add¶

Add two values.

Implementations:

• add(x: i8, y: i8, option:overflow): -> i8
• add(x: i16, y: i16, option:overflow): -> i16
• add(x: i32, y: i32, option:overflow): -> i32
• add(i64, i64, option:overflow): -> i64
• add(x: fp32, y: fp32, option:rounding): -> fp32
• add(x: fp64, y: fp64, option:rounding): -> fp64

subtract¶

Subtract one value from another.

Implementations:

• subtract(x: i8, y: i8, option:overflow): -> i8
• subtract(x: i16, y: i16, option:overflow): -> i16
• subtract(x: i32, y: i32, option:overflow): -> i32
• subtract(x: i64, y: i64, option:overflow): -> i64
• subtract(x: fp32, y: fp32, option:rounding): -> fp32
• subtract(x: fp64, y: fp64, option:rounding): -> fp64

multiply¶

Multiply two values.

Implementations:

• multiply(x: i8, y: i8, option:overflow): -> i8
• multiply(x: i16, y: i16, option:overflow): -> i16
• multiply(x: i32, y: i32, option:overflow): -> i32
• multiply(x: i64, y: i64, option:overflow): -> i64
• multiply(x: fp32, y: fp32, option:rounding): -> fp32
• multiply(x: fp64, y: fp64, option:rounding): -> fp64

divide¶

Divide x by y. In the case of integer division, partial values are truncated (i.e. rounded towards 0). The on_division_by_zero option governs behavior in cases where y is 0. If the option is IEEE then the IEEE754 standard is followed: all values except ±infinity return NaN and ±infinity are unchanged. If the option is LIMIT then the result is ±infinity in all cases. If either x or y are NaN then behavior will be governed by on_domain_error. If x and y are both ±infinity, behavior will be governed by on_domain_error.

Implementations:

• divide(x: i8, y: i8, option:overflow, option:on_domain_error, option:on_division_by_zero): -> i8
• divide(x: i16, y: i16, option:overflow, option:on_domain_error, option:on_division_by_zero): -> i16
• divide(x: i32, y: i32, option:overflow, option:on_domain_error, option:on_division_by_zero): -> i32
• divide(x: i64, y: i64, option:overflow, option:on_domain_error, option:on_division_by_zero): -> i64
• divide(x: fp32, y: fp32, option:rounding, option:on_domain_error, option:on_division_by_zero): -> fp32
• divide(x: fp64, y: fp64, option:rounding, option:on_domain_error, option:on_division_by_zero): -> fp64

negate¶

Negation of the value

Implementations:

• negate(x: i8, option:overflow): -> i8
• negate(x: i16, option:overflow): -> i16
• negate(x: i32, option:overflow): -> i32
• negate(x: i64, option:overflow): -> i64
• negate(x: fp32): -> fp32
• negate(x: fp64): -> fp64

modulus¶

Calculate the remainder ® when dividing dividend (x) by divisor (y). In mathematics, many conventions for the modulus (mod) operation exists. The result of a mod operation depends on the software implementation and underlying hardware. Substrait is a format for describing compute operations on structured data and designed for interoperability. Therefore the user is responsible for determining a definition of division as defined by the quotient (q). The following basic conditions of division are satisfied: (1) q ∈ ℤ (the quotient is an integer) (2) x = y * q + r (division rule) (3) abs® < abs(y) where q is the quotient. The division_type option determines the mathematical definition of quotient to use in the above definition of division. When division_type=TRUNCATE, q = trunc(x/y). When division_type=FLOOR, q = floor(x/y). In the cases of TRUNCATE and FLOOR division: remainder r = x - round_func(x/y) The on_domain_error option governs behavior in cases where y is 0, y is ±inf, or x is ±inf. In these cases the mod is undefined. The overflow option governs behavior when integer overflow occurs. If x and y are both 0 or both ±infinity, behavior will be governed by on_domain_error.

Implementations:

• modulus(x: i8, y: i8, option:division_type, option:overflow, option:on_domain_error): -> i8
• modulus(x: i16, y: i16, option:division_type, option:overflow, option:on_domain_error): -> i16
• modulus(x: i32, y: i32, option:division_type, option:overflow, option:on_domain_error): -> i32
• modulus(x: i64, y: i64, option:division_type, option:overflow, option:on_domain_error): -> i64

power¶

Take the power with x as the base and y as exponent.

Implementations:

• power(x: i64, y: i64, option:overflow): -> i64
• power(x: fp32, y: fp32): -> fp32
• power(x: fp64, y: fp64): -> fp64

sqrt¶

Square root of the value

Implementations:

• sqrt(x: i64, option:rounding, option:on_domain_error): -> fp64
• sqrt(x: fp32, option:rounding, option:on_domain_error): -> fp32
• sqrt(x: fp64, option:rounding, option:on_domain_error): -> fp64

exp¶

The mathematical constant e, raised to the power of the value.

Implementations:

• exp(x: i64, option:rounding): -> fp64
• exp(x: fp32, option:rounding): -> fp32
• exp(x: fp64, option:rounding): -> fp64

cos¶

Get the cosine of a value in radians.

Implementations:

• cos(x: fp32, option:rounding): -> fp32
• cos(x: fp64, option:rounding): -> fp64

sin¶

Get the sine of a value in radians.

Implementations:

• sin(x: fp32, option:rounding): -> fp32
• sin(x: fp64, option:rounding): -> fp64

tan¶

Get the tangent of a value in radians.

Implementations:

• tan(x: fp32, option:rounding): -> fp32
• tan(x: fp64, option:rounding): -> fp64

cosh¶

Get the hyperbolic cosine of a value in radians.

Implementations:

• cosh(x: fp32, option:rounding): -> fp32
• cosh(x: fp64, option:rounding): -> fp64

sinh¶

Get the hyperbolic sine of a value in radians.

Implementations:

• sinh(x: fp32, option:rounding): -> fp32
• sinh(x: fp64, option:rounding): -> fp64

tanh¶

Get the hyperbolic tangent of a value in radians.

Implementations:

• tanh(x: fp32, option:rounding): -> fp32
• tanh(x: fp64, option:rounding): -> fp64

acos¶

Get the arccosine of a value in radians.

Implementations:

• acos(x: fp32, option:rounding, option:on_domain_error): -> fp32
• acos(x: fp64, option:rounding, option:on_domain_error): -> fp64

asin¶

Get the arcsine of a value in radians.

Implementations:

• asin(x: fp32, option:rounding, option:on_domain_error): -> fp32
• asin(x: fp64, option:rounding, option:on_domain_error): -> fp64

atan¶

Get the arctangent of a value in radians.

Implementations:

• atan(x: fp32, option:rounding): -> fp32
• atan(x: fp64, option:rounding): -> fp64

acosh¶

Get the hyperbolic arccosine of a value in radians.

Implementations:

• acosh(x: fp32, option:rounding, option:on_domain_error): -> fp32
• acosh(x: fp64, option:rounding, option:on_domain_error): -> fp64

asinh¶

Get the hyperbolic arcsine of a value in radians.

Implementations:

• asinh(x: fp32, option:rounding): -> fp32
• asinh(x: fp64, option:rounding): -> fp64

atanh¶

Get the hyperbolic arctangent of a value in radians.

Implementations:

• atanh(x: fp32, option:rounding, option:on_domain_error): -> fp32
• atanh(x: fp64, option:rounding, option:on_domain_error): -> fp64

atan2¶

Get the arctangent of values given as x/y pairs.

Implementations:

• atan2(x: fp32, y: fp32, option:rounding, option:on_domain_error): -> fp32
• atan2(x: fp64, y: fp64, option:rounding, option:on_domain_error): -> fp64

radians¶

Converts angle x in degrees to radians.

Implementations:

• radians(x: fp32, option:rounding): -> fp32
• radians(x: fp64, option:rounding): -> fp64

degrees¶

Converts angle x in radians to degrees.

Implementations:

• degrees(x: fp32, option:rounding): -> fp32
• degrees(x: fp64, option:rounding): -> fp64

abs¶

Calculate the absolute value of the argument. Integer values allow the specification of overflow behavior to handle the unevenness of the twos complement, e.g. Int8 range [-128 : 127].

Implementations:

• abs(x: i8, option:overflow): -> i8
• abs(x: i16, option:overflow): -> i16
• abs(x: i32, option:overflow): -> i32
• abs(x: i64, option:overflow): -> i64
• abs(x: fp32): -> fp32
• abs(x: fp64): -> fp64

sign¶

Return the signedness of the argument. Integer values return signedness with the same type as the input. Possible return values are [-1, 0, 1] Floating point values return signedness with the same type as the input. Possible return values are [-1.0, -0.0, 0.0, 1.0, NaN]

Implementations:

• sign(x: i8): -> i8
• sign(x: i16): -> i16
• sign(x: i32): -> i32
• sign(x: i64): -> i64
• sign(x: fp32): -> fp32
• sign(x: fp64): -> fp64

factorial¶

Return the factorial of a given integer input. The factorial of 0! is 1 by convention. Negative inputs will raise an error.

Implementations:

• factorial(n: i32, option:overflow): -> i32
• factorial(n: i64, option:overflow): -> i64

bitwise_not¶

Return the bitwise NOT result for one integer input.

Implementations:

• bitwise_not(x: i8): -> i8
• bitwise_not(x: i16): -> i16
• bitwise_not(x: i32): -> i32
• bitwise_not(x: i64): -> i64

bitwise_and¶

Return the bitwise AND result for two integer inputs.

Implementations:

• bitwise_and(x: i8, y: i8): -> i8
• bitwise_and(x: i16, y: i16): -> i16
• bitwise_and(x: i32, y: i32): -> i32
• bitwise_and(x: i64, y: i64): -> i64

bitwise_or¶

Return the bitwise OR result for two given integer inputs.

Implementations:

• bitwise_or(x: i8, y: i8): -> i8
• bitwise_or(x: i16, y: i16): -> i16
• bitwise_or(x: i32, y: i32): -> i32
• bitwise_or(x: i64, y: i64): -> i64

bitwise_xor¶

Return the bitwise XOR result for two integer inputs.

Implementations:

• bitwise_xor(x: i8, y: i8): -> i8
• bitwise_xor(x: i16, y: i16): -> i16
• bitwise_xor(x: i32, y: i32): -> i32
• bitwise_xor(x: i64, y: i64): -> i64

shift_left¶

Bitwise shift left. The vacant (least-significant) bits are filled with zeros. Params: base – the base number to shift. shift – number of bits to left shift.

Implementations:

• shift_left(base: i32, shift: i32): -> i32
• shift_left(base: i64, shift: i32): -> i64

shift_right¶

Bitwise (signed) shift right. The vacant (most-significant) bits are filled with zeros if the base number is positive or with ones if the base number is negative, thus preserving the sign of the resulting number. Params: base – the base number to shift. shift – number of bits to right shift.

Implementations:

• shift_right(base: i32, shift: i32): -> i32
• shift_right(base: i64, shift: i32): -> i64

shift_right_unsigned¶

Bitwise unsigned shift right. The vacant (most-significant) bits are filled with zeros. Params: base – the base number to shift. shift – number of bits to right shift.

Implementations:

• shift_right_unsigned(base: i32, shift: i32): -> i32
• shift_right_unsigned(base: i64, shift: i32): -> i64

Aggregate Functions¶

sum¶

Sum a set of values. The sum of zero elements yields null.

Implementations:

• sum(x: i8, option:overflow): -> i64?
• sum(x: i16, option:overflow): -> i64?
• sum(x: i32, option:overflow): -> i64?
• sum(x: i64, option:overflow): -> i64?
• sum(x: fp32, option:overflow): -> fp64?
• sum(x: fp64, option:overflow): -> fp64?

sum0¶

Sum a set of values. The sum of zero elements yields zero. Null values are ignored.

Implementations:

• sum0(x: i8, option:overflow): -> i64
• sum0(x: i16, option:overflow): -> i64
• sum0(x: i32, option:overflow): -> i64
• sum0(x: i64, option:overflow): -> i64
• sum0(x: fp32, option:overflow): -> fp64
• sum0(x: fp64, option:overflow): -> fp64

avg¶

Average a set of values. For integral types, this truncates partial values.

Implementations:

• avg(x: i8, option:overflow): -> i8?
• avg(x: i16, option:overflow): -> i16?
• avg(x: i32, option:overflow): -> i32?
• avg(x: i64, option:overflow): -> i64?
• avg(x: fp32, option:overflow): -> fp32?
• avg(x: fp64, option:overflow): -> fp64?

min¶

Min a set of values.

Implementations:

• min(x: i8): -> i8?
• min(x: i16): -> i16?
• min(x: i32): -> i32?
• min(x: i64): -> i64?
• min(x: fp32): -> fp32?
• min(x: fp64): -> fp64?

max¶

Max a set of values.

Implementations:

• max(x: i8): -> i8?
• max(x: i16): -> i16?
• max(x: i32): -> i32?
• max(x: i64): -> i64?
• max(x: fp32): -> fp32?
• max(x: fp64): -> fp64?

product¶

Product of a set of values. Returns 1 for empty input.

Implementations:

• product(x: i8, option:overflow): -> i8
• product(x: i16, option:overflow): -> i16
• product(x: i32, option:overflow): -> i32
• product(x: i64, option:overflow): -> i64
• product(x: fp32, option:rounding): -> fp32
• product(x: fp64, option:rounding): -> fp64

std_dev¶

Calculates standard-deviation for a set of values.

Implementations:

• std_dev(x: fp32, option:rounding, option:distribution): -> fp32?
• std_dev(distribution, x: fp32, option:rounding): -> fp32?
• std_dev(x: fp64, option:distribution, option:rounding): -> fp64?
• std_dev(distribution, x: fp64, option:rounding): -> fp64?

variance¶

Calculates variance for a set of values.

Implementations:

• variance(x: fp32, option:distribution, option:rounding): -> fp32?
• variance(distribution, x: fp32, option:rounding): -> fp32?
• variance(x: fp64, option:distribution, option:rounding): -> fp64?
• variance(distribution, x: fp64, option:rounding): -> fp64?

corr¶

Calculates the value of Pearson’s correlation coefficient between x and y. If there is no input, null is returned.

Implementations:

• corr(x: fp32, y: fp32, option:rounding): -> fp32?
• corr(x: fp64, y: fp64, option:rounding): -> fp64?

mode¶

Calculates mode for a set of values. If there is no input, null is returned.

Implementations:

• mode(x: i8): -> i8?
• mode(x: i16): -> i16?
• mode(x: i32): -> i32?
• mode(x: i64): -> i64?
• mode(x: fp32): -> fp32?
• mode(x: fp64): -> fp64?

median¶

Calculate the median for a set of values. Returns null if applied to zero records. For the integer implementations, the rounding option determines how the median should be rounded if it ends up midway between two values. For the floating point implementations, they specify the usual floating point rounding mode.

Implementations:

• median(precision, x: i8, option:rounding): -> i8?
• median(precision, x: i16, option:rounding): -> i16?
• median(precision, x: i32, option:rounding): -> i32?
• median(precision, x: i64, option:rounding): -> i64?
• median(precision, x: fp32, option:rounding): -> fp32?
• median(precision, x: fp64, option:rounding): -> fp64?

quantile¶

*Calculates quantiles for a set of values. This function will divide the aggregated values (passed via the distribution argument) over N equally-sized bins, where N is passed via a constant argument. It will then return the values at the boundaries of these bins in list form. If the input is appropriately sorted, this computes the quantiles of the distribution. The function can optionally return the first and/or last element of the input, as specified by the boundaries argument. If the input is appropriately sorted, this will thus be the minimum and/or maximum values of the distribution. When the boundaries do not lie exactly on elements of the incoming distribution, the function will interpolate between the two nearby elements. If the interpolated value cannot be represented exactly, the rounding option controls how the value should be selected or computed. The function fails and returns null in the following cases: - n is null or less than one; - any value in distribution is null.

The function returns an empty list if n equals 1 and boundaries is set to NEITHER.*

Implementations:

• quantile(boundaries, precision, n: i64, distribution: any, option:rounding): -> LIST?<any>

Window Functions¶

row_number¶

the number of the current row within its partition, starting at 1

Implementations:

• row_number(): -> i64?

rank¶

the rank of the current row, with gaps.

Implementations:

• rank(): -> i64?

dense_rank¶

the rank of the current row, without gaps.

Implementations:

• dense_rank(): -> i64?

percent_rank¶

the relative rank of the current row.

Implementations:

• percent_rank(): -> fp64?

cume_dist¶

the cumulative distribution.

Implementations:

• cume_dist(): -> fp64?

ntile¶

Return an integer ranging from 1 to the argument value,dividing the partition as equally as possible.

Implementations:

• ntile(x: i32): -> i32?
• ntile(x: i64): -> i64?

first_value¶

Returns the first value in the window.

Implementations:

• first_value(expression: any1): -> any1

last_value¶

Returns the last value in the window.

Implementations:

• last_value(expression: any1): -> any1

nth_value¶

Returns a value from the nth row based on the window_offset. window_offset should be a positive integer. If the value of the window_offset is outside the range of the window, null is returned. The on_domain_error option governs behavior in cases where window_offset is not a positive integer or null.

Implementations:

• nth_value(expression: any1, window_offset: i32, option:on_domain_error): -> any1?

lead¶

Return a value from a following row based on a specified physical offset. This allows you to compare a value in the current row against a following row. The expression is evaluated against a row that comes after the current row based on the row_offset. The row_offset should be a positive integer and is set to 1 if not specified explicitly. If the row_offset is negative, the expression will be evaluated against a row coming before the current row, similar to the lag function. A row_offset of null will return null. The function returns the default input value if row_offset goes beyond the scope of the window. If a default value is not specified, it is set to null. Example comparing the sales of the current year to the following year. row_offset of 1. | year | sales | next_year_sales | | 2019 | 20.50 | 30.00 | | 2020 | 30.00 | 45.99 | | 2021 | 45.99 | null |

Implementations:

• lead(expression: any1): -> any1?
• lead(expression: any1, row_offset: i32): -> any1?
• lead(expression: any1, row_offset: i32, default: any1): -> any1?

lag¶

Return a column value from a previous row based on a specified physical offset. This allows you to compare a value in the current row against a previous row. The expression is evaluated against a row that comes before the current row based on the row_offset. The expression can be a column, expression or subquery that evaluates to a single value. The row_offset should be a positive integer and is set to 1 if not specified explicitly. If the row_offset is negative, the expression will be evaluated against a row coming after the current row, similar to the lead function. A row_offset of null will return null. The function returns the default input value if row_offset goes beyond the scope of the partition. If a default value is not specified, it is set to null. Example comparing the sales of the current year to the previous year. row_offset of 1. | year | sales | previous_year_sales | | 2019 | 20.50 | null | | 2020 | 30.00 | 20.50 | | 2021 | 45.99 | 30.00 |

Implementations:

• lag(expression: any1): -> any1?
• lag(expression: any1, row_offset: i32): -> any1?
• lag(expression: any1, row_offset: i32, default: any1): -> any1?