Агрегированный запрос по нескольким столбцам (один - массив) в clickhouse

ProgramBox

Агрегированный запрос по нескольким столбцам (один — массив) в clickhouse

Post author:admin
Запись опубликована:8 ноября, 2022
Post category:Вопросы по программированию

#clickhouse

Вопрос:

Я пытаюсь получить агрегированные значения для каждого столбца att1 и att2, а также для каждого значения массивов в столбце att3.

Насколько я пытался:

 create table test(value Float32, att1 String, att2 String, att3 Array(String))
 ENGINE=MergeTree() ORDER BY ();

 INSERT INTO test VALUES (2.0, 'a', 'Z', ['sports', 'office', 'anothertag'])
INSERT INTO test VALUES (4.0, 'b', 'X', ['sports', 'office', 'tag'])
INSERT INTO test VALUES (6.0, 'b', 'X', ['sports', 'internet', 'planes'])

 SELECT * from test;

 ┌─value─┬─att1─┬─att2─┬─att3───────────────────────────┐
│     6 │ b    │ X    │ ['sports','internet','planes'] │
└───────┴──────┴──────┴────────────────────────────────┘
┌─value─┬─att1─┬─att2─┬─att3─────────────────────────────┐
│     2 │ a    │ Z    │ ['sports','office','anothertag'] │
└───────┴──────┴──────┴──────────────────────────────────┘
┌─value─┬─att1─┬─att2─┬─att3──────────────────────┐
│     4 │ b    │ X    │ ['sports','office','tag'] │
└───────┴──────┴──────┴───────────────────────────┘

Я хочу получить совокупную сумму (значение) для каждого отдельного атрибута.

У меня это работает для столбцов att1 и att2 с:

 SELECT
    att1,
    att2,
    sum(value)
FROM test
GROUP BY
    att1,
    att2
    WITH CUBE

Результат:

 ┌─att1─┬─att2─┬─sum(value)─┐
│ b    │ X    │         10 │
│ a    │ Z    │          2 │
└──────┴──────┴────────────┘
┌─att1─┬─att2─┬─sum(value)─┐
│ a    │      │          2 │
│ b    │      │         10 │
└──────┴──────┴────────────┘
┌─att1─┬─att2─┬─sum(value)─┐
│      │ Z    │          2 │
│      │ X    │         10 │
└──────┴──────┴────────────┘
┌─att1─┬─att2─┬─sum(value)─┐
│      │      │         12 │
└──────┴──────┴────────────┘

Что дает мне больше, чем нужно, но результаты два и три дают правильные результаты.

Но мне также нужно значение для каждого значения в att3, оно работает в другом запросе, но при попытке выполнить один запрос:

 SELECT
    att1,
    att2,
    arrayJoin(att3) AS tags,
    sum(value)
FROM test
GROUP BY
    att1,
    att2,
    tags
    WITH CUBE

Что дает (среди прочего):

 ┌─att1─┬─att2─┬─tags─┬─sum(value)─┐
│ a    │      │      │          6 │
│ b    │      │      │         30 │
└──────┴──────┴──────┴────────────┘

┌─att1─┬─att2─┬─tags───────┬─sum(value)─┐
│      │      │ tag        │          4 │
│      │      │ anothertag │          2 │
│      │      │ planes     │          6 │
│      │      │ sports     │         12 │
│      │      │ internet   │          6 │
│      │      │ office     │          6 │
└──────┴──────┴────────────┴────────────┘

Поскольку arrayJoin «разворачивает» массив в строки, теперь значения sum(value) в att1 не являются точными.

Я также попробовал LEFT ARRAY JOIN синтаксис с теми же результатами.

Обновлено:

Идеальным результатом было бы что-то вроде:

 ┌─'att1'─┬─'att2'─┬─'tags'─┬─'sum(value)'─┐
│    a   │        │        │         2    │
│    b   │        │        │         10   │
│        │    X   │        │         10   │
│        │    Z   │        │         2    │
│        │        │ sports │         12   │
│        │        │ office │         6    │
│        │        │ anot.. │         2    │
│        │        │ tag    │         4    │
│        │        │internet│         6    │
│        │        │planes  │         6    │
└────────┴────────┴────────┴──────────────┘

Может быть в разных строках (результатах), но в идеале в одном запросе.

Ответ №1:

 SELECT
    sumMap(([att1], [value])) AS r1,
    sumMap(([att2], [value])) AS r2,
    sumMap((att3, replicate(value, att3))) AS r3
FROM test
┌─r1─────────────────┬─r2─────────────────┬─r3──────────────────────────────────────────────────────────────────────────┐
│ (['a','b'],[2,10]) │ (['X','Z'],[10,2]) │ (['anothertag','internet','office','planes','sports','tag'],[2,6,6,6,12,4]) │
└────────────────────┴────────────────────┴─────────────────────────────────────────────────────────────────────────────┘



SELECT
    (arrayJoin(arrayZip((arrayJoin([sumMap(([att1], [value])), sumMap(([att2], [value])), sumMap((att3, replicate(value, att3)))]) AS r).1, r.2)) AS x).1 AS y,
    x.2 AS z
FROM test
┌─y──────────┬──z─┐
│ a          │  2 │
│ b          │ 10 │
│ X          │ 10 │
│ Z          │  2 │
│ anothertag │  2 │
│ internet   │  6 │
│ office     │  6 │
│ planes     │  6 │
│ sports     │ 12 │
│ tag        │  4 │
└────────────┴────┘

1. Группа тегов имеет дубликаты, поэтому она работает не так, как я ожидал. В реальных данных он разбивается на строки.

2. @chaim тогда я не совсем понимаю, чего вы ожидаете в результате.

3. пожалуйста, объясните, как этот код решает проблему

4. @DennyCrane Я обновил вопрос с возможным желаемым результатом. Это должно объясняться понятнее, чем я 🙂

5. @chaim вам нужна функция суммирования

Ответ №2:

Я думаю, что более простой способ — объединить два запроса:

 SELECT
    att1,
    att2,
    '' AS tags,
    sum(value)
FROM test
GROUP BY
    att1,
    att2
    WITH CUBE
UNION ALL
SELECT
    '' AS att1,
    '' AS att2,
    arrayJoin(att3) AS tags,
    sum(value)
FROM test
GROUP BY tags

/*
┌─att1─┬─att2─┬─tags───────┬─sum(value)─┐
│      │      │ internet   │          6 │
│      │      │ sports     │         12 │
│      │      │ office     │          6 │
│      │      │ tag        │          4 │
│      │      │ planes     │          6 │
│      │      │ anothertag │          2 │
└──────┴──────┴────────────┴────────────┘
┌─att1─┬─att2─┬─tags─┬─sum(value)─┐
│ b    │ X    │      │         10 │
│ a    │ Z    │      │          2 │
└──────┴──────┴──────┴────────────┘
┌─att1─┬─att2─┬─tags─┬─sum(value)─┐
│ a    │      │      │          2 │
│ b    │      │      │         10 │
└──────┴──────┴──────┴────────────┘
┌─att1─┬─att2─┬─tags─┬─sum(value)─┐
│      │ Z    │      │          2 │
│      │ X    │      │         10 │
└──────┴──────┴──────┴────────────┘
┌─att1─┬─att2─┬─tags─┬─sum(value)─┐
│      │      │      │         12 │
└──────┴──────┴──────┴────────────┘

*/