If the window size is countably small, you can do something like this
SELECT
sum(window.2) AS amount,
max(dttm) AS business_dttm,
sum(amt) AS moving_sum
FROM
(
SELECT
arrayJoin([(rowNumberInAllBlocks(), amount), (rowNumberInAllBlocks() + 1, 0), (rowNumberInAllBlocks() + 2, 0)]) AS window,
amount AS amt,
business_dttm AS dttm
FROM
(
SELECT
amount,
business_dttm
FROM A
ORDER BY business_dttm
)
)
GROUP BY window.1
HAVING count() = 3
ORDER BY window.1;
The first two rows are ignored as ClickHouse doesn't collapse aggregates into null. You can prepend them later.
Update:
It's still possible to compute moving sum for arbitrary window sizes. Tune the window_size as you want (3 for this example).
-- Note, rowNumberInAllBlocks is incorrect if declared inside with block due to being stateful
WITH
(
SELECT arrayCumSum(groupArray(amount))
FROM
(
SELECT
amount
FROM A
ORDER BY business_dttm
)
) AS arr,
3 AS window_size
SELECT
amount,
business_dttm,
if(rowNumberInAllBlocks() + 1 < window_size, NULL, arr[rowNumberInAllBlocks() + 1] - arr[rowNumberInAllBlocks() + 1 - window_size]) AS moving_sum
FROM
(
SELECT
amount,
business_dttm
FROM A
ORDER BY business_dttm
)
Or this variant
SELECT
amount,
business_dttm,
moving_sum
FROM
(
WITH 3 AS window_size
SELECT
groupArray(amount) AS amount_arr,
groupArray(business_dttm) AS business_dttm_arr,
arrayCumSum(amount_arr) AS amount_cum_arr,
arrayMap(i -> if(i < window_size, NULL, amount_cum_arr[i] - amount_cum_arr[(i - window_size)]), arrayEnumerate(amount_cum_arr)) AS moving_sum_arr
FROM
(
SELECT *
FROM A
ORDER BY business_dttm ASC
)
)
ARRAY JOIN
amount_arr AS amount,
business_dttm_arr AS business_dttm,
moving_sum_arr AS moving_sum
Fair warning, both approaches are far from optimal, but it exhibits the unique power of ClickHouse beyond SQL.
