ARRAY_SORTBY
Function
Sort the values array according to the order of a keys array.
- For example, if
keysis[3, 0, 2]andvaluesis[5, 7, 8], after sorting thekeysbecome[0, 2, 3]and the correspondingvaluesbecome[7, 8, 5].
Syntax
ARRAY_SORTBY(values, keys)ARRAY_SORTBY(lambda, values)ARRAY_SORTBY(lambda, values)is equivalent toARRAY_SORTBY(values, ARRAY_MAP(lambda, values))
Parameters
values:ARRAY<T>, the value array to be sorted.Tsupports numeric, boolean, string, datetime, IP, etc.keys:ARRAY<T>, a key array of the same length asvalues.Tsupports numeric, boolean, string, datetime, IP, etc.lambda: alambdaexpression applied tovaluesto produce thekeysarray used for sorting.
Return value
- Returns
ARRAY<T>of the same type asvalues. - An error is thrown when, for any row, the element counts of
valuesandkeysare different.
Usage notes
- Stability:
valuesare reordered by ascendingkeys. The relative order among equal keys is undefined. - In higher-order calls,
keysare computed first byARRAY_MAP, thenvaluesare sorted bykeys.
Examples
-
Basic: sort
valuesby the ascending order ofkeys.ARRAY_SORTBY([10,20,30], [3,1,2])->[20,30,10]ARRAY_SORTBY(['a','b','c'], [2,2,1])->['c','a','b]
-
Higher-order: compute
keysvialambda, then sort.ARRAY_SORTBY(x -> x + 1, [3,1,2])->[1,2,3](withkeys[4,2,3])ARRAY_SORTBY(x -> x*2 <= 2, [1,2,3])->[1,2,3](withkeys[true,false,false])
-
When
keysorvaluesisNULL, returnvaluesunchanged.array_sortby([10,20,30], NULL)->[10, 20, 30]array_sortby(NULL, [2,3])->NULL