数据库时序数据库物联网大数据实时分析云原生【免费下载链接】tdengineTDengine is an open source, high-performance, cloud native time-series database optimized for Internet of Things (IoT), Connected Cars, Industrial IoT and DevOps.项目地址https://gitcode.com/taosdata/tdengine点击查看免费下载导读本文聚焦 TDenginev3.4.2.0 起对 SQL 标准OVER子句与窗口函数的完整支持。窗口函数与 TDengine 传统的时间窗口INTERVAL、STATE_WINDOW、SESSION等不同它保留每一行原始数据、只追加一列计算结果天然适合移动平均、累计求和、分组排名、相邻行比较等分析场景也是报表与 BI 工具自动生成 SQL 中非常常见的语法。读完本文你将掌握窗口函数调用语法、ROWS/RANGE窗口帧规则、默认窗口帧行为、WINDOW命名窗口复用以及lag/lead、排名类、分布类、取值类等全部窗口函数的用法与限制并能直接在 TDengine 中运行文中的所有示例。窗口函数与时间窗口的区别在 TDengine 中INTERVAL、STATE_WINDOW、SESSION等时间窗口属于时序数据扩展功能它们把窗口内的多行聚合坍缩成一行输出。而窗口函数完全不同它为结果集中的每一行计算一个值计算时可以看到当前行以及同一窗口内的其他行它不会把多行合并为一行原始行全部保留只在旁边追加一列计算结果。这个“逐行计算、逐行输出”的特性决定了窗口函数非常契合移动平均、运行累计、分区排名、与相邻行比较等分析场景。在 TDengine 中窗口函数调用由两部分组成function_name ( [ arguments ] ) OVER ( window_spec | window_name )window_spec直接写在OVER (...)括号内的内联窗口定义window_name通过WINDOW子句定义的命名窗口的引用。窗口规范window_spec描述“对于当前行参与计算的行集合如何确定”包含三个可选部分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 unitROWS按物理行数界定范围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分区终点。当帧边界超出分区范围时只使用分区中实际存在的行。默认窗口帧未显式指定帧时默认帧按如下规则确定场景默认帧无ORDER BY整个分区ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING有ORDER BY聚合类窗口函数RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW从分区起点到当前行及其对等行有ORDER BY排名/分布/取值类窗口函数这些函数不依赖帧基于整个分区的有序结果计算RANGE 帧约束RANGE帧的取值规则比ROWS帧更严格使用不当会直接报错带数值或时间偏移expr PRECEDING/expr FOLLOWING的RANGE帧只允许一个ORDER BY表达式多个排序表达式会报错偏移类型必须与排序列类型匹配排序列为时间戳类型时偏移必须使用 TDengine 时长写法如10s PRECEDING、1m PRECEDING排序列为数值类型整型或浮点不含UNSIGNED BIGINT时偏移必须是非负整数不带偏移的RANGE帧只有CURRENT ROW、UNBOUNDED PRECEDING/FOLLOWING按对等语义计算允许多个ORDER BY表达式按所有排序键共同确定对等行当排序列是字符串、布尔等无法解释“范围距离”的类型时带偏移的RANGE帧会报错。从源码实现看帧边界计算在窗口函数算子中完成winCalcRangeFrameForInt64、winCalcRangeFrameForDouble分别处理整型与浮点排序列的RANGE偏移计算winFuncFindPeerRange负责按对等语义寻找对等行区间帧的计算结果由 windowfunc.h 中的SSqlWindowFrameRange含start/end承载。命名窗口WINDOW 子句当一条查询中有多个窗口函数共享同一窗口定义时可以用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子句即可当窗口函数使用以及该功能新增的专用窗口函数。在规划层这两类窗口函数由planner 的逻辑计划统一识别并生成QUERY_NODE_LOGIC_PLAN_WINDOW_FUNC节点见其中pFunc-pOver的判断逻辑在执行层由窗口函数算子负责把输入行按分区物化winFuncBuildInput中的winInputStoreAppendBlock必要时落盘到页缓存、逐行计算帧并产出结果。聚合与选择类窗口函数以下既有函数加OVER子句后即可作为窗口聚合使用作用于当前行的窗口帧。空值处理、数值精度与类型推断遵循其常规聚合语义。函数说明count(expr)窗口帧内非空行数sum(expr)窗口帧内求和min(expr)窗口帧内最小值max(expr)窗口帧内最大值avg(expr)窗口帧内平均值percentile(expr, p)窗口帧内百分位first(expr)窗口帧内第一个非空值last(expr)窗口帧内最后一个非空值last_row(expr)窗口帧内最后一行取值不忽略空值这些函数都不强制要求ORDER BY省略时窗口覆盖整个分区给了ORDER BY但未指定帧时窗口默认覆盖从分区起点到当前行含对等行。排名类窗口函数排名类函数依赖排序结果必须指定ORDER BY且忽略窗口帧。函数返回类型说明row_number()BIGINT当前行在分区内的行号从 1 开始对等行也严格递增rank()BIGINT当前行的排名对等行共享同一排名后续排名按并列数量跳号dense_rank()BIGINT当前行的排名对等行共享同一排名后续排名不跳号分布类窗口函数分布类函数同样依赖排序结果必须指定ORDER BY且忽略窗口帧。函数返回类型说明percent_rank()DOUBLE相对排名(rank - 1) / (分区行数 - 1)分区只有一行时返回 0cume_dist()DOUBLE累积分布排序值 当前行的行数 / 分区行数取值类窗口函数取值类函数依赖排序结果必须指定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。作为窗口函数使用时必须 0offset为 0 表示当前行本身default目标行不存在时返回的值必须与expr类型兼容省略时返回NULL对nth_valuen必须 1第n行不存在时返回NULL。:::notelag/lead也可以不带OVER子句使用此时按输入结果集的行顺序计算详见顺序分析函数。两者的参数规则略有差异不带OVER时offset必须是大于 0 的整数带OVER时offset可以为 0。 :::函数注册表builtins.c中可以看到这些专用窗口函数的定义细节例如lag/lead的参数声明为 1~3 个第二个参数限定整型且取值范围[0, INT64_MAX]对应offset第三个参数类型与第一个参数对齐对应default并带有FUNC_MGT_SQL_WINDOW_FUNC | FUNC_MGT_SQL_WINDOW_ORDER_FUNC分类标记表明其依赖ORDER BYrow_number/rank/dense_rank声明为 0 参数并输出BIGINTfirst_value、percent_rank等也都在同一注册表中定义。执行层则在winFuncCalcDedicated中按FUNCTION_TYPE_ROW_NUMBER、FUNCTION_TYPE_RANK、FUNCTION_TYPE_LAG、FUNCTION_TYPE_FIRST_VALUE等类型分别计算例如lag的实现是target row - offset若目标行落在分区范围内则拷贝该行值否则返回default或NULLnth_value在nth超过帧内行数时直接置空。使用限制窗口函数只能出现在查询块的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。建议先建表并写入少量数据参考快速上手或对应建表文档再执行下述 SQL 对照验证。移动平均计算每块电表最近 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;分区排名在每个分组内按电压降序排名。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 秒内的电压和。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;命名窗口 子查询过滤在子查询中计算移动平均再在外层过滤出高于移动平均的行。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;这些语法的正确性与边界行为都有规划层测试覆盖可参考 planWindowTest.cpp其中验证了内联OVER (...)、命名窗口OVER win、多个不同窗口函数并存、窗口聚合表达式中带标量运算如avg(c1 1)等场景的解析与规划结果。延伸阅读时序数据扩展时间窗口与插值了解INTERVAL、STATE_WINDOW、SESSION等与窗口函数互补的窗口聚合能力顺序分析函数 lag/leadlag/lead不带OVER子句时的行序语义SQL 手册总览TDengine 全部 SQL 语句与函数的完整索引赞分享数据库时序数据库物联网大数据实时分析云原生【免费下载链接】tdengineTDengine is an open source, high-performance, cloud native time-series database optimized for Internet of Things (IoT), Connected Cars, Industrial IoT and DevOps.项目地址https://gitcode.com/taosdata/tdengine点击查看免费下载相关推荐TDengine 窗口函数完全指南OVER 子句、窗口帧与 SQL 标准窗口函数实践TDengine 窗口函数完全指南OVER 子句、窗口帧与 SQL 标准窗口函数实践 自 v3.4.2.0 起TDengine 支持 SQL 标准的 OVE数据库时序数据库大数据物联网云原生TDengine SQL 窗口函数Window Functions完全指南OVER 子句、窗口框架与实战TDengine SQL 窗口函数Window Functions完全指南OVER 子句、窗口框架与实战 导读 本文围绕 TDengine从 v3.4数据库时序数据库大数据物联网云原生Daft SQL 窗口函数完全指南语法、窗口帧、实战案例与源码解析Daft SQL 窗口函数完全指南语法、窗口帧、实战案例与源码解析 窗口函数Window Functions是 Daft SQL 中在与当前行相关的一组大数据数据分析数据工程AI 应用创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考