Interval between several dates ClickHouse

Question

There is a table in ClickHouse that is constantly updated, format:

date_time           | shop_id | item_id | status | balance
---------------------------------------------------------------
2022-09-09 13:00:01 | abc     | 1234    | 0      | 0
2022-09-09 13:00:00 | abc     | 1234    | 1      | 3
2022-09-09 12:50:00 | abc     | 1234    | 1      | 10

create table x(d DateTime, sh String, i Int64, s String, b Float64) Engine=Memory;

insert into x format CSV 2022-09-09 13:00:01,abc,1234,0,0
2022-09-09 13:00:00,abc,1234,1,3
2022-09-09 12:50:00,abc,1234,1,10

The table stores statuses and balances for each item_id, when the balance is changed, a new record with status, time and balance is added. If the balance is = 0, the status changes to 0.

Need to calculate how much time (how many minutes) every item_id in the shop was available for the day. The status may change several times a day.

Please help me calculate this.

Denny Crane · Answer

SELECT
    sh,
    i,
    sum(m)
FROM
(
    SELECT
        sh,
        i,
        arrayMap(x -> (((x[-1]).1) - ((x[1]).1)), arrayFilter(y -> (((y[1]).2) != '0'), arraySplit(j -> ((j.2) = '0'), arraySort(i -> (i.1), groupArray((d, s)) AS a))))[1] AS m
    FROM x
    GROUP BY
        sh,
        i
)
GROUP BY
    sh,
    i

Query id: 72643508-1f58-4f49-9718-6e4851541134

┌─sh──┬────i─┬─sum(m)─┐
│ abc │ 1234 │    600 │
└─────┴──────┴────────┘

SELECT
    sh,
    toNullable(i) AS i,
    sum(m)
FROM
(
    SELECT
        sh,
        i,
        arrayMap(x -> (((x[-1]).1) - ((x[1]).1)), arrayFilter(y -> (((y[1]).2) != '0'), arraySplit(j -> ((j.2) = '0'), arraySort(i -> (i.1), groupArray((d, s)) AS a))))[1] AS m
    FROM x
    GROUP BY
        sh,
        i
)
GROUP BY
    GROUPING SETS (
        (sh),
        (sh, i))

Query id: 45a941dc-b8d4-494d-a0fe-b03f697f2966

┌─sh──┬────i─┬─sum(m)─┐
│ abc │ ᴺᵁᴸᴸ │    600 │
└─────┴──────┴────────┘
┌─sh──┬────i─┬─sum(m)─┐
│ abc │ 1234 │    600 │
└─────┴──────┴────────┘

Interval between several dates ClickHouse

1 Answers