专栏首页ClickHouse的秘密基地如何在ClickHouse中实现RANK OVER排序 ("开窗函数")

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

如何在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.

本文分享自微信公众号 - ClickHouse的秘密基地(chcave),作者:凯朱

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-04-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 正宗的ClickHouse开窗函数来袭("开窗函数")

    由于公众号不再按时间线推送,如果不想错过精彩内容,请在关注公众号后,点击右上角 ... 设为星标,感谢支持。

    Nauu
  • SQL进阶-3-排序与窗口函数

    在使用数据库制作各种统计数据的时候,需要对数据进行排序,比如按照分数、销量、人数等数值进行排序,通常排序的方法有两种:

    皮大大
  • 关于SparkSQL的开窗函数,你应该知道这些!

    相信用过MySQL的朋友都知道,MySQL中也有开窗函数的存在。开窗函数的引入是为了既显示聚集前的数据,又显示聚集后的数据。即在每一行的最后一列添加聚合函数的结...

    王知无-import_bigdata
  • postgreSQL窗口函数总结

    1、我们都知道在SQL中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少...

    小徐
  • postgreSQL窗口函数总结

    1、我们都知道在SQL中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少...

    小徐
  • 2021年大数据Spark(二十九):SparkSQL案例四开窗函数

    https://www.cnblogs.com/qiuting/p/7880500.html

    Lanson
  • spark、hive中窗口函数实现原理复盘

    这篇文章从一次业务中遇到的问题出发,深入聊了聊hsql中窗口函数的数据流转原理,在文章最后针对这个问题给出解决方案。

    数据仓库践行者
  • MySQL 窗口函数之 RANK 函数

    Mysql8.0+ 版本支持窗口函数,该类函数也称为分析函数,对于初学者来说,窗口函数特别容易与分组聚合函数混合。两者的不同之处在于,窗口函数的使用,使得每一行...

    DataScience
  • PostgreSQL>窗口函数的用法

    转载请注明出处:https://www.cnblogs.com/funnyzpc/p/9311281.html

    上帝
  • Hive常用窗口函数实战

    本文介绍了Hive常见的序列函数,排名函数和窗口函数。结合业务场景展示了Hive分析函数的使用

    Eights
  • SQL中几个常用的排序函数

    最近使用窗口函数的频率越来越高,这里打算简单介绍一下几个排序的函数,做一个引子希望以后这方面的问题能够更深入的理解,这里先简单介绍一下几个简单的排序函数及其相...

    用户1217611
  • MySQL——开窗函数

    高级开窗函数/ 排名的实现ROW_NUMBER();rank() ,dense_rank()

    全栈程序员站长
  • 字节跳动面试题:你的平均薪水是多少?

    要求每个部门除去最高、最低薪水后的的平均薪水,所以应该查询出每个部门的最高、最低工资。

    猴子数据分析
  • 2021年数据科学家面试:4个基本SQL窗口函数介绍以及示例

    墨墨导读:在数据科学家岗位的面试中,窗口函数(WINDOW function)是SQL函数家族中经常会被问到的主题。在本文中,我会根据面试的问题,问题模式和解决...

    数据和云
  • SQL干货 | 窗口函数的使用

    Mysql从8.0版本开始,也和Sql Server、Oracle一样支持在查询中使用窗口函数,本文将根据官方文档,通过实例介绍窗口函数并举例分组排序函数的使用...

    Python数据科学
  • Hive的利器:强大而实用的开窗函数

    与聚合函数类似,开窗函数也是对行集组进行聚合计算。但是它不像普通聚合函数那样,每组通常只返回一个值,开窗函数可以为每组返回多个值,因为开窗函数所执行聚合计算的行...

    大数据学习与分享
  • 如何在spark里面使用窗口函数

    在大数据分析中,窗口函数最常见的应用场景就是对数据进行分组后,求组内数据topN的需求,如果没有窗口函数,实现这样一个需求还是比较复杂的,不过现在大多数标准SQ...

    我是攻城师
  • 举一反三-Pandas实现Hive中的窗口函数

    1、Hive窗口函数 我们先来介绍一下Hive中几个常见的窗口函数,row_number(),lag()和lead()。 row_number() 该函数的格式...

    石晓文
  • 配套版《MySQL窗口函数》基础篇,来啦!

    前段时间,黄同学写了一篇《MySQL窗口实战》文章(文章如下),但是里面大多数是以实战练习为主,没有做详细的解释。

    数据分析与统计学之美

扫码关注云+社区

领取腾讯云代金券