Clickhouse - Moving Sum in Arrays

Question

I am looking at an efficient way to do a Sum of (n forward-looking array elements) in an array.

For e.g.

Input -> [1,2,3,4,5,6,7,8]
Expected Result (for n = 2) -> [3,5,7,9,11,13,15,8]

Similarly if the n=3 then Expected Result -> [6,9,12,15,18,21,15,8]

Is there a way, this can be accomplished in a super-efficient way within CH. Thanks!

Edit: I would like to know whether this can be accomplished without using
arrayReduceInRanges(agg_func, ranges, arr1...). The CH version (version 19.15.1) we are using doesn't have arrayReduceInRanges

score 2 · Answer 1 · answered Mar 18 '21 at 14:50

Try this one:

WITH 2 AS n
SELECT
    range(1, 9, 1) AS arr,
    arrayMap((x, index) -> arraySum(arraySlice(arr, index, n)), arr, arrayEnumerate(arr)) AS result

┌─arr───────────────┬─result───────────────┐
│ [1,2,3,4,5,6,7,8] │ [3,5,7,9,11,13,15,8] │
└───────────────────┴──────────────────────┘

Thank you so much Vladimir...very helpful!! – calgs Mar 18 '21 at 16:07 — calgs, Mar 18 '21 at 16:07

Clickhouse - Moving Sum in Arrays

1 Answers1