贵州省水利建设管理总站网站,龙岩网站设计 都找推商吧系统,wordpress支持大文件上传,运营设计是干什么的文章目录 1、源数据2、生成数组2.1 groupArray 分组合并为数组2.2 arrayEnumerate 标记数据 3、rank()、row_number()3.1 说明3.2 使用 目前应用很多需求设计对数据分组并去特定数量的数据#xff1b; clickhouse 新版本增加了row_number()#xff0c;rank() 函数#xff0c… 文章目录 1、源数据2、生成数组2.1 groupArray 分组合并为数组2.2 arrayEnumerate 标记数据 3、rank()、row_number()3.1 说明3.2 使用 目前应用很多需求设计对数据分组并去特定数量的数据 clickhouse 新版本增加了row_number()rank() 函数可以直接对分组数据添加行号下面是记录了这两个函数的基本使用另外用 groupArray方式也大概实现了添加行号(官网上有说 groupArrayLast 可以实现自动排序的功能)但是实测这函数没法使用所以暂不记录 1、源数据
select a as name, 25 as age,165 as height union all
select b as name, 21 as age,182 as height union all
select a as name, 21 as age,187 as height union all
select a as name, 25 as age,158 as height union all
select b as name, 22 as age,168 as height2、生成数组
2.1 groupArray 分组合并为数组 groupArray 会把同类型的值合并为数组并过滤NULL值数据格式groupArray(max_size)(fields) -- 不限制分组数量
select name,groupArray(age) from
(select a as name, 25 as age,165 as height union all select b as name, 21 as age,182 as height union all select a as name, 21 as age,187 as height union all select a as name, 25 as age,158 as height union all select b as name, 22 as age,168 as height
) a group by name;namegroupArray(age)b[21,22]a[25,21,25]
-- 限制分组数量为1
-- 如果先对内部数据排序再分组可拿取age最大的一条
-- arrayStringConcat 将数据用特定字符合并
select name,groupArray(1)(age),arrayStringConcat(groupArray(1)(age),) from
(select * from (select a as name, 25 as age,165 as height union all select b as name, 21 as age,182 as height union all select a as name, 21 as age,187 as height union all select a as name, 25 as age,158 as height union all select b as name, 22 as age,168 as height) a order by age desc
) r group by name;namegroupArray(1)(age)arrayStringConcat(groupArray(1)(age),‘’)b[22]22a[25]25
2.2 arrayEnumerate 标记数据
select name,groupArray(age) as values,arrayEnumerate(values) as indexs from
(select a as name, 25 as age,165 as height union all select b as name, 21 as age,182 as height union all select a as name, 21 as age,187 as height union all select a as name, 25 as age,158 as height union all select b as name, 22 as age,168 as height
) a group by name;namevaluesindexsb[21,22][1,2]a[25,21,25][1,2,3]
3、rank()、row_number()
3.1 说明
https://clickhouse.com/docs/en/sql-reference/window-functions
3.2 使用
--rank()
select name,age,rank() over(partition by name order by age asc ) from
(select a as name, 25 as age,165 as height union all select b as name, 21 as age,182 as height union all select a as name, 21 as age,187 as height union all select a as name, 25 as age,158 as height union all select b as name, 22 as age,168 as height
) a group by name,age;-- row_number()
select name,age,row_number() over(partition by name order by age asc ) from
(select a as name, 25 as age,165 as height union all select b as name, 21 as age,182 as height union all select a as name, 21 as age,187 as height union all select a as name, 25 as age,158 as height union all select b as name, 22 as age,168 as height
) a group by name,age;nameagerank() OVER (PARTITION BY name ORDER BY age ASC)a211a252b211b222
PS. 行号已经标明后续需要取多少数据设置行号条件即可
