group和count a array

carvr3hs 于 2021-07-15 发布在 ClickHouse

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

从relaxo.tracks中选择arrayreduce（'groupuniqarray'，grouparray（browser））； arrayReduce 不适用于任意的lambda。有没有办法计算数组中元素的出现次数？喜欢

select groupArray(age) from customers;
:) [21, 40, 20, 20, 20, 30]
select arrayReduce('groupUniqArray', groupArray(age)) from customers;
:) [21, 40, 20, 30]
select arrayReduce('???', groupArray(age)) from customers;
:) [(21, 1), (40, 1), (20, 3), (30, 1)]

输出格式没有那么重要。我不想在这里使用groupby/count，因为我想用一个查询聚合多个字段。

select 
  arrayReduce('???', groupArray(age)),
  arrayReduce('???', groupArray(job)),
  arrayReduce('???', groupArray(country))
from customers;

这样地

clickhouse

来源：https://stackoverflow.com/questions/55382296/group-and-count-an-array

1条答案

按热度按时间

zed5wv101#

只需进行几次数组操作：

SELECT
    groupArray(age) AS ages,
    arrayReduce('groupUniqArray', ages) AS uniqAges,
    arraySort(x -> x.1, arrayMap(x -> (x, countEqual(ages, x)), uniqAges)) AS resultAges,

    groupArray(job) AS jobs,
    arrayReduce('groupUniqArray', jobs) AS uniqJobs,
    arraySort(x -> x.1, arrayMap(x -> (x, countEqual(jobs, x)), uniqJobs)) AS resultJobs,

    groupArray(country) AS countries,
    arrayReduce('groupUniqArray', countries) AS uniqCountries,
    arraySort(x -> x.1, arrayMap(x -> (x, countEqual(countries, x)), uniqCountries)) AS resultCountries
FROM test.test4
FORMAT Vertical

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

我来回答

group和count a array

1条答案

相关问题

热门标签

最新问答