【转】如何在ClickHouse中实现RANK OVER排序 ("开窗函数")
原文链接：ClickHouse的秘密基地（chcave），作者：凯朱
如何在ClickHouse中实现ROW_NUMBER OVER 和DENSE_RANK OVER等同效果的查询，它们在一些其他数据库中可用于RANK排序。
同样的，CH中并没有直接提供对应的开窗函数，需要利用一些特殊函数变相实现，主要会用到下面几个数组函数，它们分别是:
arrayEnumerate
arrayEnumerateDense
arrayEnumerateUniq
这些函数均接受一个数组作为输入参数，并返回数组中元素出现的位置，例如:
ch7.nauu.com :) SELECT arrayEnumerate([10,20,30,10,40]) AS row_number, arrayEnumerateDense([10,20,30,10,40]) AS dense_rank, arrayEnumerateUniq([10,20,30,10,40]) AS uniq_rank SELECT arrayEnumerate([10, 20, 30, 10, 40]) AS row_number, arrayEnumerateDense([10, 20, 30, 10, 40]) AS dense_rank, arrayEnumerateUniq([10, 20, 30, 10, 40]) AS uniq_rank ┌─row_number──┬─dense_rank──┬─uniq_rank───┐ │ [1,2,3,4,5] │ [1,2,3,1,4] │ [1,1,1,2,1] │ └─────────────┴─────────────┴─────────────┘ 1 rows in set. Elapsed: 0.005 sec.
熟悉开窗函数的看官应该一眼就能明白
arrayEnumerate 的效果等同于 ROW_NUMBER
arrayEnumerateDense 的效果等同于 DENSE_RANK
而 arrayEnumerateUniq 相对特殊，它只返回元素第一次出现的位置
在知道了上述几个函数的作用之后，接下来我用一个具体示例，逐步演示如何实现最终需要的查询效果。
首先准备测试数据集，创建一张测试表
CREATE TABLE test_data engine = Memory AS WITH( SELECT ['A','A','A','A','B','B','B','B','B','A','59','90','80','80','65','75','78','88','99','70'])AS dictSELECT dict[number%10+1] AS id, dict[number+11] AS val FROM system.numbers LIMIT 10
这是一张典型的分数表:
ch7.nauu.com :) SELECT * FROM test_data SELECT *FROM test_data ┌─id─┬─val─┐ │ A │ 59 │ │ A │ 90 │ │ A │ 80 │ │ A │ 80 │ │ B │ 65 │ │ B │ 75 │ │ B │ 78 │ │ B │ 88 │ │ B │ 99 │ │ A │ 70 │ └────┴─────┘ 10 rows in set. Elapsed: 0.002 sec.
我们的目标，是要实现如下语义的查询:
ROW_NUMBER() OVER( PARTITION BY id ORDER BY val ) DENSE_RANK() OVER( PARTITION BY id ORDER BY val ) UNIQ_RANK() OVER( PARTITION BY id ORDER BY val )
即按照 id 分组后，基于val 排序并得出RANK。
第一步，按 val 排序，因为条件是 ORDER BY val :
SELECT * FROM test_data ORDER BY val
(因为要返回所有字段，所以这里可以使用 * )
第二步，按 id 分组，因为条件是 PARTITION BY id :
SELECT id FROM ( SELECT * FROM test_data ORDER BY val ASC ) GROUP BY id ┌─id─┐ │ B │ │ A │ └────┘ 2 rows in set. Elapsed: 0.006 sec.
第三步，计算val的RANK，需要用到刚才介绍的几个arrayEnumerate*函数，由于它们的入参要求数组，所以先使用 groupArray将 val 转成数组:
SELECT id, groupArray(val) AS arr_val, arrayEnumerate(arr_val) AS row_number, arrayEnumerateDense(arr_val) AS dense_rank, arrayEnumerateUniq(arr_val) AS uniq_rank FROM ( SELECT * FROM test_data ORDER BY val ASC ) GROUP BY id ┌─id─┬─arr_val────────────────────┬─row_number──┬─dense_rank──┬─uniq_rank───┐ │ B │ ['65','75','78','88','99'] │ [1,2,3,4,5] │ [1,2,3,4,5] │ [1,1,1,1,1] │ │ A │ ['59','70','80','80','90'] │ [1,2,3,4,5] │ [1,2,3,3,4] │ [1,1,1,2,1] │ └────┴────────────────────────────┴─────────────┴─────────────┴─────────────┘
可以看到，到这一步各种形式的RANK排序已经查出来了。 第四步，数组展开，利用ARRAY JOIN将数组展开，并按照 id 、RANK列排序:
SELECT id, val, row_number, dense_rank, uniq_rank FROM ( SELECT id, groupArray(val) AS arr_val, arrayEnumerate(arr_val) AS row_number, arrayEnumerateDense(arr_val) AS dense_rank, arrayEnumerateUniq(arr_val) AS uniq_rank FROM ( SELECT * FROM test_data ORDER BY val ASC ) GROUP BY id ) ARRAY JOIN arr_val AS val, row_number, dense_rank, uniq_rank ORDER BY id ASC, row_number ASC, dense_rank ASC ┌─id─┬─val─┬─row_number─┬─dense_rank─┬─uniq_rank─┐ │ A │ 59 │ 1 │ 1 │ 1 │ │ A │ 70 │ 2 │ 2 │ 1 │ │ A │ 80 │ 3 │ 3 │ 1 │ │ A │ 80 │ 4 │ 3 │ 2 │ │ A │ 90 │ 5 │ 4 │ 1 │ │ B │ 65 │ 1 │ 1 │ 1 │ │ B │ 75 │ 2 │ 2 │ 1 │ │ B │ 78 │ 3 │ 3 │ 1 │ │ B │ 88 │ 4 │ 4 │ 1 │ │ B │ 99 │ 5 │ 5 │ 1 │ └────┴─────┴────────────┴────────────┴───────────┘ 10 rows in set. Elapsed: 0.004 sec.
至此，整个查询就完成了，我们实现了如下三种语义的查询:
ROW_NUMBER() OVER( PARTITION BY id ORDER BY val ) DENSE_RANK() OVER( PARTITION BY id ORDER BY val ) UNIQ_RANK() OVER( PARTITION BY id ORDER BY val )
利用RANK排序，进一步还能回答哪些问题呢?
分组TOP N，例如按id分组后，查询排名前3的分数:
SELECT id, val, dense_rank FROM ( SELECT id, val, dense_rank FROM ( SELECT id, groupArray(val) AS arr_val, arrayEnumerateDense(arr_val) AS dense_rank FROM ( SELECT DISTINCT val, id FROM test_data ORDER BY val DESC ) GROUP BY id ) ARRAY JOIN arr_val AS val, dense_rank ORDER BY id ASC, dense_rank ASC )WHERE dense_rank <= 3 ┌─id─┬─val─┬─dense_rank─┐ │ A │ 90 │ 1 │ │ A │ 80 │ 2 │ │ A │ 70 │ 3 │ │ B │ 99 │ 1 │ │ B │ 88 │ 2 │ │ B │ 78 │ 3 │ └────┴─────┴────────────┘ 6 rows in set. Elapsed: 0.008 sec.
由于分数val存在重复数据，此处使用了DISTINCT去重
指定id的分数排名，查询 id = A，val = 70的排名:
SELECT id, val, dense_rankFROM ( SELECT id, val, dense_rank FROM ( SELECT id, groupArray(val) AS arr_val, arrayEnumerateDense(arr_val) AS dense_rank FROM ( SELECT DISTINCT val, id FROM test_data ORDER BY val DESC ) GROUP BY id ) ARRAY JOIN arr_val AS val, dense_rank ORDER BY id ASC, dense_rank ASC )WHERE id = 'A' AND val = '70' ┌─id─┬─val─┬─dense_rank─┐ │ A │ 70 │ 3 │ └────┴─────┴────────────┘ 1 rows in set. Elapsed: 0.006 sec.
