时间戳

首页 > 解决方案 > 在clickhouse中对多列（一个是数组）进行聚合查询

clickhouse - 在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'] │
└───────┴──────┴──────┴───────────────────────────┘

我想为每个不同的属性获取聚合 -sum(value)- 。

我让它适用于 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 将数组“展开”成行，因此 att1 中 sum(value) 的值现在不准确。

我也尝试过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    │
└────────┴────────┴────────┴──────────────┘

可以在不同的行（结果）中，但最好在一个查询中。

标签： clickhouse

解决方案

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 │
└────────────┴────┘

推荐阅读