Grouped Aggregations (“group by”)#

Grouped aggregations are not directly invokable, but are used as part of aSQL-style “group by” operation. Like scalar aggregations, grouped aggregationsreduce multiple input values to a single output value. Instead of aggregatingall values of the input, however, grouped aggregations partition the inputvalues on some set of “key” columns, then aggregate each group individually,emitting one output value per input group.

As an example, for the following table:

we can compute a sum of the columnx, grouped on the columnkey.This gives us three groups, with the following results. Note that null istreated as a distinct key value.

The supported aggregation functions are as follows. All function names areprefixed withhash_, which differentiates them from their scalarequivalents above and reflects how they are implemented internally.

• (1) If null values are taken into account, by setting theScalarAggregateOptions::skip_nulls to false, thenKleene logiclogic is applied. The min_count option is not respected.

• (2) CountMode controls whether only non-null values are counted(the default), only null values are counted, or all values arecounted. For hash_distinct, it instead controls whether null valuesare emitted. This never affects the grouping keys, only group values(i.e. you may get a group where the key is null).

• (3)hash_distinct andhash_list gather the grouped valuesinto a list array.

• (4) For decimal inputs, the resulting decimal will have the sameprecision and scale. The result is rounded away from zero.

• (5) Output is a{"min":inputtype,"max":inputtype} Struct array.

  Of the interval types, only the month interval is supported, as the day-timeand month-day-nano types are not sortable.

• (6)hash_one returns one arbitrary value from the input for eachgroup. The function is biased towards non-null values: if there is at leastone non-null value for a certain group, that value is returned, and only ifall the values arenull for the group will the function returnnull.

• (7) The first input contains the pivot key, while the second input containsthe values to be pivoted. The output is a Struct with one field for each keyinPivotOptions::key_names.

• (8) Output is Int64, UInt64, Float64, or Decimal128/256, depending on theinput type.

• (9) Decimal arguments are cast to Float64 first.

• (10) T-digest computes approximate quantiles, and so only needs afixed amount of memory. See thereference implementation for details.

• (11) Result is based on ordering of the input data.

Arithmetic functions#

These functions expect inputs of numeric type and apply a given arithmeticoperation to each element(s) gathered from the input(s). If any of theinput element(s) is null, the corresponding output element is null.For binary functions, input(s) will be cast to thecommon numeric type(and dictionary decoded, if applicable) before the operation is applied.

The default variant of these functions does not detect overflow (the resultthen typically wraps around). Most functions are also available in anoverflow-checking variant, suffixed_checked, which returnsanInvalidStatus when overflow is detected.

For functions which support decimal inputs (currentlyadd,subtract,multiply, anddivide and their checked variants), decimals of differentprecisions/scales will be promoted appropriately. Mixed decimal andfloating-point arguments will cast all arguments to floating-point, while mixeddecimal and integer arguments will cast all arguments to decimals.Mixed time resolution temporal inputs will be cast to finest input resolution.

• (1) Precision and scale of computed DECIMAL results

  Operation	Result precision and scale
  add subtract	scale = max(s1, s2) precision = max(p1-s1, p2-s2) + 1 + scale
  multiply	scale = s1 + s2 precision = p1 + p2 + 1
  divide	scale = max(4, s1 + p2 - s2 + 1) precision = p1 - s1 + s2 + scale

  It’s compatible with Redshift’s decimal promotion rules. All decimal digitsare preserved foradd,subtract andmultiply operations. The resultprecision ofdivide is at least the sum of precisions of both operands withenough scale kept. Error is returned if the result precision is beyond thedecimal value range.

• (2) Output is any of (-1,1) for nonzero inputs and 0 for zero input. NaNvalues return NaN. Integral and decimal values return signedness as Int8 andfloating-point values return it with the same type as the input values.

Bit-wise functions#

• (1) An error is emitted if the shift amount (i.e. the second input) isout of bounds for the data type. However, an overflow when shifting thefirst input is not error (truncated bits are silently discarded).

Rounding functions#

Rounding functions displace numeric inputs to an approximate value with a simplerrepresentation based on the rounding criterion.

• (1) By default rounding functions change a value to the nearestinteger using HALF_TO_EVEN to resolve ties. Options are available to controlthe rounding criterion. Allround functions have theround_mode option to set the rounding mode.

• (2) Round to a number of digits where thendigits option ofRoundOptions specifies the rounding precision in terms of numberof digits. A negative value corresponds to digits in the non-fractionalpart. For example, -2 corresponds to rounding to the nearest multiple of100 (zeroing the ones and tens digits). Default value ofndigits is 0which rounds to the nearest integer. For integer inputs a non-negativendigits value is ignored and the input is returned unchanged. For integerinputs, if-ndigits is larger than the maximum number of digits theinput type can hold, an error is returned.

• (3) Round to a multiple where themultiple option ofRoundToMultipleOptions specifies the rounding scale. The roundingmultiple has to be a positive value and can be casted to input type.For example, 100 corresponds to rounding to the nearest multiple of 100(zeroing the ones and tens digits). Default value ofmultiple is 1 whichrounds to the nearest integer.

• (4) Round the first input to multiple of the second input. The roundingmultiple has to be a positive value and can be casted to the first input type.For example, 100 corresponds to rounding to the nearest multiple of 100(zeroing the ones and tens digits).

Forround functions, the following rounding modes are available.Tie-breaking modes are prefixed with HALF and round non-ties to the nearest integer.The example values are given for default values ofndigits andmultiple.

The following table gives examples of howndigits (for theroundandround_binary functions) andmultiple (forround_to_multiple)influence the operation performed, respectively.

Logarithmic functions#

Logarithmic functions are also supported, and also offer_checkedvariants that check for domain errors if needed.

Decimal values are accepted, but are cast to Float64 first.

Trigonometric functions#

Trigonometric functions are also supported, and also offer_checkedvariants that check for domain errors if needed.

Decimal values are accepted, but are cast to Float64 first.

Hyperbolic trigonometric functions#

Hyperbolic trigonometric functions are also supported, and, where applicable, also offer_checked variants that check for domain errors if needed.

Decimal values are accepted, but are cast to Float64 first.

Comparisons#

These functions expect two inputs of numeric type (in which case they will becast to thecommon numeric type before comparison),or two inputs of Binary- or String-like types, or two inputs of Temporal types.If any input is dictionary encoded it will be expanded for the purposes ofcomparison. If any of the input elements in a pair is null, the correspondingoutput element is null. Decimal arguments will be promoted in the same way asforadd andsubtract.

These functions take any number of inputs of numeric type (in which case theywill be cast to thecommon numeric type beforecomparison) or of temporal types. If any input is dictionary encoded it will beexpanded for the purposes of comparison.

• (1) By default, nulls are skipped (but the kernel can be configured to propagate nulls).For floating point values, NaN will be taken over null but not over any other value.For binary- and string-like values, only identical type parameters are supported.

Logical functions#

The normal behaviour for these functions is to emit a null if any of theinputs is null (similar to the semantics ofNaN in floating-pointcomputations).

Some of them are also available in aKleene logic variant (suffixed_kleene) where null is taken to mean “undefined”. This is theinterpretation of null used in SQL systems as well as R and Julia,for example.

For the Kleene logic variants, therefore:

• “true AND null”, “null AND true” give “null” (the result is undefined)

• “true OR null”, “null OR true” give “true”

• “false AND null”, “null AND false” give “false”

• “false OR null”, “null OR false” give “null” (the result is undefined)

String predicates#

These functions classify the input string elements according to their charactercontents. An empty string element emits false in the output. For ASCIIvariants of the functions (prefixedascii_), a string element with non-ASCIIcharacters emits false in the output.

The first set of functions operates on a character-per-character basis,and emit true in the output if the input contains only characters of agiven class:

• (1) Also matches all numeric ASCII characters and all ASCII digits.

• (2) Non-cased characters, such as punctuation, do not match.

• (3) This is currently the same asutf8_is_decimal.

• (4) Unlikeutf8_is_decimal, non-decimal numeric characters also match.

The second set of functions also consider the character order in a stringelement:

• (1) Output is true iff the input string element is title-cased, i.e. anyword starts with an uppercase character, followed by lowercase characters.Word boundaries are defined by non-cased characters.

The third set of functions examines string elements on a byte-per-byte basis:

• (1) Output is true iff the input string element contains only ASCII characters,i.e. only bytes in [0, 127].

String transforms#

• (1) Each ASCII character in the input is converted to lowercase oruppercase. Non-ASCII characters are left untouched.

• (2) ASCII input is reversed to the output. If non-ASCII charactersare present,InvalidStatus will be returned.

• (3) Output is the physical length in bytes of each input element. Outputtype is Int32 for Binary/String, Int64 for LargeBinary/LargeString.

• (4) Repeat the input binary string a given number of times.

• (5) Replace the slice of the substring fromReplaceSliceOptions::start(inclusive) toReplaceSliceOptions::stop (exclusive) byReplaceSubstringOptions::replacement. The binary kernel measures theslice in bytes, while the UTF8 kernel measures the slice in codeunits.

• (6) Perform a byte-level reverse.

• (7) Replace non-overlapping substrings that match toReplaceSubstringOptions::pattern byReplaceSubstringOptions::replacement. IfReplaceSubstringOptions::max_replacements != -1, it determines themaximum number of replacements made, counting from the left.

• (8) Replace non-overlapping substrings that match to the regular expressionReplaceSubstringOptions::pattern byReplaceSubstringOptions::replacement, using the Google RE2 library. IfReplaceSubstringOptions::max_replacements != -1, it determines themaximum number of replacements made, counting from the left. Note that if thepattern contains groups, backreferencing can be used.

• (9) Each UTF8-encoded character in the input is converted to lowercase oruppercase.

• (10) Output is the number of characters (not bytes) of each input element.Output type is Int32 for String, Int64 for LargeString.

• (11) Each UTF8-encoded code unit is written in reverse order to the output.If the input is not valid UTF8, then the output is undefined (but the size of outputbuffers will be preserved).

String padding#

These functions append/prepend a given padding byte (ASCII) or codepoint (UTF8) inorder to center (center), right-align (lpad), or left-align (rpad) a string.

String trimming#

These functions trim off characters on both sides (trim), or the left (ltrim) or right side (rtrim).

• (1) Only characters specified inTrimOptions::characters will betrimmed off. Both the input string and thecharacters argument areinterpreted as ASCII characters.

• (2) Only trim off ASCII whitespace characters ('\t','\n','\v','\f','\r' and'').

• (3) Only characters specified inTrimOptions::characters will betrimmed off.

• (4) Only trim off Unicode whitespace characters.

String splitting#

These functions split strings into lists of strings. All kernels can optionallybe configured with amax_splits and areverse parameter, wheremax_splits==-1 means no limit (the default). Whenreverse is true,the splitting is done starting from the end of the string; this is only relevantwhen a positivemax_splits is given.

• (1) A non-zero length sequence of ASCII defined whitespace bytes('\t','\n','\v','\f','\r' and'') is seenas separator.

• (2) The string is split when an exact pattern is found (the pattern itselfis not included in the output).

• (3) The string is split when a regex match is found (the matchedsubstring itself is not included in the output).

• (4) A non-zero length sequence of Unicode defined whitespace codepointsis seen as separator.

String component extraction#

• (1) Extract substrings defined by a regular expression using the Google RE2library. The output struct field names refer to the named capture groups,e.g. ‘letter’ and ‘digit’ for the regular expression(?P<letter>[ab])(?P<digit>\\d).

• (2) Extract the offset and length of substrings defined by a regular expressionusing the Google RE2 library. The output struct field names refer to the namedcapture groups, e.g. ‘letter’ and ‘digit’ for the regular expression(?P<letter>[ab])(?P<digit>\\d). Each output struct field is a fixed size listof two integers: the index to the start of the captured group and the lengthof the captured group, respectively.

String joining#

These functions do the inverse of string splitting.

• (1) The first input must be an array, while the second can be a scalar or array.Each list of values in the first input is joined using each second inputas separator. If any input list is null or contains a null, the correspondingoutput will be null.

• (2) All arguments are concatenated element-wise, with the last argument treatedas the separator (scalars are recycled in either case). Null separators emitnull. If any other argument is null, by default the corresponding output will benull, but it can instead either be skipped or replaced with a given string.

String Slicing#

This function transforms each sequence of the array to a subsequence, accordingto start and stop indices, and a non-zero step (defaulting to 1). Slicingsemantics follow Python slicing semantics: the start index is inclusive,the stop index exclusive; if the step is negative, the sequence is followedin reverse order.

• (1) Slice string into a substring defined by (start,stop,step)as given bySliceOptions wherestart andstop are measuredin bytes. Null inputs emit null.

• (2) Slice string into a substring defined by (start,stop,step)as given bySliceOptions wherestart andstop are measuredin codeunits. Null inputs emit null.

Containment tests#

• (1) Output is the number of occurrences ofMatchSubstringOptions::pattern in the corresponding inputstring. Output type is Int32 for Binary/String, Int64for LargeBinary/LargeString.

• (2) Output is true iffMatchSubstringOptions::patternis a suffix/prefix of the corresponding input.

• (3) Output is the index of the first occurrence ofMatchSubstringOptions::pattern in the corresponding inputstring, otherwise -1. Output type is Int32 for Binary/String, Int64for LargeBinary/LargeString.

• (4) Output is the index of the corresponding input element inSetLookupOptions::value_set, if found there. Otherwise,output is null.

• (5) Output is true iff the corresponding input element is equal to oneof the elements inSetLookupOptions::value_set.

• (6) Output is true iff the SQL-style LIKE patternMatchSubstringOptions::pattern fully matches thecorresponding input element. That is,% will match any number ofcharacters,_ will match exactly one character, and any othercharacter matches itself. To match a literal percent sign orunderscore, precede the character with a backslash.

• (7) Output is true iffMatchSubstringOptions::patternis a substring of the corresponding input element.

• (8) Output is true iffMatchSubstringOptions::patternmatches the corresponding input element at any position.

Categorizations#

• (1) Output is true iff the corresponding input element is finite (neither Infinity,-Infinity, nor NaN). Hence, for Decimal and integer inputs this always returns true.

• (2) Output is true iff the corresponding input element is Infinity/-Infinity.Hence, for Decimal and integer inputs this always returns false.

• (3) Output is true iff the corresponding input element is NaN.Hence, for Decimal and integer inputs this always returns false.

• (4) Output is true iff the corresponding input element is null. NaN valuescan also be considered null by settingNullOptions::nan_is_null.

• (5) Output is true iff the corresponding input element is non-null, else false.

• (6) Output is true iff the corresponding input element is non-null, else null.

  Mostly intended for expression simplification/guarantees.

Selecting / multiplexing#

For each “row” of input values, these functions emit one of the input values,depending on a condition.

• (1) This function acts like a SQL “case when” statement or switch-case. Theinput is a “condition” value, which is a struct of Booleans, followed by thevalues for each “branch”. There must be either exactly one value argument foreach child of the condition struct, or one more value argument than children(in which case we have an “else” or “default” value). The output is of thesame type as the value inputs; each row will be the corresponding value fromthe first value datum for which the corresponding Boolean is true, or thecorresponding value from the “default” input, or null otherwise.

  Note that currently, while all types are supported, dictionaries will beunpacked.

• (2) The first input must be an integral type. The rest of the arguments can beany type, but must all be the same type or promotable to a common type. Eachvalue of the first input (the ‘index’) is used as a zero-based index into theremaining arguments (i.e. index 0 is the second argument, index 1 is the thirdargument, etc.), and the value of the output for that row will be thecorresponding value of the selected input at that row. If the index is null,then the output will also be null.

• (3) Each row of the output will be the corresponding value of the firstinput which is non-null for that row, otherwise null.

• (4) First input must be a Boolean scalar or array. Second and third inputscould be scalars or arrays and must be of the same type. Output is an array(or scalar if all inputs are scalar) of the same type as the second/ thirdinput. If the nulls present on the first input, they will be promoted to theoutput, otherwise nulls will be chosen based on the first input values.

  Also see:replace_with_mask.

Structural transforms#

• (1) Each output element is the length of the corresponding input element(null if input is null). Output type is Int32 for List, ListView, andFixedSizeList, Int64 for LargeList and LargeListView.

• (2) The output struct’s field types are the types of its arguments. Thefield names are specified using an instance ofMakeStructOptions.The output shape will be scalar if all inputs are scalar, otherwise anyscalars will be broadcast to arrays.

Conversions#

A general conversion function namedcast is provided which accepts a largenumber of input and output types. The type to cast to can be passed in aCastOptions instance. As an alternative, the same service isprovided by a concrete functionCast().

The conversions available withcast are listed below. In all cases, anull input value is converted into a null output value.

• (1) Output precision of%S (seconds) flag depends on the input timestampprecision. Timestamps with second precision are represented as integers whilemilliseconds, microsecond and nanoseconds are represented as fixed floatingpoint numbers with 3, 6 and 9 decimal places respectively. To obtain integerseconds, cast to timestamp with second resolution.The character for the decimal point is localized according to the locale.Seedetailed formatting documentation for descriptions of other flags.

Truth value extraction

• (1) Output is true iff the corresponding input value has non-zero length.

• (2) Output is true iff the corresponding input value is non-zero.

Same-kind conversion

• (1) No-operation cast: the raw values are kept identical, onlythe type is changed.

• (2) Validates the contents ifCastOptions::allow_invalid_utf8is false.

• (3) No-operation cast: only the type is changed.

• (4) Overflow and truncation checks are enabled depending onthe givenCastOptions.

• (5) Not all such casts have been implemented.

String representations

Generic conversions

• (1) The dictionary indices are unchanged, the dictionary values arecast from the input value type to the output value type (if a conversionis available).

• (2) Fields are casted primarily by matching field names between the inputand output type. For duplicate field names, their relative order is preserved,with each input field used for one or zero output fields. This allows castingto a subset or re-ordered field names. If a nullable field in the output typehas no matching field name in the input type, it will be filled with nulls.Casting a field from nullable to not null is supported if the input datacontains zero nulls.

• (3) The list offsets are unchanged, the list values are cast from theinput value type to the output value type (if a conversion isavailable). If the output type is (Large)ListView, then sizes arederived from the offsets.

• (4) If output type is list-like, offsets (consequently, the values array)might have to be rebuilt to be sorted and spaced adequately. If output type isa list-view type, the offsets and sizes are unchanged. In any case, the listvalues are cast from the input value type to the output value type (if aconversion is available).

• (5) Offsets are unchanged, the keys and values are cast from respective inputto output types (if a conversion is available). If output type is a list ofstruct, the key field is output as the first field and the value field thesecond field, regardless of field names chosen.

• (6) Any input type that can be cast to the resulting extension’s storage type.This excludes extension types, unless being cast to the same extension type.

Temporal component extraction#

These functions extract datetime components (year, month, day, etc) from temporal types.For timestamps inputs with non-empty timezone, localized timestamp components will be returned.

• (1) Outputs the number of the day of the week. By default week begins on Mondayrepresented by 0 and ends on Sunday represented by 6. Day numbering can start with 0 or 1 based onDayOfWeekOptions::count_from_zero parameter.DayOfWeekOptions::week_start can beused to set the starting day of the week using ISO convention (Monday=1, Sunday=7).DayOfWeekOptions::week_start parameter is not affected byDayOfWeekOptions::count_from_zero.

• (2) First ISO week has the majority (4 or more) of it’s days in January. ISO yearstarts with the first ISO week. ISO week starts on Monday.SeeISO 8601 week date definition for more details.

• (3) Output is a{"iso_year":outputtype,"iso_week":outputtype,"iso_day_of_week": outputtype} Struct.

• (4) First US week has the majority (4 or more) of its days in January. US yearstarts with the first US week. US week starts on Sunday.

• (5) Returns week number allowing for setting several parameters.IfWeekOptions::week_starts_monday is true, the week starts with Monday, else Sunday if false.IfWeekOptions::count_from_zero is true, dates from the current year that fall into the last ISO weekof the previous year are numbered as week 0, else week 52 or 53 if false.IfWeekOptions::first_week_is_fully_in_year is true, the first week (week 1) must fully be in January;else if false, a week that begins on December 29, 30, or 31 is considered the first week of the new year.

• (6) Output is a{"year":int64(),"month":int64(),"day":int64()} Struct.

Temporal difference#

These functions compute the difference between two timestamps in thespecified unit. The difference is determined by the number ofboundaries crossed, not the span of time. For example, the differencein days between 23:59:59 on one day and 00:00:01 on the next day isone day (since midnight was crossed), not zero days (even though lessthan 24 hours elapsed). Additionally, if the timestamp has a definedtimezone, the difference is calculated in the local timezone. Forinstance, the difference in years between “2019-12-31 18:00:00-0500”and “2019-12-31 23:00:00-0500” is zero years, because the local yearis the same, even though the UTC years would be different.

Timezone handling#

assume_timezone function is meant to be used when an external system produces“timezone-naive” timestamps which need to be converted to “timezone-aware”timestamps (see for example thedefinitionin the Python documentation).

Input timestamps are assumed to be relative to the timezone given inAssumeTimezoneOptions::timezone. They are converted toUTC-relative timestamps with the timezone metadata set to the above value.An error is returned if the timestamps already have the timezone metadata set.

local_timestamp function converts UTC-relative timestamps to local “timezone-naive”timestamps. The timezone is taken from the timezone metadata of the inputtimestamps. This function is the inverse ofassume_timezone. Please note:all temporal functions already operate on timestamps as if they were in localtime of the metadata provided timezone. Usinglocal_timestamp is only meant to beused when an external system expects local timestamps.

• (1) In addition to the timezone value,AssumeTimezoneOptionsallows choosing the behaviour when a timestamp is ambiguous or nonexistentin the given timezone (because of DST shifts).

Random number generation#

This function generates an array of uniformly-distributed double-precision numbersin range [0, 1). The options provide the length of the output and the algorithm forgenerating the random numbers, using either a seed or a system-provided, platform-specificrandom generator.

Cumulative Functions#

Cumulative functions are vector functions that perform a running accumulation ontheir input using a given binary associative operation with an identity element(a monoid) and output an array containing the corresponding intermediate runningvalues. The input is expected to be of numeric type. By default these functionsdo not detect overflow. They are also available in an overflow-checking variant,suffixed_checked, which returns anInvalidStatus whenoverflow is detected.

• (1) CumulativeOptions has two optional parameters. The first parameterCumulativeOptions::start is a starting value for the runningaccumulation. It has a default value of 0 forsum, 1 forprod, min ofinput type formax, and max of input type formin. Specified values ofstart must be castable to the input type. The second parameterCumulativeOptions::skip_nulls is a boolean. When set tofalse (the default), the first encountered null is propagated. When set totrue, each null in the input produces a corresponding null in the output anddoesn’t affect the accumulation forward.

• (2)CumulativeOptions::start is ignored.

Statistical functions#

• (1) Clamp values in the lower and upper quantiles to reduce the statisticalinfluence of outliers. The quantiles can be configured inWinsorizeOptions.

Associative transforms#

• (1) Output isDictionary(Int32,inputtype). It is a no-op if input isalready a Dictionary array.

• (2) Duplicates are removed from the output while the original order ismaintained.

• (3) Output is a{"values":inputtype,"counts":Int64} Struct.Each output element corresponds to a unique value in the input, alongwith the number of times this value has appeared.

Selections#

These functions select and return a subset of their input.

• (1) Each element in the input is appended to the output iff it is non-null.If the input is a record batch or table, any null value in a column dropsthe entire row.

• (2) Each element in input 1 (the values) is appended to the output iffthe corresponding element in input 2 (the filter) is true. Hownulls in the filter are handled can be configured using FilterOptions.

• (3) For each elementi in input 2 (the indices), thei’th elementin input 1 (the values) is appended to the output.

• (4) The output type is specified inInversePermutationOptions.

• (5) Forindices[i] = x,inverse_permutation[x] = i. Andinverse_permutation[x]= null ifx does not appear in the input indices. Indices must be in the rangeof[0, max_index], or null, which will be ignored. If multiple indices point to thesame value, the last one is used.

• (6) Forindices[i] = x,output[x] = values[i]. Andoutput[x] = nullifx does not appear in the input indices. Indices must be in the rangeof[0, max_index], or null, in which case the corresponding value will beignored. If multiple indices point to the same value, the last one is used.

Containment tests#

This function returns the indices at which array elements are non-null and non-zero.

Sorts and partitions#

By default, in these functions, nulls are considered greater than any other value(they will be sorted or partitioned at the end of the array). Floating-pointNaN values are considered greater than any other non-null value, but smallerthan nulls. This behaviour can be changed using thenull_placement settingin the respective option classes.

• (1) The output is an array of indices into the input, that define astable sort of the input.

• (2) The input must be an array. The default order is ascending.

• (3) The output is an array of indices into the input array, that definea partial non-stable sort such that theN’th index points to theN’thelement in sorted order, and all indices before theN’th point toelements less or equal to elements at or after theN’th (similar tostd::nth_element()).N is given inPartitionNthOptions::pivot.

• (4) The output is a one-based numerical array of ranks.

• (5) The output ofrank_quantile is an array of quantiles strictly between0 and 1. The ouput ofrank_normal is an array of finite real valuescorresponding to points in the normal distribution that reflect the input’squantile ranks.

• (6) The input can be an array, chunked array, record batch ortable. If the input is a record batch or table, one or more sortkeys must be specified.

• (7) The output is an array of indices into the input, that define anon-stable sort of the input.

Structural transforms#

• (1) Output is an array of the same length as the input list array. Theoutput values are the values at the specified index of each child list.

• (2) The top level of nesting is removed: all values in the list child array,including nulls, are appended to the output. However, nulls in the parentlist array are discarded.

• (3) For each value in the list child array, the index at which it is foundin the list-like array is appended to the output. Indices of null lists in theparent array might still be present in the output if they are non-empty nulllists. If the parent is a list-view, child array values that are not used byany non-null list-view are null in the output.

• (4) For each list element, compute the slice of that list element, thenreturn another list-like array of those slices. Can return either afixed or variable size list-like array, as determined by options provided.

• (5) Extract either theFIRST,LAST orALL items from amap whose key match the given query key passed via options.The output type is an Array of items for theFIRST/LAST optionsand an Array of List of items for theALL option.

• (6) Extract a child value based on a sequence of indices passed inthe options. The validity bitmap of the result will be theintersection of all intermediate validity bitmaps. For example, foran array with typestruct<a:int32,b:struct<c:int64,d:float64>>:

  • An empty sequence of indices yields the original value unchanged.

  • The index0 yields an array of typeint32 whose validitybitmap is the intersection of the bitmap for the outermost structand the bitmap for the childa.

  • The index1,1 yields an array of typefloat64 whosevalidity bitmap is the intersection of the bitmaps for theoutermost struct, for structb, and for the childd.

  For unions, a validity bitmap is synthesized based on the typecodes. Also, the index is always the child index and not a type code.Hence for array with typesparse_union<2:int32,7:utf8>:

  • The index0 yields an array of typeint32, which is validat an indexn if and only if the child arraya is valid atindexn and the type code at indexn is 2.

  • The indices2 and7 are invalid.

Replace functions#

These functions create a copy of the first input with some elementsreplaced, based on the remaining inputs.

• (1) Valid values are carried forward/backward to fill null values.

• (2) Each element in input 1 for which the corresponding Boolean in input 2is true is replaced with the next value from input 3. A null in input 2results in a corresponding null in the output.

  Also see:if_else.

Pairwise functions#

Pairwise functions are unary vector functions that perform a binary operation ona pair of elements in the input array, typically on adjacent elements. The n-thoutput is computed by applying the binary operation to the n-th and (n-p)-th inputs,where p is the period. The default period is 1, in which case the binaryoperation is applied to adjacent pairs of inputs. The period can also benegative, in which case the n-th output is computed by applying the binaryoperation to the n-th and (n+abs(p))-th inputs.

• (1) Computes the first order difference of an array, It internally callsthe scalar functionSubtract (or the checked variant) to computedifferences, so its behavior and supported types are the same asSubtract. The period can be specified inPairwiseOptions.

• (2) Wraps around the result when overflow is detected.

• (3) Returns anInvalidStatus when overflow is detected.