sql—将clickhouse中嵌套类型的不同键值对收集到数组中

e37o9pze 于 2021-07-15 发布在 ClickHouse

关注(0)|答案(1)|浏览(367)

我在clickhouse中有以下模式的数据：

CREATE TABLE table AS (
     key String,
     …
     nested Nested (
            key String,
            value String
     )
) …

一些示例数据：

key | … | nested                        |
----|---|-------------------------------|
k1  |   | [{"key": "a", "value": "1"}]  |
k1  |   | [{"key": "a", "value": "2"}]  |
k1  |   | [{"key": "a", "value": "1"},  |
    |   |   "key": "a", "value": "2"}]  |
k1  |   | [{"key": "b", "value": "3"    |

我想按键分组，并将所有不同的键值对收集到两个数组中：

key   |  nested.key     |  nested.value    |
------|-----------------|------------------|
k1    | ["a", "a", "b"] |  ["1", "2", "3"] |

在clickhouse中，最简单、最有效的方法是什么？

sql clickhouse

来源：https://stackoverflow.com/questions/57595840/collecting-distinct-key-value-pairs-of-nested-type-in-clickhouse-into-arrays

1条答案

按热度按时间

gab6jxml1#

我建议这样问：

SELECT DISTINCT
    key,
    arrayDistinct(groupArray((nested.key, nested.value))) AS distinctNested,
    arrayMap(x -> (x.1), distinctNested) AS `nested.keys`,
    arrayMap(x -> (x.2), distinctNested) AS `nested.values`
FROM test.table_002
ARRAY JOIN nested
GROUP BY key

/* Result
┌─key─┬─distinctNested──────────────────┬─nested.keys───┬─nested.values─┐
│ k1  │ [('a','1'),('a','2'),('b','3')] │ ['a','a','b'] │ ['1','2','3'] │
└─────┴─────────────────────────────────┴───────────────┴───────────────┘

* /

/* Test data preparing */

CREATE TABLE test.table_002 (
  key String,
  nested Nested (key String, value String)
) ENGINE = Memory;

INSERT INTO test.table_002
FORMAT JSONEachRow
{"key": "k1", "nested.key":["a"], "nested.value": ["1"]}
{"key": "k1", "nested.key":["a"], "nested.value": ["2"]}
{"key": "k1", "nested.key":["a", "a"], "nested.value": ["1", "2"]}
{"key": "k1", "nested.key":["b"], "nested.value": ["3"]}

赞(0）回复(0）举报 2021-07-15

我来回答

sql—将clickhouse中嵌套类型的不同键值对收集到数组中

1条答案

相关问题

热门标签

最新问答