云服务器特惠 99/年起
  1. 腾讯云优惠网首页
  2. 腾讯云产品

TDSQL Boundless 窗口函数使用说明

• 腾讯云产品

窗口函数也叫 OLAP 函数（Online Analytical Processing，联机分析处理），用于对分区内的数据进行实时分析与计算，是分析型数据库的核心能力之一。

相关产品
TDSQL Boundless 产品页，含版本规格、价格与购买入口。

只读分析实例中窗口函数的使用方法与 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_numFROM student_scores;

该示例的语义为：在分组（departmentid、classid）内，按 math 列升序为每一行编号。

注意：说明：

一、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 drkFROM 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(<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)

相关内容：

  1. TDSQL-C MySQL 版 窗口函数使用说明 窗口函数也叫 OLAP 函数（Online Analytical Processing，联机分析处理），可以对数据进行实时分析处理，对于一个分析型数据库而言非常重要。...
  2. TDSQL Boundless SQL调优 在使用 理解执行计划 章节中的工具找出真正的慢查询以及它具体慢在哪里后，用户可能需要采取相应的措施来优化该查询的执行效率，这些措施大致可以分为三类。...
  3. TDSQL PostgreSQL版 高级 SQL 语句编写 drop table if exists bills ;create table bills ( id serial not null, goodsdesc text not null, beginunit text not null, begincity text not null, pubtime timestamp not null, amount float8 not null default 0, prim。...
  4. TDSQL Boundless CREATE TABLE CREATE TABLE [IF NOT EXISTS] table_name (table_definition_list) [table_option_list] [partition_clause] [opt_using_policy] ;table_definition_list: table_definition [, table_definition ...]table_definition: col。...
  5. TDSQL Boundless PARTITION_POLICY_AFFINITIES INFORMATION_SCHEMA.PARTITION_POLICY_AFFINITIES用于描述 PARTITION POLICY 内的亲和性列表。用户表绑定到 PARTITION POLICY，PARTITION POLICY AFFINITIES 维护哪些用户表、用户分区具有亲和性。...
  6. TDSQL Boundless PARTITION_POLICY_PARTITIONS INFORMATION_SCHEMA.PARTITION_POLICY_PARTITIONS用于查询系统当中存在的 PARTITION POLICY 规则，INFORMATION_SCHEMA.PARITTION_POLICIES和INFORMATION_SCHEMA.PARTITION_POLICY_PARTITIONS组成 PARTITION POLICY 规则定义，类似于INFORMATION_SCHEMA.T。...
  7. TDSQL Boundless CREATE PARTITION POLICY 显式创建分区亲和性策略。亲和性可用于控制数据的放置方式以及数据之间的亲和关系使其在自动调度时遵守预设的策略。通过亲和性策略，用户可实现将不同表的分区绑定在同一物理节点，减少分布式开销的效果，适合关联查询频繁的表组。...
  8. TDSQL Boundless 全局二级索引 全局二级索引（Global Secondary Index，GSI）是 TDSQL Boundless 分区表提供的一种跨分区索引类型。与默认的 LOCAL 索引（每个分区独立存储索引数据）不同，GSI 将索引数据跨所有分区全局有序存储，使优化器可以直接通过 GSI 定位数据所在分区，无需扫描所有分区。...
赞 (0)
上一篇 4天前
下一篇 4天前