clickhouse - 对数组进行分组和计数
问题描述
从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)]
输出格式不是那么重要。我不想在这里使用 group-by/count,因为我想通过一个查询来聚合多个字段。
select
arrayReduce('???', groupArray(age)),
arrayReduce('???', groupArray(job)),
arrayReduce('???', groupArray(country))
from customers;
像这样
解决方案
只需进行几个数组的操作:
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
推荐阅读
- python - 将css链接到html的Django问题
- finite-automata - 将此 NFA 转换为 DFA
- deployment - 您如何将 blazor wasm grpc-web 应用程序部署到 azure?
- spring - TCP Client Spring 与 Legacy Server 的集成
- javascript - PixiJs Sprites 在顶部添加行
- python - 如何获得某个数字的固定排列数
- ubuntu - nginx 不会使用 100% cpu
- amazon-web-services - 基于 AWS SES 接收规则,执行 Lambda 函数,使用 SES 发送电子邮件
- regex - 正则表达式搜索没有虚拟的析构函数
- python - 彩色直方图 HSV 1D