如果窗口的大小很小，你可以这样做

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;

前两行被忽略，因为clickhouse不会将聚合折叠为null。你可以稍后再做准备。

更新：

对于任意大小的窗口，仍然可以计算移动和。调整 window_size 如您所愿（本例中为3）。

-- 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
)

或者这个变种

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

公平地说，这两种方法都远不是最佳的，但它展示了clickhouse超越sql的独特功能。