Блог

Учитывая следующую таблицу:

 CREATE TABLE main
(
    `job_id` UUID,
    `request_time` DateTime,
    `host_id` UInt8,
    `status_code` LowCardinality(String),
)
ENGINE = MergeTree
ORDER BY request_time
SETTINGS index_granularity = 8192
 

Я пытаюсь получить все статусы по хосту с их относительным процентом. Для этого мне нужно подсчитать результаты, сгруппированные по хосту и статусу, и разделить каждое подсчитанное поле на общее количество полей хоста.

Например, этот запрос работал бы в MySQL:

 SELECT
    main.host_id,
    main.status_code,
    COUNT() AS status_count,
    COUNT() / sub.host_total * 100 AS percent
FROM
    main
INNER JOIN (
    SELECT host_id, COUNT() AS host_total
    FROM main
    GROUP BY host_id
) AS sub ON (sub.host_id = main.host_id)
GROUP BY
    main.host_id,
    main.status_code
 

Но ClickHouse выдает:

DB::Exception: Неизвестный идентификатор: host_total; есть столбцы: host_id, status_code, count(): при обработке host_id, status_code, count() КАК status_count, (count() / host_total) * 100 В процентах. (UNKNOWN_IDENTIFIER)

Вероятно, потому, что коррелированные (зависимые) подзапросы не поддерживаются.

Мне предложили использовать CTE, поэтому я попробовал это:

 WITH sub AS (
    SELECT host_id, COUNT() AS host_total
    FROM main
    GROUP BY host_id
)
SELECT
    main.host_id,
    main.status_code,
    COUNT() AS status_count,
    COUNT() / (SELECT host_total FROM sub WHERE sub.host_id = main.host_id) * 100 AS percent
FROM
    main
GROUP BY
    main.host_id,
    main.status_code
 

Но все равно не повезло:

DB ::Exception: отсутствуют столбцы: ‘main.host_id’ при обработке запроса: ‘ВЫБЕРИТЕ host_total ИЗ sub, ГДЕ host_id = main.host_id’, обязательные столбцы: ‘host_total’ ‘host_id’ ‘main.host_id’ ‘host_total’ ‘host_id’ ‘main.host_id’: при обработке (ВЫБЕРИТЕhost_total ИЗ sub, ГДЕ sub.host_id = main.host_id) КАК _subquery20: При обработке count() / ((ВЫБЕРИТЕ host_total ИЗ sub, ГДЕ sub.host_id = main.host_id) КАК _subquery20): При обработке (count() / ((ВЫБЕРИТЕ host_total ИЗ sub, ГДЕ sub.host_id = main.host_id) КАК _subquery20.host_id) КАК _subquery20)) * 100 В процентах. (UNKNOWN_IDENTIFIER)

CH выдает некорректную ошибку. https://github.com/ClickHouse/ClickHouse/issues/4567

host_total должен быть в разделе groupby или в разделе aggregate function

 insert into main(request_time, host_id,status_code) values ( now(), 1, 200);
insert into main(request_time, host_id,status_code) values ( now(), 1, 500);
insert into main(request_time, host_id,status_code) values ( now(), 1, 200);
insert into main(request_time, host_id,status_code) values ( now(), 2, 500);
insert into main(request_time, host_id,status_code) values ( now(), 2, 200);
insert into main(request_time, host_id,status_code) values ( now(), 3, 500);

SELECT
    main.host_id,
    main.status_code,
    COUNT() AS status_count,
    round((COUNT() / any(sub.host_total)) * 100, 2) AS percent
FROM main
INNER JOIN
(
    SELECT
        host_id,
        COUNT() AS host_total
    FROM main
    GROUP BY host_id
) AS sub ON sub.host_id = main.host_id
GROUP BY
    main.host_id,
    main.status_code
ORDER BY
    main.host_id ASC,
    main.status_code ASC

┌─host_id─┬─status_code─┬─status_count─┬─percent─┐
│       1 │ 200         │            2 │   66.67 │
│       1 │ 500         │            1 │   33.33 │
│       2 │ 200         │            1 │      50 │
│       2 │ 500         │            1 │      50 │
│       3 │ 500         │            1 │     100 │
└─────────┴─────────────┴──────────────┴─────────┘
 

Но есть лучшие способы решить эту проблему:

функции окна

 SELECT
    host_id,
    status_code,
    status_count,
    round((status_count / host_total) * 100, 2) AS percent
FROM
(
    SELECT
        host_id,
        status_code,
        status_count,
        sum(status_count) OVER (PARTITION BY host_id) AS host_total
    FROM
    (
        SELECT
            host_id,
            status_code,
            COUNT() AS status_count
        FROM main
        GROUP BY
            host_id,
            status_code
    )
)
ORDER BY
    host_id ASC,
    status_code ASC

┌─host_id─┬─status_code─┬─status_count─┬─percent─┐
│       1 │ 200         │            2 │   66.67 │
│       1 │ 500         │            1 │   33.33 │
│       2 │ 200         │            1 │      50 │
│       2 │ 500         │            1 │      50 │
│       3 │ 500         │            1 │     100 │
└─────────┴─────────────┴──────────────┴─────────┘
 

массивы

 SELECT
    host_id,
    status_code,
    status_count,
    round((status_count / host_total) * 100, 2) AS percent
FROM
(
    SELECT
        host_id,
        sumMap([CAST(status_code, 'String')], [1]) AS ga,
        count() AS host_total
    FROM main
    GROUP BY host_id
)
ARRAY JOIN
    ga.1 AS status_code,
    ga.2 AS status_count

┌─host_id─┬─status_code─┬─status_count─┬─percent─┐
│       1 │ 200         │            2 │   66.67 │
│       1 │ 500         │            1 │   33.33 │
│       2 │ 200         │            1 │      50 │
│       2 │ 500         │            1 │      50 │
│       3 │ 500         │            1 │     100 │
└─────────┴─────────────┴──────────────┴─────────┘