clickhouse选取group后的首末行

在上一篇文章 clickhouse中使用AggregatingMergeTree表引擎 中，我通过一个简单的实际例子，演示了AggregatingMergeTree表引擎的使用方式。在这个例子中，我们统计了用户的 最小付款金额、最大付款金额，以及付款总额。但还有一种常见的统计：统计分组后的首行/尾行记录，例如：用户的首次付款金额、首次订单号、用户最后一次付款金额、最后一次订单号。这篇文章将介绍在Clilckhouse中处理这种情况的几种方式。

准备工作

我们先做一些数据准备，先创建一张user_order用户订单表：

drop table if exists user_order;
create table user_order
(    
    user_id String,         // 用户ID
    event_date String,      // 付款日期
    order_no String,        // 订单号
    amount Int32            // 金额
) ENGINE = MergeTree()
ORDER BY (user_id, event_date)

然后插入测试数据：

insert into user_order(user_id, event_date, order_no, amount)
values('user1', '2022-01-01', 'B', 4);
insert into user_order(user_id, event_date, order_no, amount)
values('user1', '2022-01-02', 'C', 8);
insert into user_order(user_id, event_date, order_no, amount)
values('user1', '2022-01-03', 'A', 2);

我们需要获得分组后的首行，即是需要得到 ('user1', '2022-01-01', "B", 4) 这行数据。

如果此时多次执行 select * from user_order，可以看到返回的表格数据，行的顺序是不确定的：

方式1：使用窗体函数（window function）

通常在mysql等传统关系型数据库中遇到这种情况，我们可以使用窗体函数，row_number()，查询语句类似下面：

select user_id, event_date, amount, order_no, x.row_index
from 
(select user_id, event_date, amount, order_no,
row_number() OVER (PARTITION BY user_id ORDER BY event_date asc) row_index
from user_order) x
where x.row_index = 1

注意这里并没有Group by语句，如果去掉where语句再次进行查询，可以看到相当于给原表加了一列分组后的排序索引。

关于window function的详细介绍，可以看这里：Window Function Concepts and Syntax。

方式2：使用any()、anyLast() 函数

窗体函数在数据量大的时候性能堪忧，在clickhouse中还有其他的处理方式。比如使用any()、anyLast()函数。

按官方文档的定义：any() “selects the first encountered value.”，也就是返回遇到的首个值，看上去是很符合当前的情况。但文档又做了说明：因为查询可能是以任意顺序执行的，并且可能每次执行得顺序都不同（如同我们上面的select * from user_order返回的行顺序不同），所以这个函数的执行结果可能是不确定的。如果要获得确定的值，可以使用“min”或者“max”。或者，select的对象的是一个已经排序过的子查询。

显而易见，对于这里的情况，我们是无法使用 min() 或者 max() 来取首行或者末行的，比如下面的语句：

select user_id, min(event_date) event_date, min(amount) amount, min(order_no) order_no
from user_order 
group by user_id 

这样只会得出错配的结果，例如返回这样本不存在的组合：('user1', '2022-01-01', 'A', 2)。因为这些聚合函数都是针对“单列”的，而我们是取“首行”，维度不一致。

接下来换any()函数试一下：

SELECT user_id, any(event_date) event_date,
any(order_no) order_no, any(amount) amount
From user_order
group by user_id

此时并没有得到预期的结果，记得上面 select * from user_order 返回的顺序是不确定的，这里any()返回的也是不确定的，在我电脑上执行结果返回的是：('user1', '2022-01-03', 'A', 2)，最后一行，而不是第一行。

那么此时，就需要按官方文档所说，将from的对象改为基于某个字段排序的子查询。我们以user_id、event_date 排序：

SELECT user_id, any(event_date) event_date,
any(order_no) order_no, any(amount) amount
From (select * from user_order order by user_id, event_date)
group by user_id

因为排序后的字段顺序是确定的，并且我们的插入顺序，和event_date相一致。通过这种间接的方式，就实现了通过any()函数获取首行的需求。这里需要特别注意的是：在数据量大的情况下，排序是一件很耗时的操作，所以如果需要得到这样的统计值，仍然可以使用 clickhouse中使用AggregatingMergeTree表引擎 中介绍的方法，针对上面的查询建一个物化视图，后续的查询针对该物化视图进行，减轻针对明细表查询的负担。这里就不再演示了。

可见，不论是窗体函数、any()函数，如果要取确定的首行，就需要有一个排序的字段，这个排序字段的数值大小需要和数据写入的顺序相一致。

方式3：使用Clickhouse的分组和数组函数

除了使用any()函数以外，还有一组函数，也可以实现类似的需求。它用到了clickhouse的几个其他的函数：

1. groupArray()

groupArray函数可以在group时，将未参与group的字段以数组的方式返回：

SELECT user_id, groupArray(amount) amount_arr From user_order
group by user_id 

输出为：

user_id amount_arr
user1	[2,8,4]

注意到这里，因为没有排序，所以返回的金额顺序 [2,8,4] 已经和插入时的顺序 [4,8,2] 不一致了。如果多刷新几次，也可能返回 [2,4,8] 等，总之就是不定的。

2. arraySlice(array, offset[, length])

arraySlice函数可以返回数组的一部分，它接收3个参数，第1个参数是数组，第2个参数是偏移量，如果从第1个元素开始，则offset取值为1（这里有点奇怪，按我理解是0才比较合理），第3个参数是长度。

对于我们这里的需求，可以将SQL语句改写成下面这样：

SELECT user_id, 
arraySlice(groupArray(amount),1,1) first_amount 
From user_order
group by user_id

user_id first_amount
user1	[2]

3. arrayJoin()

这个函数有点类似于groupArray()的逆向操作，groupArray()是把一列的多个数据合并为单个数组；而arrayJoin()则把一个数组展开成多个列。比如下面这样：

select arrayJoin([1,2,3]) as amount

输出为：

amount
1
2
3

如果有其他列，则会复制多列的值：

select arrayJoin([1,2,3]) as amount, 'user1' as user_id

输出为：

amount user_id
1	user1
2	user1
3	user1

对于我们这里的实际情况，因为在arraySlice之后，只取首个，那么可以在arraySlice上再嵌套一层arrayJoin()函数，则可以实现我们需要的效果：

SELECT user_id,  
arrayJoin(arraySlice(groupArray(event_date),1,1)) event_date,  
arrayJoin(arraySlice(groupArray(order_no),1,1)) order_no,
arrayJoin(arraySlice(groupArray(amount),1,1)) amount 
From user_order
group by user_id 

在我的电脑上执行时，上面的语句返回了：('user1', '2022-01-03', 'A', 2) 这行数据。类似any()函数的处理方式，我们需要写一个排过序的子查询：

SELECT user_id,  
arrayJoin(arraySlice(groupArray(event_date),1,1)) event_date,  
arrayJoin(arraySlice(groupArray(order_no),1,1)) order_no,
arrayJoin(arraySlice(groupArray(amount),1,1)) amount 
From (select * from user_order order by user_id, event_date)
group by user_id

如此，便可以得到期望的结果。

总结

在这篇文章中，我们针对查询中常见的一种情况：获取分组后的首末行，介绍了在clickhouse中可行的三种方式：窗体函数、any()/anyLast()函数，以及groupArray()/arraySlice()/arrayJoin()系列函数。综合起来，使用any()/anyLast()再配合AggregatingMergeTree物化视图，应该是处理这种情况的一种较优的方式。

感谢阅读，希望这篇文章能给你带来帮助！