我是靠谱客的博主 单薄心锁,这篇文章主要介绍ClickHouse开窗函数已上线(转载),现在分享给大家,希望可以做个参考。

ClickHouse开窗函数已上线 转载

    • 1.如何在ClickHouse中实现RANK OVER排序
    • 2.使用ClickHouse快速实现同比、环比分析

Window Functions 在clickhouse的需求和呼声很高,早期的版本需要借助array函数,在21.1版本进行了开窗函数的初步支持。

在今年2月6号线上举行的 ClickHouse China Spring Meetup 中,朵夫为我们带来了 ClickHouse Features 2021 的分享,其中有非常多强大的新特性,幻灯片的下载地址如下:
https://presentations.clickhouse.tech/meetup50/new_features/

1.如何在ClickHouse中实现RANK OVER排序

首先准备测试表:

复制代码
1
2
3
4
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 dict SELECT dict[number%10+1] AS id, dict[number+11] AS val FROM system.numbers LIMIT 10

在此之前,如果要实现 row_number 和 dense_rank 的分组查询,需要借助arrayEnumerate 和 arrayEnumerateDense 这类数组函数,代码量巨大且嵌套复杂:

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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

而在新版本中( 21.3.1 ),实现相同的功能只需要下面这样:

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT id, val, rank() OVER w AS rank, dense_rank() OVER w AS dense_rank, row_number() OVER w AS row_number, count(*) OVER w AS count, sum(toInt32(val)) OVER w AS sum_v, avg(toInt32(val)) OVER w AS avg_v, max(toInt32(val)) OVER w AS max_v FROM test_data WINDOW w AS (PARTITION BY id ORDER BY val ASC range unbounded preceding) ORDER BY id ASC SETTINGS allow_experimental_window_functions = 1 ┌─id─┬─val─┬─rank─┬─dense_rank─┬─row_number─┬─count─┬─sum_v─┬─────────────avg_v─┬─max_v─┐ │ A │ 591111595959 │ │ A │ 70222212964.570 │ │ A │ 80333428972.2580 │ │ A │ 80334428972.2580 │ │ A │ 90545537975.890 │ │ B │ 651111656565 │ │ B │ 7522221407075 │ │ B │ 78333321872.6666666666666778 │ │ B │ 88444430676.588 │ │ B │ 9955554058199 │ └────┴─────┴──────┴────────────┴────────────┴───────┴───────┴───────────────────┴───────┘ 10 rows in set. Elapsed: 0.003 sec.

可以看到,ClickHouse 现在支持了原生的:

分析函数 rank()、dense_rank()、row_number()
开窗函数 over(),且开窗函数也支持分组子句 partition by、排序子句 order by 和窗口子句 range/row

由于默认窗口子句是 range ,所以下面的写法是等价的:

复制代码
1
2
3
4
PARTITION BY id ORDER BY val ASC range unbounded precedingPARTITION BY id ORDER BY val ASC

2.使用ClickHouse快速实现同比、环比分析

接着我们再来看一看同比/环比功能,现在可以如何实现。

在此之前,实现同比/环比需要借助 neighbor 函数实现:

复制代码
1
2
3
4
5
6
7
8
WITH toDate('2019-01-01') AS start_date SELECT toStartOfMonth(start_date + (number * 32)) AS date_time, (number + 1) * 100 AS money, neighbor(money, -12) AS prev_year, neighbor(money, -1) AS prev_month FROM numbers(16)

在新的版本中,虽然目前也还未实现 lead/lag 函数,但通过开窗函数的窗口子句就能变相实现该功能:

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SELECT date_time, money, any(money) OVER (ORDER BY money ASC ROWS BETWEEN 12 PRECEDING AND 12 PRECEDING) AS prev_year, any(money) OVER (ORDER BY money ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_month FROM ( WITH toDate('2019-01-01') AS start_date SELECT toStartOfMonth(start_date + (number * 32)) AS date_time, (number + 1) * 100 AS money FROM numbers(16) ) SETTINGS allow_experimental_window_functions = 1 ┌──date_time─┬─money─┬─prev_year─┬─prev_month─┐ │ 2019-01-0110000 │ │ 2019-02-012000100 │ │ 2019-03-013000200 │ │ 2019-04-014000300 │ │ 2019-05-015000400 │ │ 2019-06-016000500 │ │ 2019-07-017000600 │ │ 2019-08-018000700 │ │ 2019-09-019000800 │ │ 2019-10-0110000900 │ │ 2019-11-01110001000 │ │ 2019-12-01120001100 │ │ 2020-01-0113001001200 │ │ 2020-02-0114002001300 │ │ 2020-03-0115003001400 │ │ 2020-04-0116004001500 │ └────────────┴───────┴───────────┴────────────┘ 如上所示,这里是利用窗口子句,将 range 换成 row ,通过如下的句式实现: any(value) over (.... rows between <offset> preceding and <offset> preceding), or following

这么使用下来,ClickHouse 开窗函数的语法和其他数据库中的用法基本无异,果然 CH 又变强大了呢 。

好了今天的分享就到这里吧,开窗函数目前完整的官方描述参见下面的地址:

https://github.com/ClickHouse/ClickHouse/blob/master/docs/en/sql-reference/window-functions/index.md#experimental-window-function

转载自:https://my.oschina.net/u/4579603/blog/4970693

最后

以上就是单薄心锁最近收集整理的关于ClickHouse开窗函数已上线(转载)的全部内容,更多相关ClickHouse开窗函数已上线(转载)内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
点赞(59)

评论列表共有 0 条评论

立即
投稿
返回
顶部