CODEWITHSUNDEEP
clickhouse
fl00r
fl00r

Reputation: 83680

Group and count an Array

select arrayReduce('groupUniqArray', groupArray(browser)) from relaxo.tracks;

arrayReduce doesn't work with arbitrary lambdas. Is there a way to calculate counts of occurrences elements in an array? Like

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)]

The output format is not that important. I don't want to use group-by/count here because I want to have aggregates for multiple fields with one query.

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

like this

Upvotes: 2

Views: 2644

Answers (1)

vladimir
vladimir

Reputation: 15218

Just take several array's manipulations:

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

Upvotes: 5

Related Questions