TDSQL Boundless 窗口函数使用说明

窗口函数也叫 OLAP 函数（Online Analytical Processing，联机分析处理），用于对分区内的数据进行实时分析与计算，是分析型数据库的核心能力之一。
只读分析实例中窗口函数的使用方法与 MySQL 8.0 基本一致
开窗语法如下：
SELECT
    <WindowFunction> OVER (PARTITION BY <expr_list> ORDER BY <expr_list> [ASC | DESC] [<WindowFrame>])
FROM
    tb_test_window;
SQL 示例：
SELECT studentid, departmentid, classid, math,
       ROW_NUMBER() OVER (PARTITION BY departmentid, classid ORDER BY math) AS row_num
FROM   student_scores;
该示例的语义为：在分组（departmentid、classid）内，按 math 列升序为每一行编号。
说明：
PARTITION BY 与 ORDER BY 的表达式必须是表的列引用，暂不支持任意表达式。
ORDER BY 子句暂不支持 NULLS FIRST 与 NULLS LAST，NULL 值默认作为最小值参与排序。
暂不支持 IGNORE NULLS、RESPECT NULLS、FROM FIRST 与 FROM LAST 修饰符。
WindowFrame
窗口框架（Window Frame）用于在窗口内进一步划定参与计算的行集合。分析引擎支持 ROWS 与 RANGE 两种框架类型，暂不支持 GROUPS 框架与 EXCLUDE 子句。
Frame
ROWS 场景
RANGE 场景
CURRENT ROW
当前行。
与当前行 ORDER BY 列值相同的所有行。
UNBOUNDED PRECEDING
到分区第一行。
到分区第一行。
UNBOUNDED FOLLOWING
到分区最后一行。
到分区最后一行。
 PRECEDING
当前行向前偏移 N 行。
到 ORDER BY 列值大于等于（当前行值 - N）对应的行。
 FOLLOWING
当前行向后偏移 N 行。
到 ORDER BY 列值小于等于（当前行值 + N）对应的行。
支持的框架语法：
ROWS | RANGE <Frame>。
ROWS | RANGE BETWEEN <Frame> AND <Frame>。
说明：
排序类窗口函数（ROW_NUMBER、RANK、DENSE_RANK）以及取值类的 LEAD、LAG 函数不受 <WindowFrame> 限制。
WindowFunction
分析引擎当前支持的窗口函数如下表所示：
函数名称
功能描述
函数参数
支持类型
ROW_NUMBER()
对每个分区内的数据按 ORDER BY 顺序标记行号。
-
-
RANK()
对每个分区内的数据进行非密集型排名（相同值同名次，下一名次跳号）。
-
-
DENSE_RANK()
对每个分区内的数据进行密集型排名（相同值同名次，下一名次连续）。
-
-
LEAD(, , )
返回当前行向后  行处  的值，越界时返回 。
[必选] ：计算列。
所有类型。
﻿
﻿
[可选] ：当前行向后偏移行数，缺省为 1。
整数类型（INT、BIGINT 等）。
﻿
﻿
[可选] ：越界时返回的默认值，缺省为 NULL。
类型需与  一致，或可被  列的类型容纳。
LAG(, , )
返回当前行向前  行处  的值，越界时返回 。
[必选] ：计算列。
所有类型。
﻿
﻿
[可选] ：当前行向前偏移行数，缺省为 1。
整数类型（INT、BIGINT 等）。
﻿
﻿
[可选] ：越界时返回的默认值，缺省为 NULL。
类型需与  一致，或可被  列的类型容纳。
FIRST_VALUE()
返回窗口框架内第一行  的值。
[必选] ：计算列。
所有类型。
LAST_VALUE()
返回窗口框架内最后一行  的值。
[必选] ：计算列。
所有类型。
MIN()
返回窗口框架内  的最小值。
[必选] ：计算列。
数值类型、字符类型、时间类型。
MAX()
返回窗口框架内  的最大值。
[必选] ：计算列。
数值类型、字符类型、时间类型。
COUNT()
返回窗口框架内  非 NULL 值的行数。
[必选] ：计算列。
所有类型。
SUM()
返回窗口框架内  的总和。
[必选] ：计算列。
数值类型。
AVG()
返回窗口框架内  的平均值。
[必选] ：计算列。
数值类型。
类型说明：
数值类型：INT、BIGINT、FLOAT、DOUBLE、DECIMAL。
字符类型：CHAR、VARCHAR。
时间类型：DATE、TIME、DATETIME、TIMESTAMP。
注意：
暂不支持 NTILE、PERCENT_RANK、CUME_DIST、NTH_VALUE 等窗口函数。
详细案例
下列案例使用统一的建表语句和数据：
DROP TABLE IF EXISTS test.tb_window;
CREATE TABLE test.tb_window (
    c1 INT NOT NULL PRIMARY KEY,
    c2 INT,
    c3 INT
);
INSERT INTO test.tb_window VALUES
    (1, 1, 1), (2, 1, 1), (3, 1, 2),
    (4, 1, 4), (5, 1, 6), (6, 1, 6);
说明：
窗口函数分析引擎上执行，请确认表已开启列存能力后再执行下列查询。
ROWS 关键字
说明：
该关键字按照行数统计窗口大小，并对窗口内数据进行计算。
-- 案例语句
tdsql> SELECT c2, c3,
              COUNT(c1) OVER (PARTITION BY c2 ORDER BY c3
                              ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS cn
       FROM test.tb_window;
+----+----+----+
﻿
| c2 | c3 | cn |
﻿
+----+----+----+
﻿
|  1 |  1 |  3 |  -- 窗口行索引范围：current -> 之后 2 行 [0 ~ 2]
|  1 |  1 |  3 |  -- 窗口行索引范围：current -> 之后 2 行 [1 ~ 3]
|  1 |  2 |  3 |  -- 窗口行索引范围：current -> 之后 2 行 [2 ~ 4]
|  1 |  4 |  3 |  -- 窗口行索引范围：current -> 之后 2 行 [3 ~ 5]
|  1 |  6 |  2 |  -- 窗口行索引范围：current -> 之后 1 行 [4 ~ 5]（之后只有一行）
|  1 |  6 |  1 |  -- 窗口行索引范围：current（之后没有数据）
﻿
+----+----+----+
6 rows in set (0.06 sec)
RANGE 关键字
说明：
该关键字按照 ORDER BY 列的值统计窗口范围，并对窗口内数据进行计算。例如下面的 SQL 案例是从当前行 ORDER BY 列的值开始，到该值加2之间的所有行作为一个窗口。
-- 案例语句
tdsql> SELECT c2, c3,
              COUNT(c1) OVER (PARTITION BY c2 ORDER BY c3
                              RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) AS cn
       FROM test.tb_window;
+----+----+----+
﻿
| c2 | c3 | cn |
﻿
+----+----+----+
﻿
|  1 |  1 |  3 |  -- 窗口范围：c3 in [1, 3] 对应的行 [0 ~ 2]
|  1 |  1 |  3 |
|  1 |  2 |  2 |  -- 窗口范围：c3 in [2, 4] 对应的行 [2 ~ 3]
|  1 |  4 |  3 |  -- 窗口范围：c3 in [4, 6] 对应的行 [3 ~ 5]
|  1 |  6 |  2 |  -- 窗口范围：c3 in [6, 8] 对应的行 [4 ~ 5]
|  1 |  6 |  2 |
﻿
+----+----+----+
6 rows in set (0.06 sec)
ROW_NUMBER
说明：
该函数对分区内数据按 ORDER BY 顺序进行编号，不受 <WindowFrame> 限制。
-- 案例语句
tdsql> SELECT c2, c3,
              ROW_NUMBER() OVER (PARTITION BY c2 ORDER BY c3) AS rn
       FROM test.tb_window;
+----+----+------+
﻿
| c2 | c3 | rn   |
﻿
+----+----+------+
﻿
|  1 |  1 |    1 |
|  1 |  1 |    2 |
|  1 |  2 |    3 |
|  1 |  4 |    4 |
|  1 |  6 |    5 |
|  1 |  6 |    6 |
﻿
+----+----+------+
6 rows in set (0.04 sec)
RANK 与 DENSE_RANK
RANK 函数：对分区内数据进行非密集型排名，不受 <WindowFrame> 限制。
DENSE_RANK 函数：对分区内数据进行密集型排名，不受 <WindowFrame> 限制。
-- 案例语句
SELECT c2, c3,
       RANK()       OVER (PARTITION BY c2 ORDER BY c3) AS rk,
       DENSE_RANK() OVER (PARTITION BY c2 ORDER BY c3) AS drk
FROM   test.tb_window;
+------+------+------+------+
﻿
| c2   | c3   | rk   | drk  |
﻿
+------+------+------+------+
﻿
|    1 |    1 |    1 |    1 |
|    1 |    1 |    1 |    1 |
|    1 |    2 |    3 |    2 |
|    1 |    4 |    4 |    3 |
|    1 |    6 |    5 |    4 |
|    1 |    6 |    5 |    4 |
﻿
+------+------+------+------+
6 rows in set (0.05 sec)
LEAD 与 LAG
一、单参数场景
LEAD(<expr>)：返回分区当前行后一行 <expr> 的值，越界时返回 NULL，不受 <WindowFrame> 限制。
LAG(<expr>)：返回分区当前行前一行 <expr> 的值，越界时返回 NULL，不受 <WindowFrame> 限制。
-- 案例语句
tdsql> SELECT c2, c3,
              LEAD(c3) OVER (PARTITION BY c2 ORDER BY c3) AS ld,
              LAG(c3)  OVER (PARTITION BY c2 ORDER BY c3) AS lg
       FROM test.tb_window;
+------+------+------+------+
﻿
| c2   | c3   | ld   | lg   |
﻿
+------+------+------+------+
﻿
|    1 |    1 |    1 | NULL |
|    1 |    1 |    2 |    1 |
|    1 |    2 |    4 |    1 |
|    1 |    4 |    6 |    2 |
|    1 |    6 |    6 |    4 |
|    1 |    6 | NULL |    6 |
﻿
+------+------+------+------+
6 rows in set (0.11 sec)
二、双参数场景
LEAD(<expr>, <offset>)：返回分区当前行向后 <offset> 行 <expr> 的值，越界时返回 NULL，不受 <WindowFrame> 限制。
LAG(<expr>, <offset>)：返回分区当前行向前 <offset> 行 <expr> 的值，越界时返回 NULL，不受 <WindowFrame> 限制。
-- 案例语句
tdsql> SELECT c2, c3,
              LEAD(c3, 2) OVER (PARTITION BY c2 ORDER BY c3) AS ld,
              LAG(c3, 2)  OVER (PARTITION BY c2 ORDER BY c3) AS lg
       FROM test.tb_window;
+------+------+------+------+
﻿
| c2   | c3   | ld   | lg   |
﻿
+------+------+------+------+
﻿
|    1 |    1 |    2 | NULL |
|    1 |    1 |    4 | NULL |
|    1 |    2 |    6 |    1 |
|    1 |    4 |    6 |    1 |
|    1 |    6 | NULL |    2 |
|    1 |    6 | NULL |    4 |
﻿
+------+------+------+------+
6 rows in set (0.07 sec)
三、三参数场景
LEAD(<expr>, <offset>, <default>)：返回分区当前行向后 <offset> 行 <expr> 的值，越界时返回 <default>，不受 <WindowFrame> 限制。
LAG(<expr>, <offset>, <default>)：返回分区当前行向前 <offset> 行 <expr> 的值，越界时返回 <default>，不受 <WindowFrame> 限制。
-- 案例语句
tdsql> SELECT c2, c3,
              LEAD(c3, 2, 1000) OVER (PARTITION BY c2 ORDER BY c3) AS ld,
              LAG(c3, 2, 1000)  OVER (PARTITION BY c2 ORDER BY c3) AS lg
       FROM test.tb_window;
+------+------+------+------+
﻿
| c2   | c3   | ld   | lg   |
﻿
+------+------+------+------+
﻿
|    1 |    1 |    2 | 1000 |
|    1 |    1 |    4 | 1000 |
|    1 |    2 |    6 |    1 |
|    1 |    4 |    6 |    1 |
|    1 |    6 | 1000 |    2 |
|    1 |    6 | 1000 |    4 |
﻿
+------+------+------+------+
6 rows in set (0.10 sec)
FIRST_VALUE 与 LAST_VALUE
FIRST_VALUE(<expr>)：返回窗口框架内第一行 <expr> 的值。
LAST_VALUE(<expr>)：返回窗口框架内最后一行 <expr> 的值。
说明：
当 ORDER BY 列存在重复值时，第一行和最后一行的归属可能受排序稳定性影响，建议在 ORDER BY 子句中追加唯一列以保证结果稳定。
-- 案例语句
tdsql> SELECT c2, c3,
              FIRST_VALUE(c3) OVER (PARTITION BY c2 ORDER BY c3
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fv,
              LAST_VALUE(c3)  OVER (PARTITION BY c2 ORDER BY c3
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
       FROM test.tb_window;
+------+------+------+------+
﻿
| c2   | c3   | fv   | lv   |
﻿
+------+------+------+------+
﻿
|    1 |    1 |    1 |    6 |
|    1 |    1 |    1 |    6 |
|    1 |    2 |    1 |    6 |
|    1 |    4 |    1 |    6 |
|    1 |    6 |    1 |    6 |
|    1 |    6 |    1 |    6 |
﻿
+------+------+------+------+
6 rows in set (0.07 sec)
MIN 与 MAX
MIN(<expr>)：返回窗口框架内 <expr> 的最小值。
MAX(<expr>)：返回窗口框架内 <expr> 的最大值。
-- 案例语句
tdsql> SELECT c2, c3,
              MIN(c3) OVER (PARTITION BY c2 ORDER BY c3
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mi,
              MAX(c3) OVER (PARTITION BY c2 ORDER BY c3
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ma
       FROM test.tb_window;
+------+------+------+------+
﻿
| c2   | c3   | mi   | ma   |
﻿
+------+------+------+------+
﻿
|    1 |    1 |    1 |    6 |
|    1 |    1 |    1 |    6 |
|    1 |    2 |    1 |    6 |
|    1 |    4 |    1 |    6 |
|    1 |    6 |    1 |    6 |
|    1 |    6 |    1 |    6 |
﻿
+------+------+------+------+
6 rows in set (0.07 sec)
COUNT
说明：
返回窗口框架内 <expr> 非 NULL 值的行数。
-- 案例语句
tdsql> SELECT c2, c3,
              COUNT(c3) OVER (PARTITION BY c2 ORDER BY c3
                              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS c
       FROM test.tb_window;
+------+------+----+
﻿
| c2   | c3   |  c |
﻿
+------+------+----+
﻿
|    1 |    1 |  6 |
|    1 |    1 |  6 |
|    1 |    2 |  6 |
|    1 |    4 |  6 |
|    1 |    6 |  6 |
|    1 |    6 |  6 |
﻿
+------+------+----+
6 rows in set (0.04 sec)
SUM
说明：
返回窗口框架内 <expr> 的总和。
-- 案例语句
tdsql> SELECT c2, c3,
              SUM(c3) OVER (PARTITION BY c2 ORDER BY c3
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS s
       FROM test.tb_window;
+------+------+------+
﻿
| c2   | c3   | s    |
﻿
+------+------+------+
﻿
|    1 |    1 |   20 |
|    1 |    1 |   20 |
|    1 |    2 |   20 |
|    1 |    4 |   20 |
|    1 |    6 |   20 |
|    1 |    6 |   20 |
﻿
+------+------+------+
6 rows in set (0.06 sec)
AVG
说明：
返回窗口框架内 <expr> 的平均值。
-- 案例语句
tdsql> SELECT c2, c3,
              AVG(c3) OVER (PARTITION BY c2 ORDER BY c3
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS a
       FROM test.tb_window;
+------+------+--------+
﻿
| c2   | c3   | a      |
﻿
+------+------+--------+
﻿
|    1 |    1 | 3.3333 |
|    1 |    1 | 3.3333 |
|    1 |    2 | 3.3333 |
|    1 |    4 | 3.3333 |
|    1 |    6 | 3.3333 |
|    1 |    6 | 3.3333 |
﻿
+------+------+--------+
6 rows in set (0.06 sec)

Frame	ROWS 场景	RANGE 场景
CURRENT ROW	当前行。	与当前行 ORDER BY 列值相同的所有行。
UNBOUNDED PRECEDING	到分区第一行。	到分区第一行。
UNBOUNDED FOLLOWING	到分区最后一行。	到分区最后一行。
PRECEDING	当前行向前偏移 N 行。	到 ORDER BY 列值大于等于（当前行值 - N）对应的行。
FOLLOWING	当前行向后偏移 N 行。	到 ORDER BY 列值小于等于（当前行值 + N）对应的行。

函数名称	功能描述	函数参数	支持类型
ROW_NUMBER()	对每个分区内的数据按 ORDER BY 顺序标记行号。	-	-
RANK()	对每个分区内的数据进行非密集型排名（相同值同名次，下一名次跳号）。	-	-
DENSE_RANK()	对每个分区内的数据进行密集型排名（相同值同名次，下一名次连续）。	-	-
LEAD(, , )	返回当前行向后行处的值，越界时返回。	[必选] ：计算列。	所有类型。
				[可选] ：当前行向后偏移行数，缺省为 1。	整数类型（INT、BIGINT 等）。
				[可选] ：越界时返回的默认值，缺省为 NULL。	类型需与一致，或可被列的类型容纳。
LAG(, , )	返回当前行向前行处的值，越界时返回。	[必选] ：计算列。	所有类型。
				[可选] ：当前行向前偏移行数，缺省为 1。	整数类型（INT、BIGINT 等）。
				[可选] ：越界时返回的默认值，缺省为 NULL。	类型需与一致，或可被列的类型容纳。
FIRST_VALUE()	返回窗口框架内第一行的值。	[必选] ：计算列。	所有类型。
LAST_VALUE()	返回窗口框架内最后一行的值。	[必选] ：计算列。	所有类型。
MIN()	返回窗口框架内的最小值。	[必选] ：计算列。	数值类型、字符类型、时间类型。
MAX()	返回窗口框架内的最大值。	[必选] ：计算列。	数值类型、字符类型、时间类型。
COUNT()	返回窗口框架内非 NULL 值的行数。	[必选] ：计算列。	所有类型。
SUM()	返回窗口框架内的总和。	[必选] ：计算列。	数值类型。
AVG()	返回窗口框架内的平均值。	[必选] ：计算列。	数值类型。

窗口函数使用说明

本页目录：

WindowFrame

WindowFunction

详细案例

ROWS 关键字

RANGE 关键字

ROW_NUMBER

RANK 与 DENSE_RANK

LEAD 与 LAG

一、单参数场景

二、双参数场景

三、三参数场景

FIRST_VALUE 与 LAST_VALUE

MIN 与 MAX

COUNT

SUM

AVG