关注微信公共号：小程在线
关注CSDN博客：程志伟的博客
ClickHouse server version 21.6.6
ClickHouse中没有ROW_NUMBER OVER 和DENSE_RANK OVER函数
需要借助特殊函数变相实现
1.创建表并插入数据；
insert into hanshu values ('aa',18,'nan'),('aa',19,'nan'),('aa',20,'nan'),('aa',19,'nv');
2.将分组的字段改为数组类型
select id,groupArray(age) as ranking from hanshu group by id;
3.利用arrayEnumerate函数得到排序。
select id,groupArray(age) as ranking,arrayEnumerate(ranking) as cnt from hanshu group by id;
4.使用array join函数实现行列转换。
select * from (
select id,groupArray(age) as ranking,arrayEnumerate(ranking) as cnt
from hanshu group by id) a
array join cnt;
5.补充一点：arrayEnumerateDense函数实现dense_rank函数的功能
select * from (select id,groupArray(age) as ranking,arrayEnumerateDense(ranking) as cnt from hanshu group by id) a array join cnt order by cnt;