在ClickHouse中统计数组元素的数量方法询问
最编程
2024-02-06 10:59:23
...
SELECT key, groupArray((word, count)) AS result
FROM
(
SELECT key, word, count() AS count
FROM
(
/* Emulate the test dataset. */
SELECT data.1 AS key, data.2 AS seq, data.3 AS words
FROM
(
SELECT arrayJoin([
('1', '123', ['AAA', 'BBB']),
('1', '456', ['AAA', 'CCC'])]) AS data
)
)
ARRAY JOIN words AS word
GROUP BY key, word
)
GROUP BY key
/*
┌─key─┬─result──────────────────────────┐
│ 1 │ [('BBB',1),('AAA',2),('CCC',1)] │
└─────┴─────────────────────────────────┘
*/