TDengine SQL 窗口函数（Window Functions）完全指南：OVER 子句、窗口框架与实战-二趣网

TDengine SQL 窗口函数（Window Functions）完全指南：OVER 子句、窗口框架与实战

【免费下载链接】TDengineHigh-performance, scalable time-series database designed for Industrial IoT (IIoT) scenarios项目地址: https://gitcode.com/GitHub_Trending/tde/TDengine

导读：本文围绕 TDengine（从 v3.4.2.0 起支持）的 SQL 标准OVER子句与窗口函数展开。窗口函数为结果集中的每一行计算一个值，计算时可同时看到当前行与同一窗口内的其他行，但不会把多行合并成一行——这与INTERVAL、STATE_WINDOW、SESSION等时序窗口（详见时序扩展）有本质区别。读完本文，你将掌握窗口函数的调用语法、窗口框架（ROWS/RANGE）的边界语义、命名窗口的复用规则、十大窗口函数的用法与参数细节，并能直接套用文中的智能电表示例解决移动平均、累计求和、分组排名、邻行对比等分析需求。

窗口函数与普通时序窗口的区别

TDengine 从 v3.4.2.0 开始支持 SQL 标准的OVER子句和窗口函数。要理解这一特性，必须先厘清它与文档中常说的"时序窗口"的区别：

时序窗口（INTERVAL、STATE_WINDOW、SESSION等，见 Time-Series Extensions）：把窗口内的多行数据聚合成一行输出；
窗口函数：为结果集中的每一行保留原始行，仅在其后追加一个计算列。计算当前行时，可以同时引用同窗口内的其他行（如之前的行、之后的行、同行值）。

窗口函数天然适合移动平均、累计求和、分组排名、与相邻行对比等分析场景，也是报表工具和 BI 工具自动生成的 SQL 中非常常见的语法。

基本概念与调用语法

一个窗口函数调用由两部分组成：

function_name ( [ arguments ] ) OVER ( window_spec | window_name )

window_spec：直接写在OVER (...)括号内的内联窗口规格；
window_name：引用由WINDOW子句定义的命名窗口。

窗口规格描述"当前行的计算所涉及的行的集合"，由三个可选部分组成：

window_spec: [ PARTITION BY expr [, ...] ] [ ORDER BY expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, ...] ] [ frame_clause ]

组成部分	作用	说明
`PARTITION BY`	将输入结果集按一个或多个表达式拆分为相互独立的分区，各分区独立计算	省略时整个结果集视为一个分区
`ORDER BY`	在分区内按一个或多个表达式排序，支持`ASC`/`DESC`（默认`ASC`）与`NULLS FIRST`/`NULLS LAST`	决定行号、邻值、累计范围等对顺序敏感的语义
`frame_clause`	窗口框架，在有序分区内进一步界定参与计算的行的范围	详见下文"窗口框架"

需要特别强调的是使用位置限制：窗口函数只能出现在查询块的SELECT列表和ORDER BY中，不允许出现在WHERE、GROUP BY、HAVING、PARTITION BY或框架边界表达式中。若需要对窗口结果做过滤或再聚合，应把窗口查询写成子查询，在外层查询中引用其输出列——这正是本文"命名窗口 + 子查询过滤"示例的做法。

窗口框架（Window Frame）

窗口框架在有序分区内为当前行界定一个更小的行集合，例如"最近 3 行"、"当前行前后各 1 行"或"当前行之前的所有行"。框架由框架单位（frame unit）和上下边界构成：

frame_clause: { ROWS | RANGE } frame_extent frame_extent: frame_bound | BETWEEN frame_bound AND frame_bound frame_bound: UNBOUNDED PRECEDING | expr PRECEDING | CURRENT ROW | expr FOLLOWING | UNBOUNDED FOLLOWING

框架单位与边界语义

框架单位（Frame Unit）：
- ROWS：按物理行数界定边界，expr为非负整数的行数；
- RANGE：按ORDER BY值的距离界定边界，CURRENT ROW会包含所有与当前行排序值相同的行（即peer rows，同行值行）。
省略BETWEEN的简写形式只指定起始边界，结束边界默认为CURRENT ROW。例如ROWS 10 PRECEDING等价于ROWS BETWEEN 10 PRECEDING AND CURRENT ROW。
五种边界：UNBOUNDED PRECEDING（分区起点）、expr PRECEDING（当前行之前）、CURRENT ROW（当前行）、expr FOLLOWING（当前行之后）、UNBOUNDED FOLLOWING（分区终点）。
当框架边界超出分区范围时，只使用分区内实际存在的行。

从源码实现看，TDengine 在 windowfunc.h 中定义了SSqlWindowFrameRange（含start/end）来承载每行最终计算出的框架范围，并在 windowfuncoperator.c 的winCalcRowsFrame中实现了ROWS框架：先由winCalcBoundPosition将边界换算成分区内的行位置，再用winMaxI64(start, 0)与winMinI64(end, partitionRows - 1)把越界部分裁剪到分区范围内；若start > end（例如框架完全落在分区外），则返回空范围，该行对应的聚合结果自然为NULL或空集。单测 windowFuncFrameTests.cpp 覆盖了这些边界情况。

默认窗口框架

未显式指定框架时，默认框架由以下规则决定：

场景	默认框架
无`ORDER BY`	整个分区（`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`）
有`ORDER BY`，且为聚合类窗口函数	`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`（从分区起点到当前行及其同行值行）
有`ORDER BY`，且为排名/分布/取值类窗口函数	不依赖框架，在整个分区的有序结果上计算

RANGE 框架的约束

RANGE框架在边界语义上比ROWS更严格，TDengine 从源码层面做了明确约束：

带数值或时间偏移量（expr PRECEDING/expr FOLLOWING）的RANGE框架只允许一个ORDER BY表达式，多个排序表达式会报错；
偏移量类型必须与排序列类型匹配：
- 排序列为时间戳类型时，偏移量须使用 TDengine 的时间时长写法，如10s PRECEDING、1m PRECEDING；
- 排序列为数值类型（整型或浮点，不含UNSIGNED BIGINT）时，偏移量须为非负整数；
无偏移量的RANGE框架（仅CURRENT ROW、UNBOUNDED PRECEDING/FOLLOWING）按peer 语义计算，允许多个ORDER BY表达式，此时以全部排序键共同决定同行值行；
排序列为字符串、布尔等其他无法解释"距离"的类型时，带偏移量的RANGE框架会报错。

RANGE框架的底层计算在 windowfuncoperator.c 中分为两条路径：winCalcRangeFrameForInt64针对整型/时间戳（按current ± offset计算上下界后线性扫描命中行，并做了饱和加减处理），winCalcRangeFrameForDouble针对浮点型（额外处理了NaN：当前值为NaN时仅匹配NaN行，非NaN行一律跳过）。

命名窗口（Named Windows）

当查询中多个窗口函数共享同一窗口规格时，可用WINDOW子句给规格命名，再通过OVER window_name引用，避免重复书写：

SELECT avg(voltage) OVER win AS ma, max(voltage) OVER win AS mx FROM meters WINDOW win AS (PARTITION BY tbname ORDER BY ts ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY ts;

WINDOW子句放在查询语句末尾，可逗号分隔定义多个命名窗口。命名窗口遵循以下规则：

命名窗口仅在定义它的查询块内有效，不会泄漏到外层或内层查询块；
OVER window_name必须整体引用命名窗口的定义，不能在引用处追加或覆盖PARTITION BY、ORDER BY或框架；
不支持从一个命名窗口继承另一个命名窗口；
引用未定义的窗口名，或同一查询块内重复定义同名窗口，都会返回明确错误。

窗口函数清单

窗口函数分为两类：一类是原有的聚合/选择函数加上OVER子句即可当作窗口函数使用；另一类是本次特性新增的专用窗口函数。

聚合与选择类窗口函数

以下既有函数加上OVER子句后即成为窗口聚合，按当前行的窗口框架进行计算。NULL 处理、数值精度与类型推导沿用其常规聚合语义。这些函数都不要求ORDER BY：省略时窗口覆盖整个分区；给出ORDER BY但未指定框架时，窗口默认为从分区起点到当前行（含同行值行）。

函数	说明
`count(expr)`	窗口框架内非 NULL 行的数量
`sum(expr)`	窗口框架内求和
`min(expr)`	窗口框架内最小值
`max(expr)`	窗口框架内最大值
`avg(expr)`	窗口框架内平均值
`percentile(expr, p)`	窗口框架内分位数
`first(expr)`	窗口框架内第一个非 NULL 值
`last(expr)`	窗口框架内最后一个非 NULL 值
`last_row(expr)`	窗口框架内最后一行的值，不忽略 NULL

排名类窗口函数

排名函数依赖排序结果，必须指定ORDER BY，且忽略窗口框架。

函数	返回类型	说明
`row_number()`	BIGINT	当前行在分区内的行号，从 1 开始；同行值行也严格递增
`rank()`	BIGINT	当前行的排名；同行值行共享同一排名，后续排名跳过并列数
`dense_rank()`	BIGINT	当前行的排名；同行值行共享同一排名，后续排名不跳过

以rank()为例，底层实现位于 windowfuncoperator.c 的winCalcRankValue：通过预先扫描同行值区间得到peerStart（当前行所在同行组的起点），排名即为peerStart + 1，因此并列行得到相同排名，而后续行则跳过并列数量。

分布类窗口函数

分布函数同样依赖排序结果，必须指定ORDER BY，且忽略窗口框架。

函数	返回类型	说明
`percent_rank()`	DOUBLE	相对排名，`(rank - 1) / (分区行数 - 1)`；分区仅一行时返回 0
`cume_dist()`	DOUBLE	累积分布，`排序值 <= 当前行的行数 / 分区行数`

源码实现与公式完全对应：winCalcPercentRank（windowfuncoperator.c）在partitionRows == 1时直接返回0.0，否则计算(rank - 1) / (partitionRows - 1)；winCalcCumeDist（windowfuncoperator.c）用同行值组终点peerEnd + 1除以分区行数。

取值类窗口函数

取值函数依赖排序结果，必须指定ORDER BY。

函数	返回类型	说明
`lag(expr [, offset [, default]])`	与`expr`相同	当前行之前`offset`行的`expr`值
`lead(expr [, offset [, default]])`	与`expr`相同	当前行之后`offset`行的`expr`值
`first_value(expr)`	与`expr`相同	当前窗口框架内第一行的`expr`值
`last_value(expr)`	与`expr`相同	当前窗口框架内最后一行的`expr`值
`nth_value(expr, n)`	与`expr`相同	当前窗口框架内第`n`行的`expr`值，`n`从 1 开始

lag/lead的参数细节：

offset：行偏移量，省略时默认为 1；作为窗口函数使用时必须>= 0（offset为 0 表示当前行）；
default：目标行不存在时返回的值，必须与expr类型兼容；省略时返回NULL；
nth_value的n必须>= 1；第n行不存在时返回NULL。

:::notelag/lead也可以不带OVER子句使用，此时按输入结果集的行序求值，详见顺序分析函数。参数规则略有差异：不带OVER时offset必须是大于 0 的整数；带OVER时offset可以为 0。 :::

在函数注册层面，这些专用窗口函数在 builtins.c 中统一定义，并通过fmCanUseAsSqlWindowAgg等机制校验其是否可作为 SQL 窗口聚合使用（见 windowfuncoperator.c 的winFuncCheckDedicatedFallback）。

使用限制

窗口函数只能出现在查询块的SELECT列表和ORDER BY中；出现在WHERE、GROUP BY、HAVING、PARTITION BY、框架边界表达式，或作为标量函数/另一个窗口函数规格的参数时，都会报错；
窗口函数不能嵌套：窗口函数的参数或窗口规格中不能包含另一个窗口函数；
对顺序敏感的窗口函数（排名、分布、取值三类）在未指定ORDER BY时返回错误；
当前版本仅支持批量查询，窗口函数不支持用于流式计算；
排序时NULL默认视为最小值；同行值行的输出顺序由ORDER BY决定，需要稳定的逐行顺序时请补充显式排序键。

用 OFFSET 跳过窗口预热期

使用固定长度窗口计算移动指标时，开头的若干行往往缺少足够的历史数据。此时可在窗口计算完成后用OFFSET N跳过前 N 行结果。OFFSET在窗口值计算完成后生效，不会改变已算出的窗口值。

从 v3.4.2.0 起，OFFSET N可以独立使用而无需搭配LIMIT：

SELECT v, avg(v) OVER (ORDER BY ts ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS ma FROM meters ORDER BY ts OFFSET 9;

实战示例

以下示例基于 TDengine 文档通篇使用的智能电表数据模型：超级表meters，包含列ts、current、voltage、phase以及标签location、groupid。

移动平均：计算每只电表最近 10 个样本的平均电压。

SELECT tbname, ts, voltage, avg(voltage) OVER (PARTITION BY tbname ORDER BY ts ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS ma FROM meters ORDER BY tbname, ts;

累计求和：计算每只电表的电流累计值。

SELECT tbname, ts, current, sum(current) OVER (PARTITION BY tbname ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM meters ORDER BY tbname, ts;

分组排名：在每个分组内按电压降序排名，同时展示row_number、rank、dense_rank三种排名的差异。

SELECT groupid, tbname, voltage, row_number() OVER (PARTITION BY groupid ORDER BY voltage DESC) AS rn, rank() OVER (PARTITION BY groupid ORDER BY voltage DESC) AS rk, dense_rank() OVER (PARTITION BY groupid ORDER BY voltage DESC) AS drk FROM meters;

邻行对比：计算每只电表相邻样本间的电压差。

SELECT tbname, ts, voltage, lag(voltage) OVER (PARTITION BY tbname ORDER BY ts) AS prev_v, voltage - lag(voltage) OVER (PARTITION BY tbname ORDER BY ts) AS delta FROM meters ORDER BY tbname, ts;

时间范围窗口：按时间值计算每行之前 10 秒内的电压总和——注意这里用的是RANGE配合时间时长偏移10s。

SELECT tbname, ts, voltage, sum(voltage) OVER (PARTITION BY tbname ORDER BY ts RANGE BETWEEN 10s PRECEDING AND CURRENT ROW) AS sum_10s FROM meters ORDER BY tbname, ts;

命名窗口 + 子查询过滤：在子查询中计算移动平均，再在外层查询中筛选出电压高于移动平均的行——这也是窗口函数结果不能直接进WHERE时的标准写法。

SELECT tbname, ts, voltage, ma FROM ( SELECT tbname, ts, voltage, avg(voltage) OVER win AS ma FROM meters WINDOW win AS (PARTITION BY tbname ORDER BY ts ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) ) t WHERE voltage > ma ORDER BY tbname, ts;

小结

窗口函数是 TDengine 从 v3.4.2.0 起提供的标准 SQL 分析能力：它以"每行追加计算列"的方式完成移动平均、累计求和、分组排名、邻行对比、时间范围聚合等时序分析，与INTERVAL/SESSION等"多行聚一行"的时序窗口互补。理解PARTITION BY/ORDER BY/框架三要素、ROWS与RANGE的边界语义、默认框架规则以及命名窗口的复用方式，即可在查询与 BI 场景中自如使用；若需对窗口结果过滤，记住"子查询包一层"这一关键技巧。

【免费下载链接】TDengineHigh-performance, scalable time-series database designed for Industrial IoT (IIoT) scenarios项目地址: https://gitcode.com/GitHub_Trending/tde/TDengine

创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考

企业官网建设流程全解析