【转】如何在ClickHouse中实现RANK OVER排序 ("开窗函数")

原文链接：ClickHouse的秘密基地（chcave），作者：凯朱

 

如何在ClickHouse中实现ROW_NUMBER OVER 和DENSE_RANK OVER等同效果的查询，它们在一些其他数据库中可用于RANK排序。

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

arrayEnumeratearrayEnumerateDensearrayEnumerateUniq

 

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

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_rankSELECT  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 ASWITH( 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_dataSELECT *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 idFROM (  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_rankFROM (  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_rankFROM (  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_rankORDER 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_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 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.

 

 

原文转载：http://www.shaoqun.com/a/521178.html

跨境电商：https://www.ikjzd.com/

败欧洲网站：https://www.ikjzd.com/w/1555

白色清关：https://www.ikjzd.com/w/1410

原文链接：ClickHouse的秘密基地（chcave），作者：凯朱如何在ClickHouse中实现ROW_NUMBEROVER和DENSE_RANKOVER等同效果的查询，它们在一些其他数据库中可用于RANK排序。同样的，CH中并没有直接提供对应的开窗函数，需要利用一些特殊函数变相实现，主要会用到下面几个数组函数，它们分别是:arrayEnumeratearrayEnumerateDensear
feedly：feedly
四海商舟：四海商舟
大众产品和独特产品，怎么做亚马逊广告？：大众产品和独特产品，怎么做亚马逊广告？
"白色星期五"VS"黄色星期五"，中东电商Souq和Noon促销大比拼！："白色星期五"VS"黄色星期五"，中东电商Souq和Noon促销大比拼！
2019卖家要关注的几个新零售趋势：消费者更青睐多渠道购物方式和....：2019卖家要关注的几个新零售趋势：消费者更青睐多渠道购物方式和....