WINDOW Clause#

Syntax#

window_clause:
  WINDOW named_window_expression [, ...]

named_window_expression:
  named_window AS { named_window | ( window_specification ) }

window_specification:
  [ UNION ( from_item [, ...] ) ]
  PARTITION BY expression [ ORDER BY ordering_expression ]
  window_frame_clause [ window_attr [, ...] ]

window_frame_clause:
  frame_units BETWEEN frame_bound AND frame_bound [ MAXSIZE numeric_expression ] )

frame_unit:
  ROWS 
  | ROWS_RANGE

frame_boud:
  { UNBOUNDED | numeric_expression | interval_expression } [ OPEN ] PRECEDING
  | CURRENT ROW

window_attr:
  EXCLUDE CURRENT_TIME
  | EXCLUDE CURRENT_ROW
  | INSTANCE_NOT_IN_WINDOW

窗口调用函数实现了类似于聚合函数的功能。不同的是，窗口调用函数不需要将查询结果打包成一行输出—在查询输出中，每一行都是分开的。然而，窗口调用函数可以扫描所有的行，根据窗口调用函数的分组规范(PARTITION BY列)，这些行可能会是当前行所在组的一部分。一个窗口调用函数的语法是下列之一：

function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ([expression [, expression ... ]]) OVER window_name
function_name ( * ) OVER ( window_definition )
function_name ( * ) OVER window_name

SQL语句模版#

ROWS WINDOW(条数窗口) SQL模版

SELECT select_expr [, select_expr ...], window_function_name(expr) OVER window_name, ... FROM ... WINDOW AS window_name (PARTITION BY ... ORDER BY ... ROWS BETWEEN ... AND ...)

ROWS RANGE WINDOW(时间窗口) SQL模版

SELECT select_expr [,select_expr...], window_function_name(expr) OVER window_name, ... FROM ... WINDOW AS window_name (PARTITION BY ... ORDER BY ... ROWS_RANEG BETWEEN ... AND ...)

快速上手#

首先选择窗口类型，按时间，还是按条数划分窗口。

再看窗口想要什么大小，这里要分窗口类型说明：

时间窗口：时间窗口通常使用s, m, h, d等时间单位，如果没有单位，默认为ms。比如：
- [3小时前，当前行] - 3h preceding and current row
- [3小时前，30分钟前] - 3h preceding and 30m preceding
条数窗口：条数不需要单位。比如：
- [10条，当前行] - 10 preceding and current row
- [10条，3条] - 10 preceding and 3 preceding

如何推断窗口是什么样的？#

首先，先明确是什么执行模式：

离线模式或在线预览模式，合称为批模式，它是对from表的每一行都做一次窗口划分与计算。因此，每一行对应产生一行SQL结果。请求模式，会带一条请求行，它会将请求行当做from表的数据，只对该行做窗口划分和计算，因此，只产生一行SQL结果。注意，不会将请求行插入到表中。

我们将批模式看作多次请求模式来看待，所以请求模式查询如何划分窗口，我们分为三段来讲：

对一次请求行来说，窗口只可能包含，它自己，与它的partition by列值相等的行
partition key相等的所有行，它们不是乱序，而是按order by列排序
根据rows/rows_range排除窗口范围以外的数据
- rows：例如，10 preceding and current row的条数窗口，就要抛弃10行以外的数据行（第10行包含在窗口内），又因为包括current row，于是窗口一共有11行数据。 -rows_range：例如，10s preceding and current row的时间窗口，就要抛弃10s以外的数据行（第10s包含在窗口内），也包括current row，于是窗口只会出现order key值在[current_row_order_key - 10s, current_row_order_key]范围内的数据行。

Note

窗口划分范围，仅与order by列相关。如果认为窗口内行数或具体某数据不符合预期范围，一般是窗口写法的误解，极小概率是SQL引擎计算有误。请以某一个partition key为例，分步检查表的数据（以下操作都是在线模式）：

提取与该key相等的所有数据。可以使用select * from table where partition_key = xxx来提取，或使用源数据文件，通过pandas/spark等工具提取。
再按order by列排序，这类似于window设置窗口为unbounded preceding and current row。此处，可以将手动处理的数据和OpenMLDB的unbounded window计算结果进行对比。
- 由于OpenMLDB只支持在窗口内聚合，很难看到窗口的数据全貌，而且窗口内数据较多时，查看全部也是很难的。通常是使用count/min/max/lag等聚合函数来衡量窗口内数据的数量和范围。
- 如果仍需要通过窗口内具体数据来确认，可以使用top来展示前k大的值，但它会对列进行再排序，不能等同于窗口排序（order by列排序）。其他聚合函数，参考udf函数。
最后，再检查窗口的rows/rows_range设置是否符合预期。
- 通常情况，如果前两步没问题，条数划分一般不会有问题。
- 时间划分，需要注意时间单位。OpenMLDB中order by列无论是timestamp还是bigint，都当作整数来计算的，timestamp是转换为ms为单位的整数。我们支持在窗口设置中使用时间单位，但不会对表中的order by列值做任何单位假设。例如，如果order by列并非timestamp，而是设置整数20230905，在时间窗口设置5ms时，窗口的范围是[20230905 - 5, 20230905]，而不是[20230905 00:00:00 - 5ms, 20230905]。请谨慎对待order by列，最方便的做法是，任何时间格式都将其转换为timestamp或ms为单位的bigint。

preceding为闭区间，包含该条，开区间需使用open preceding

窗口还可以exclude current time，current row等，详情见下文。

边界说明#

SELECT语句元素	离线模式	在线预览模式	在线请求模式	说明
WINDOW Clause	`✓`	`✓(0.6.4 或者更高版本)`	`✓`	窗口子句用于定义一个或者若干个窗口。窗口可以是有名或者匿名的。用户可以在窗口上调用聚合函数来进行一些分析型计算的操作（`sql agg_func() over window_name`)。在线请求模式下，需要遵循Online Request下Window的使用规范

基本的 WindowSpec 语法元素#

WINDOW PARTITION BY clause 和 WINDOW ORDER BY clause#

WindowPartitionClause
        ::= ( 'PARTITION' 'BY' ByList )

WindowOrderByClause
        ::= ( 'ORDER' 'BY' ByList )            

PARTITION BY选项将查询的行分为一组进入partitions，这些行在窗口函数中单独处理。PARTITION BY和查询级别GROUP BY 子句做相似的工作, 只是它只能作为表达式不能作为查询结果的输出列或输出列 ID。OpenMLDB要求必须配置PARTITION BY。PARTITION BY list 可以有多个, 但仅支持按列分组，无法支持按运算或函数表达式分组。

ORDER BY 选项决定分区中的行被窗口函数处理的顺序。它和查询级别ORDER BY子句做相似的工作，同样不能作为查询结果的输出列或者输出列 ID。OpenMLDB 目前仅支持按列排序，ORDER BY list 有且只能有一个, 不支持按运算或函数表达式排序。OpenMLDB 0.8.4 以后, 在线模式下 ORDER BY 子句可以不写 (离线模式暂时不支持), 表示窗口内的列将以不确定的顺序处理, 不带 ORDER BY 子句的窗口需要额外满足如下条件:

不能有EXCLUDE CURRENT_TIME
对于 ROWS 类型窗口没有更多限制, 对于 ROWS_RANGE 类型窗口:
1. 窗口 FRAME 的边界不能是 offset [OPEN] PRECEDING/FOLLOWING 的格式, 目前情况只能为 UNBOUNDED PRECEDING AND CURRENT ROW

Note

窗口不带 ORDER BY 的情况, 意味着对于在线预览模式, 计算结果是不确定的, 无法预测哪些行进去了窗口哪些行没有. 同时对于一些通用窗口函数, 例如 lag, first_value, 在所有模式下得到的计算结果都是不确定的，无法预测窗口内行的先后顺序.

Window Frame Clause#

WindowFrameClause
        ::= ( WindowFrameUnits WindowFrameBounds [WindowFrameMaxSize] )

WindowFrameUnits
        ::= 'ROWS'
          | 'ROWS_RANGE'

WindowFrameBounds
        ::= 'BETWEEN' WindowFrameBound 'AND' WindowFrameBound

WindowFrameBound
        ::= ( 'UNBOUNDED' | NumLiteral | IntervalLiteral ) ['OPEN'] 'PRECEDING'
          | 'CURRENT' 'ROW'

共三部分：

WindowFrameUnits: 窗口类型
WindowFrameBounds: 窗口边界
WindowFrameMaxSize (Optional) 窗口最大行数, 见 Window With MAXSIZE

Window Frame Units#

WindowFrameUnits定义了窗口的框架类型。OpenMLDB支持两类窗口框架：ROWS和ROWS_RANGE。

SQL标准的RANGE类窗口OpenMLDB系统目前暂不支持。他们直接的对比差异如下图所示

Figure 1: window frame type

ROWS: 窗口按行划入窗口，根据条数滑出窗口
ROWS_RANGE：窗口按行划入窗口，根据时间区间滑出窗口
RANGE: 窗口按时间粒度划入窗口（一次可能滑入多条同一时刻的数据行），按时间区间滑出窗口

Window Frame Bounds#

WindowFrameBounds定义了窗口的上界和下界, 用 BETWEEN .. AND .. 的方式串联:

CURRENT ROW: 表示当前行
UNBOUNDED PRECEDING: 表示无限制上界
expr PRECEDING
- 窗口类型为ROWS时，expr必须为一个非负整数。它表示边界为当前行往前expr行。
- 窗口类型为ROWS_RANGE时,expr 可以为时间区间（例如10s, 10m,10h, 10d)，它表示边界为当前行往前移expr时间段（例如，10秒，10分钟，10小时，10天）
  - 也可以写成正整数，单位为 ms, 例如 1000 等价于 1s
OpenMLDB支持默认边界是闭合的。但支持OPEN关键字来修饰边界开区间
请注意：标准SQL中，还支持FOLLOWING的边界，当OpenMLDB并不支持。

Examples#

有名窗口（Named Window）

SELECT sum(col2) OVER w1 as w1_col2_sum FROM t1
WINDOW w1 AS (PARTITION BY col1 ORDER BY col5 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

匿名窗口

SELECT id, pk1, col1, std_ts,
sum(col1) OVER (PARTITION BY pk1 ORDER BY std_ts ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as w1_col1_sum
from t1;

ROWS 类型窗口

定义一个ROWS 类型窗口，窗口范围是前1000行到当前行。

SELECT sum(col2) OVER w1 as w1_col2_sum FROM t1
WINDOW w1 AS (PARTITION BY col1 ORDER BY col5 ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW);

ROWS_RANGE 类型窗口

定义一个ROWS_RANGE类型窗口，窗口范围是当前行前10s的所有行，以及当前行。

SELECT sum(col2) OVER w1 as w1_col2_sum FROM t1
WINDOW w1 AS (PARTITION BY col1 ORDER BY col5 ROWS_RANGE BETWEEN 10s PRECEDING AND CURRENT ROW);

OpenMLDB特有的 WindowSpec 元素#

1. WINDOW … UNION#

WindowUnionClause
        :: = ( 'UNION' TableRefs)

Example#

基于一张副表的 WINDOW … UNION

SELECT col1, col5, sum(col2) OVER w1 as w1_col2_sum FROM t1
WINDOW w1 AS (UNION t2 PARTITION BY col1 ORDER BY col5 ROWS_RANGE BETWEEN 10s PRECEDING AND CURRENT ROW);

Figure 2: window union one table

基于多张副表的 WINDOW … UNION

SELECT col1, col5, sum(col2) OVER w1 as w1_col2_sum FROM t1
WINDOW w1 AS (UNION t2, t3 PARTITION BY col1 ORDER BY col5 ROWS_RANGE BETWEEN 10s PRECEDING AND CURRENT ROW);

Figure 3: window union two tables

带有 INSTANCE_NOT_IN_WINDOW 的 WINDOW … UNION

使用 INSTANCE_NOT_IN_WINDOW 修饰 window, 样本表除当前行外其他行不进入窗口计算。

SELECT col1, col5, sum(col2) OVER w1 as w1_col2_sum FROM t1
WINDOW w1 AS (UNION t2 PARTITION BY col1 ORDER BY col5 ROWS_RANGE BETWEEN 10s PRECEDING AND CURRENT ROW INSTANCE_NOT_IN_WINDOW);

Figure 4: window union one table with instance_not_in_window

带有列筛选子查询的 WINDOW … UNION

SELECT col1, col5, sum(col2) OVER w1 as w1_col2_sum FROM t1
WINDOW w1 AS
(UNION (select c1 as col1, c2 as col2, 0.0 as col3, 0.0 as col4, c5 as col5, "NA" as col6 from t2),
(select c1 as col1, c2 as col2, 0.0 as col3, 0.0 as col4, c5 as col5, "NA" as col6 from t3)
PARTITION BY col1 ORDER BY col5 ROWS_RANGE BETWEEN 10s PRECEDING AND CURRENT ROW);

2. WINDOW with EXCLUDE CURRENT_TIME#

窗口计算时除当前行外其他与当前行的 ts 列值相同的行不进入窗口计算。

WindowExcludeCurrentTime 
        ::= 'EXCLUDE' 'CURRENT_TIME'  

Example#

ROWS 类型窗口，带有 EXCLUDE CURRENT_TIME

定义一个ROWS 类型窗口，窗口范围是前1000行到当前行。除了当前行以外窗口内不包含当前时刻的其他数据。

SELECT sum(col2) OVER w1 as w1_col2_sum FROM t1
WINDOW w1 AS (PARTITION BY col1 ORDER BY col5 ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW EXCLUDE CURRENT_TIME);

ROWS_RANGE 类型窗口，带有 EXCLUDE CURRENT_TIME

定义一个ROWS_RANGE 类型窗口，窗口范围是当前行前10s的所有行，以及当前行。除了当前行以外窗口内不包含当前时刻的其他数据。

SELECT sum(col2) OVER w1 as w1_col2_sum FROM t1
WINDOW w1 AS (PARTITION BY col1 ORDER BY col5 ROWS_RANGE BETWEEN 10s PRECEDING AND CURRENT ROW EXCLUDE CURRENT_TIME);

Figure 5: window exclude current time

3. WINDOW with EXCLUDE CURRENT_ROW#

当前行不进入窗口计算。

WindowExcludeCurrentRow
        ::= 'EXCLUDE' 'CURRENT_ROW'

Example#

ROWS_RANGE 类型窗口，带有 EXCLUDE CURRENT_ROW

SELECT sum(col2) OVER w1 as w1_col2_sum FROM t1
WINDOW w1 AS (PARTITION BY col1 ORDER BY col5 ROWS_RANGE BETWEEN 10s PRECEDING AND CURRENT ROW EXCLUDE CURRENT_ROW);

Figure 6: window exclude current time

4. WINDOW with MAXSIZE#

OpenMLDB定义了MAXSIZE关键字，来限制有效窗口内最大数据条数。

MaxSize 属性仅支持 ROWS_RANGE 类型窗口。

WindowFrameMaxSize
        :: = MAXSIZE NumLiteral

Figure 7: window config max size

Example#

ROWS_RANGE 类型窗口，带有 MAXSIZE 限制

定义一个 ROWS_RANGE 类型窗口，窗口范围是当前行前10s的所有行，以及当前行。同时限制窗口内数据条数不超过3条。

SELECT sum(col2) OVER w1 as w1_col2_sum FROM t1
WINDOW w1 AS (PARTITION BY col1 ORDER BY col5 ROWS_RANGE BETWEEN 10s PRECEDING AND CURRENT ROW MAXSIZE 3);

WINDOW Clause

Contents

WINDOW Clause#

Syntax#

SQL语句模版#

快速上手#

如何推断窗口是什么样的？#

边界说明#

基本的 WindowSpec 语法元素#

WINDOW PARTITION BY clause 和 WINDOW ORDER BY clause#

Window Frame Clause#

Window Frame Units#

Window Frame Bounds#

Examples#

OpenMLDB特有的 WindowSpec 元素#

1. WINDOW … UNION#

Example#

2. WINDOW with EXCLUDE CURRENT_TIME#

Example#

3. WINDOW with EXCLUDE CURRENT_ROW#

Example#

4. WINDOW with MAXSIZE#

Example#