前言
最近公司选用clickhouse体系作为数据解决方案,ck由于底层存储区别hadoop函数,导致hql一些很好用的特性无法按照之前的写法来实现,比如按分区设行号,first_value(),lag()等开窗函数也无法正常使用,这也直接影响了我们实现业务的效率,可能导致直接懵逼…这不趁着周末来研究一番ck特殊语法。以保证以后快速从ck中找出惯用的hql逻辑的替代方案。虽然目前ck已经也开始有自己的窗口函数,不过相对hive来说还有待完善。
不过既然选择了ck,那么新的学习成本也是有的,查询方面它也并不是那么一无是处,必须花点精力理解下它特有的函数,也许我们就会醍醐灌顶,找出较好的替代解决思路。在理解ck一些函数特性后,我也发现hql的开窗函数ck也是可以通过一些逻辑来实现的。第一节就从分区设置行号说起吧。。ck里面有行号函数,但是只能是全局的rowNumberInAllBlocks()
不满足需求,不要看这个需求简单,实际用到的地方可不会少。
准备
- 建表
CREATE TABLE user_log -- 用户消费日志
(
uid String, -- 唯一id
value Int32 -- 金额
)
ENGINE = MergeTree PARTITION BY uid order by uid;
- 插数
insert into user_log
values
('A',100),
('B',400),
('D',600),
('A',50),
('D',400),
('A',2000);
- 需求
下面说下我们需求,就是按用户分组,根据每个用户的消费记录,按金额从大到小排序后分别设置行号。
逻辑
下面到关键的查询了,这里会用到groupArray() 、arrayEnumerate()
这个两个聚合函数以及array join
语法。函数不清楚的可以参考官网。逻辑上理解:就是先分组,用groupArray
将每组的value构造出一个数组,然后获取数组中元素下标,最后用array join
将元素和下标都读出来。好了话不多说上代码。
代码
select
uid,
value,
row_number
from(
select
uid,
groupArray(value) as value_list,
arrayEnumerate(value_list) as index_list
from(
select *
from user_log
order by value desc
)
group by uid
)array join value_list as value,
index_list as row_number
order by uid;
查询结果:
-------------------
┌─uid─┬─value─┬─row_number─┐
│ A │ 2000 │ 1 │
│ A │ 100 │ 2 │
│ A │ 50 │ 3 │
│ B │ 400 │ 1 │
│ D │ 600 │ 1 │
│ D │ 400 │ 2 │
└─────┴───────┴────────────┘
6 rows in set. Elapsed: 0.010 sec.
结尾
结果和我们想要的是一致的。行号只是众多需求中的一个,重点是对ck特有函数的学习,如果你真的理解了groupArray,arrayMap,argMin(arg, val)...
等聚合函数的用法,那么你已经掌握了ck的一项利器了!