ClickHouse / ClickHouse/ClickHouse

Add common array operation primitives

Open
#86,743 2 comments 0 reactions 0 assignees View on GitHub
comp-regular-function external feature
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Company or project name

We are an analytics company with strict performance restrictions, so we want as much as possible done with optimized builtin functions.

### Use case

In this case we need various array manipulation primitives for complex queries. They can be made by composing various other existing functions but at with a high performance penalty I assume.

### Describe the solution you'd like

The missing functions are:

`arrayCumMax` - Cumulative maximum, basically a running maximum: `arrayCumMax([1, 2, 3, 1, 2, 3, 4, 1, 2, 3, 4, 5]) = [1, 2, 3, 3, 3, 3, 4, 4, 4, 4, 4, 5]`

`arrayCumMin` - Cumulative minimum, similar to arrayCumMax: `arrayCumMin([5, 4, 3, 2, 1, 2, 3, 4]) = [5, 4, 3, 2, 1, 1, 1, 1]`

`arrayDelta` -> Very similar to the currently available `arrayDifference`, but the idea is that it becomes the inverse of `arrayCumSum`. Currently `arrayDifference` sets the first element to `0`, which breaks this inverse property. `arrayCumSum(arrayDelta([1, 2, 3, 4])) = [1, 2, 3, 4]`
Not sure about the name, but seems like a good option since `arrayDifference` is already taken.

`arraySome` -> Similar to the existing `arrayAll`, an alternative name would be `arrayAny`.

`arraySplice` -> Similar to Javascript's `splice` function allowing to manipulate the array contents in a pretty powerful interface, see: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/splice

`arrayPadLeft` -> Similar to `arrayResize` but on the left, and doesn't shrink the array.

`arrayPadRight` -> Similar to `arrayResize` but doesn't shrink the array.

`arraySumKahan` -> Similar to `arraySum` but more accurate for floating point. (Not sure if this is already the case)

`arrayCumFold` -> Similar to `arrayFold` but outputs an array with the current value of the accumulator at each element. `arrayCumFold((acc, x)-> acc + x, [1, 2, 3, 4]) = [1, 3, 6, 10]`

Then I think it would also be useful to have the usual stats functions: `arrayVar`, `arrayCovar`, etc...

### Describe alternatives you've considered

Currently we implement some of these in terms of the currently available array functions, but some of them are not trivial to implement, and performance is probably not ideal.

### Additional context

_No response_

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.