GOAL: find the id, kind with most occurrences, and then count occurrences per 20 secon

Probably blocked by the same limitation like here <a class="issue-link js-issue-link"

recursive CTE primary key optimization for recursive step about clickhouse HOT 1 OPEN

tanner-bruce commented on September 23, 2024

recursive CTE primary key optimization for recursive step

from clickhouse.

Comments (1)

UnamedRus commented on September 23, 2024

Probably blocked by the same limitation like here #55058 (comment)

Check those approaches as well https://kb.altinity.com/altinity-kb-queries-and-syntax/top-n-and-remain/

Your particular query also can be done using "scalar" instead of cte.

WITH (
        SELECT (id, kind, count() AS c)
        FROM events
        GROUP BY
            id,
            kind
        ORDER BY c DESC
        LIMIT 1
    ) AS s
SELECT
    id,
    kind,
    count() AS c,
    toStartOfInterval(time, toIntervalSecond(20)) AS timestamp,
    s.3
FROM events AS t
WHERE (t.id = (s.1)) AND ((s.2) = t.kind)
GROUP BY
    timestamp,
    id,
    kind

Query id: 2161d7c6-24da-4890-b422-44902fb00a69

┌─id─────┬─kind───┬───c─┬───────────timestamp─┬─tupleElement(s, 3)─┐
│ item 9 │ kind 9 │ 442 │ 2024-05-22 02:16:40 │               6249 │
│ item 9 │ kind 9 │ 448 │ 2024-05-22 02:14:20 │               6249 │
│ item 9 │ kind 9 │ 403 │ 2024-05-22 02:15:00 │               6249 │
│ item 9 │ kind 9 │ 393 │ 2024-05-22 02:15:20 │               6249 │
│ item 9 │ kind 9 │ 409 │ 2024-05-22 02:14:00 │               6249 │
│ item 9 │ kind 9 │ 133 │ 2024-05-22 02:12:20 │               6249 │
│ item 9 │ kind 9 │ 415 │ 2024-05-22 02:12:40 │               6249 │
│ item 9 │ kind 9 │ 427 │ 2024-05-22 02:16:00 │               6249 │
│ item 9 │ kind 9 │ 294 │ 2024-05-22 02:17:20 │               6249 │
│ item 9 │ kind 9 │ 411 │ 2024-05-22 02:13:40 │               6249 │
│ item 9 │ kind 9 │ 410 │ 2024-05-22 02:15:40 │               6249 │
│ item 9 │ kind 9 │ 413 │ 2024-05-22 02:14:40 │               6249 │
│ item 9 │ kind 9 │ 411 │ 2024-05-22 02:17:00 │               6249 │
│ item 9 │ kind 9 │ 399 │ 2024-05-22 02:13:00 │               6249 │
│ item 9 │ kind 9 │ 396 │ 2024-05-22 02:16:20 │               6249 │
│ item 9 │ kind 9 │ 445 │ 2024-05-22 02:13:20 │               6249 │
└────────┴────────┴─────┴─────────────────────┴────────────────────┘