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

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

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

今天主要想聊一下在分享中提到的 ClickHouse 原生的开窗函数，在此之前，我曾经专门写过两篇文章介绍如何在 CH 中变相实现开窗函数的功能，传送门如下：

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

如何在ClickHouse中实现RANK OVER排序

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

这里我继续沿用先前文章的场景用例，对比看一看现在实现起来是多么的简便。

首先准备测试表:

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

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

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

而在新版本中(我使用的是 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_vFROM test_dataWINDOW  w AS (PARTITION BY  id ORDER BY  val ASC range unbounded preceding)ORDER BY id ASCSETTINGS allow_experimental_window_functions = 1

可以看到，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_dateSELECT     toStartOfMonth(start_date + (number * 32)) AS date_time,     (number + 1) * 100 AS money,     neighbor(money, -12) AS prev_year,     neighbor(money, -1) AS prev_monthFROM 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_monthFROM (    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

如上所示，这里是利用窗口子句，将 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