Clickhouse 实现row_number、dense_rank

关注微信公共号：小程在线

关注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;