正宗的ClickHouse开窗函数来袭("开窗函数")
正宗的ClickHouse开窗函数来袭("开窗函数")
由于公众号不再按时间线推送，如果不想错过精彩内容，请在关注公众号后，点击右上角 ... 设为星标，感谢支持。
在今年2月6号线上举行的 ClickHouse China Spring Meetup 中，朵夫为我们带来了 ClickHouse Features 2021 的分享，其中有非常多强大的新特性，幻灯片的下载地址如下:
https://presentations.clickhouse.tech/meetup50/new_features/
在众多的新特性中，我对开窗函数、自定义UDF、ZooKeeper优化等几项特别感兴趣，后续我也打算分别用几篇文章来展开说明。
今天主要想聊一下在分享中提到的 ClickHouse 原生的开窗函数，在此之前，我曾经专门写过两篇文章介绍如何在 CH 中变相实现开窗函数的功能，传送门如下：
现在 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.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.可以看到，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
本文分享自 ClickHouse的秘密基地 微信公众号，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体分享计划 ，欢迎热爱写作的你一起参与！