ClickHouse row_number()、开窗函数(rank()等)

正宗的ClickHouse开窗函数来袭

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

https://presentations.clickhouse.tech/meetup50/new_features/

在众多的新特性中，我对开窗函数、自定义UDF、ZooKeeper优化等几项特别感兴趣，后续我也打算分别用几篇文章来展开说明。

现在 ClickHouse 提供了正宗的实现，功能上使用起来真是比先前的奇技淫巧简单太多了。

首先准备测试表:

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 这类数组函数，代码量巨大且嵌套复杂：

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.4.25 )，实现相同的功能只需要下面这样:

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 ，所以下面的写法是等价的:

PARTITION BY  id ORDER BY  val ASC range unbounded preceding
和
PARTITION BY  id ORDER BY  val ASC

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

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

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

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

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

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

转自：https://cloud.tencent.com/developer/article/1799367?from=article.detail.1612576