【转】如何在ClickHouse中实现RANK OVER排序 (

如何在clickhouse中实现row_number over 和dense_rank over等同效果的查询，它们在一些其他数据库中可用于rank排序。

同样的，ch中并没有直接提供对应的开窗函数，需要利用一些特殊函数变相实现，主要会用到下面几个数组函数，它们分别是:

这些函数均接受一个数组作为输入参数，并返回数组中元素出现的位置，例如:

熟悉开窗函数的看官应该一眼就能明白

arrayenumerate 的效果等同于 row_number

arrayenumeratedense 的效果等同于 dense_rank

而 arrayenumerateuniq 相对特殊，它只返回元素第一次出现的位置

在知道了上述几个函数的作用之后，接下来我用一个具体示例，逐步演示如何实现最终需要的查询效果。

首先准备测试数据集，创建一张测试表

这是一张典型的分数表:

我们的目标，是要实现如下语义的查询:

即按照 id 分组后，基于val 排序并得出rank。

第一步，按 val 排序，因为条件是 order by val :

(因为要返回所有字段，所以这里可以使用 * )

第二步，按 id 分组，因为条件是 partition by id :

第三步，计算val的rank，需要用到刚才介绍的几个arrayenumerate*函数，由于它们的入参要求数组，所以先使用 grouparray将 val 转成数组:

可以看到，到这一步各种形式的rank排序已经查出来了。 第四步，数组展开，利用array join将数组展开，并按照 id 、rank列排序:

至此，整个查询就完成了，我们实现了如下三种语义的查询:

利用rank排序，进一步还能回答哪些问题呢?

分组top n，例如按id分组后，查询排名前3的分数:

由于分数val存在重复数据，此处使用了distinct去重

指定id的分数排名，查询 id = a，val = 70的排名: