最近更新时间:2023.05.30 11:57:22
首次发布时间:2022.12.15 10:08:27
窗口函数是指:查询的输入参数是从上一个查询结果的一行或多行的“窗口”中取出的。例如:对查询结果分组进行排名展示,对查询结果分组再次进行累加等。
窗口函数常在业务中用于同比环比分析,top n 的排序等。
原表为:
a | b | c | e |
---|---|---|---|
1 | 1 | 1 | 10 |
1 | 1 | 2 | 20 |
1 | 1 | 3 | 30 |
1 | 2 | 1 | 100 |
1 | 2 | 2 | 200 |
2 | 1 | 1 | 60 |
3 | 1 | 1 | 80 |
查询 SQL 为:
select a, b, c, sum(e), sum(sum(e)) over (partition by a,b order by a,b,c rows between 3 preceding and 3 following) as acc_sum from t0_local group by a,b,c order by a,b,c;
该 SQL 的返回如下:
a | b | c | sum(e) | acc_sum |
---|---|---|---|---|
1 | 1 | 1 | 10 | 10 |
1 | 1 | 2 | 20 | 30 |
1 | 1 | 3 | 30 | 60 |
1 | 2 | 1 | 100 | 100 |
1 | 2 | 2 | 200 | 300 |
2 | 1 | 1 | 60 | 60 |
3 | 1 | 1 | 80 | 80 |
即通过将结果开了(1,1),(1,2),(2,1),(3,1)四个窗口,并在acc_sum
列输出每个窗口内的累加值。
可以具体参考如下文档来了解 window function 的详细语法: https://sqlite.org/windowfunctions.html
在此章节,我们会简要介绍已经当前已经实现的语法。
典型的语法示例为:
select a, b, window_founction(c) over (partition by a,b order by a,b rows between n preceding and m following)
示例中的 window_founction()
指代可以被开窗的函数,包括 ClickHouse 全部的聚合函数(例如sum
, avg
, count
, min
, max等),以及 Navigation 函数,目前 Navigation 函数仅支持以下四种:rank
,row_number
,dense_rank
,percent_rank
。
row_number()
按照值排序时产生一个自增编号,不会重复
rank()
按照值排序时产生一个自增编号,值相等时会重复,会产生空位
dense_rank()
按照值排序时产生一个自增编号,值相等时会重复,不会产生空位
percent_rank()
按照值排序时产生一个百分比值,值等于 (rank() - 1)/(- 1)
OVER
的子句包含 partition by
, order by
与 rows
三个组成部分:
partition by
,指对数据进行窗口的分割。和 Group By 不同,不同窗口的数据并不会像 Group by
那样合并为一行,而只是为了标识在分割后进行对哪些组别的数据进行order by
和 row
操作。
order by
,在over
子句中,order by
只是用来决定窗口函数按照什么顺序进行计算,但对结果呈现的排列顺序没有影响。
rows ,语义即为以 row 为单位来计算 window frame 的范围。举例如下, rows between 3 preceding and 2 following
的意思就是说以当前行为参考,frame的范围包括了往前3行和往后2行。假如当前行为 n,那么frame的范围就是 [n-3, n+2]。rows
部分可以被省略。
我们支持对窗口的命名,举例如下:
SELECT a AS a, row_number() OVER win AS row_number, rank() OVER win AS rank, dense_rank() OVER win AS dense_rank, percent_rank() OVER win AS percent_rank FROM t2 WINDOW win AS (ORDER BY a);
在以上 SQL语句中,我们用 win
来代替了一个 window: OVER (ORDER BY a)
,此时,可以在多个输出列中使用相同的 window。
select a, b, c, d, e, sum(e), sum(sum(e)) over (partition by a,b order by a,b,c rows between d preceding and e following) as acc_sum from t0_local group by a,b,c order by a,b,c;
假如 d 和 e 都是整数类型,那么就可以用 d 和 e 的值来代表偏移量, 此偏移量是逐行使用的。
d | e | preceding | following |
---|---|---|---|
1 | 2 | 1 | 2 |
3 | 4 | 3 | 4 |
preceding | following | streaming |
---|---|---|
ignore | ignore | true |
Unbounded preceding | Current row | true |
Unbounded preceding | 常数,比如3, 5 | true |
Anything else | Anything else | false |