Feature: support Array functions

[sundy-li]

Summary

Description for this feature.

common functions for array @TCeason

• array_concat, eg: array_concat([2, 3], [4, 5, 6]) #9803
• length
• array_contains(arr, elem) alias array_has
• array_indexof(arr, item) #9839
• array_slice(array, offset[, length])
• array_sort(array) #9940
• array_reverse_sort(array) // array_sort now contains this feature.

aggr combinators @b41sh

• array_unique(array) Counts the unique elements of a list.
• array_distinct(array) Removes all duplicates and NULLs from a list.
• array_aggr(array, 'agg'), aka array_<aggr>(array), eg: array_aggr([1,2,3], 'sum'), array_aggr([1,2,3], 'avg'), array_sum([1,2,3]
• array_prepend(element, array)
• array_append(array, element)
• array_any_value(array)

low-priority tasks:

• array_transform(array, lambda) (low-priority), eg: array_transform([1,2,3], x -> x + 1)
• array_filter(array, lambda) (low-priority), eg: array_filter([1,2,3], x -> x > 1)
• python style array comprehension (low-priority), eg: select [x+3 for x in [1,2,3]]
• array_join(array)
• ...

[BohuTANG]

Comments by @sundy-li :

1. https://duckdb.org/docs/sql/functions/nested
2. After the new expression is done.(Middle of November)

[BohuTANG]

@sundy-li

There are two array-related aggregate functions are required from the user:

• array_reduce https://clickhouse.com/docs/en/sql-reference/functions/array-functions/#arrayreduce
• group_array_moving_avg https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/grouparraymovingavg/

ClickHouse SQL like:

select UserID,  c, arrayReduce('count',RequestNum1) from (  
  select UserID, count(RequestNum) as c, groupArrayMovingAvg(50)(RequestNum) as RequestNum1 from
    (select UserID, RequestNum from hits_v1 order by RequestNum DESC)  
  group by UserID order by c DESC limit 1000)

Or are these two functions easy to use window functions?

[sundy-li]

we have array_<aggr> which covers arrayReduce function.

group_array_moving_avg can be implemented after we have window aggr function.