clickhouse（一、 高阶函数应用实现hive分区设置行号row_number() ）

前言

最近公司选用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的一项利器了！