ClickHouse开窗函数已上线(转载)

89 阅读 0 评论 59 点赞

我是靠谱客的博主单薄心锁，这篇文章主要介绍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 )，实现相同的功能只需要下面这样:

复制代码

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  │ 59  │    1 │          1 │          1 │     1 │    59 │                59 │    59 │
│ A  │ 70  │    2 │          2 │          2 │     2 │   129 │              64.5 │    70 │
│ A  │ 80  │    3 │          3 │          3 │     4 │   289 │             72.25 │    80 │
│ A  │ 80  │    3 │          3 │          4 │     4 │   289 │             72.25 │    80 │
│ A  │ 90  │    5 │          4 │          5 │     5 │   379 │              75.8 │    90 │
│ B  │ 65  │    1 │          1 │          1 │     1 │    65 │                65 │    65 │
│ B  │ 75  │    2 │          2 │          2 │     2 │   140 │                70 │    75 │
│ B  │ 78  │    3 │          3 │          3 │     3 │   218 │ 72.66666666666667 │    78 │
│ B  │ 88  │    4 │          4 │          4 │     4 │   306 │              76.5 │    88 │
│ B  │ 99  │    5 │          5 │          5 │     5 │   405 │                81 │    99 │
└────┴─────┴──────┴────────────┴────────────┴───────┴───────┴───────────────────┴───────┘
10 rows in set. Elapsed: 0.003 sec.

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  │ 59  │    1 │          1 │          1 │     1 │    59 │                59 │    59 │
│ A  │ 70  │    2 │          2 │          2 │     2 │   129 │              64.5 │    70 │
│ A  │ 80  │    3 │          3 │          3 │     4 │   289 │             72.25 │    80 │
│ A  │ 80  │    3 │          3 │          4 │     4 │   289 │             72.25 │    80 │
│ A  │ 90  │    5 │          4 │          5 │     5 │   379 │              75.8 │    90 │
│ B  │ 65  │    1 │          1 │          1 │     1 │    65 │                65 │    65 │
│ B  │ 75  │    2 │          2 │          2 │     2 │   140 │                70 │    75 │
│ B  │ 78  │    3 │          3 │          3 │     3 │   218 │ 72.66666666666667 │    78 │
│ B  │ 88  │    4 │          4 │          4 │     4 │   306 │              76.5 │    88 │
│ B  │ 99  │    5 │          5 │          5 │     5 │   405 │                81 │    99 │
└────┴─────┴──────┴────────────┴────────────┴───────┴───────┴───────────────────┴───────┘
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 preceding
和
PARTITION 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 函数，但通过开窗函数的窗口子句就能变相实现该功能:

复制代码

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-01 │   100 │         0 │          0 │
│ 2019-02-01 │   200 │         0 │        100 │
│ 2019-03-01 │   300 │         0 │        200 │
│ 2019-04-01 │   400 │         0 │        300 │
│ 2019-05-01 │   500 │         0 │        400 │
│ 2019-06-01 │   600 │         0 │        500 │
│ 2019-07-01 │   700 │         0 │        600 │
│ 2019-08-01 │   800 │         0 │        700 │
│ 2019-09-01 │   900 │         0 │        800 │
│ 2019-10-01 │  1000 │         0 │        900 │
│ 2019-11-01 │  1100 │         0 │       1000 │
│ 2019-12-01 │  1200 │         0 │       1100 │
│ 2020-01-01 │  1300 │       100 │       1200 │
│ 2020-02-01 │  1400 │       200 │       1300 │
│ 2020-03-01 │  1500 │       300 │       1400 │
│ 2020-04-01 │  1600 │       400 │       1500 │
└────────────┴───────┴───────────┴────────────┘
如上所示，这里是利用窗口子句，将 range 换成 row ，通过如下的句式实现：

any(value) over (.... rows between <offset> preceding and <offset> preceding), or following

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-01 │   100 │         0 │          0 │
│ 2019-02-01 │   200 │         0 │        100 │
│ 2019-03-01 │   300 │         0 │        200 │
│ 2019-04-01 │   400 │         0 │        300 │
│ 2019-05-01 │   500 │         0 │        400 │
│ 2019-06-01 │   600 │         0 │        500 │
│ 2019-07-01 │   700 │         0 │        600 │
│ 2019-08-01 │   800 │         0 │        700 │
│ 2019-09-01 │   900 │         0 │        800 │
│ 2019-10-01 │  1000 │         0 │        900 │
│ 2019-11-01 │  1100 │         0 │       1000 │
│ 2019-12-01 │  1200 │         0 │       1100 │
│ 2020-01-01 │  1300 │       100 │       1200 │
│ 2020-02-01 │  1400 │       200 │       1300 │
│ 2020-03-01 │  1500 │       300 │       1400 │
│ 2020-04-01 │  1600 │       400 │       1500 │
└────────────┴───────┴───────────┴────────────┘
如上所示，这里是利用窗口子句，将 range 换成 row ，通过如下的句式实现：

any(value) over (.... rows between <offset> preceding and <offset> preceding), or following