Input:
Clickhouse

Table A
business_dttm (datetime)
amount (float)

I need to calculate moving sum for 15 minutes (or for last 3 records) on each business_dttm

For example

amount business_dttm     moving sum

0.3 2018-11-19 13:00:00  

0.3 2018-11-19 13:05:00

0.4 2018-11-19 13:10:00  1

0.5 2018-11-19 13:15:00  1.2

0.6 2018-11-19 13:15:00  1.5

0.7 2018-11-19 13:20:00  1.8

0.8 2018-11-19 13:25:00  2.1

0.9 2018-11-19 13:25:00  2.4

0.5 2018-11-19 13:30:00  2.2

Unfortunately we haven't window functions and join without equal conditions in Clickhouse

How can i do it without cross join and where condition?

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.