all_parts.sql

gistfile1.txt

WITH
    'default' AS db_name,
    'upclick_metrics' AS table_name,
    10 AS interval_seconds, -- NEED TO CHANGE FILL STEP accordingly - see WITH FILL STEP below
    (SELECT uuid FROM system.tables WHERE database = db_name and name = table_name) AS table_id,
    (SELECT min(event_time) FROM system.part_log WHERE table_uuid = table_id) AS start_time,
    T1 AS (
        SELECT
            toStartOfInterval(event_time, toIntervalSecond(interval_seconds)) AS t,
            countIf(event_type != 'RemovePart') as add_events,
            countIf(event_type = 'RemovePart') as remove_events
        FROM
            system.part_log
        WHERE
            table_uuid = table_id
        GROUP BY
            t
        ORDER BY t ASC WITH FILL STEP (10)
    ),
    T2 AS (
        SELECT
            groupArray(t) AS t,
            groupArrayMovingSum(add_events) AS add_events,
            groupArrayMovingSum(remove_events) AS remove_events
        FROM T1
    ),
    T3 AS (
        SELECT
            t,
            arrayMap(e -> e.1 - e.2, arrayZip(add_events, remove_events)) as parts
        FROM T2
    )
SELECT
    t,
    dateDiff('second', toStartOfInterval(start_time, toIntervalSecond(interval_seconds)), t) AS seconds,
    parts
FROM T3
ARRAY JOIN
    t,
    parts
ORDER BY t DESC;