TDengine中的伪列

伪列不是 TDengine 的独创概念，熟悉 Oracle 的都知道，在 Oracle 中就经常使用伪列 ROWID 和 ROWNUM。在《TDengine3.0 踩坑实录》吐槽过TDengine 的有些查询，需要使用伪列才能获取到想要的结果。

吐槽归吐槽，真正要使用 TDengine，还是必须要了解伪列的。

• 以下对于 TDengine 伪列的介绍主要来自官方文档；
• 以下示例均在3.0.2.4 完成，不一定适用于其他版本；
• 部分数据为taosBenchmark -y 自动创建。

TDengine中常用的伪列如下：

文章目录

• • tbname
  • _c0/_rowts
  • _qstart/_qend
  • _wstart/_wedn/_wduration
  • _irowts

tbname

tbname 代表了超级表的子表名，类似于超级表的一个标签。
查看超级表结构

taos> desc meters;field              |         type         |   length    |   note   |
=================================================================================ts                             | TIMESTAMP            |           8 |          |current                        | FLOAT                |           4 |          |voltage                        | INT                  |           4 |          |phase                          | FLOAT                |           4 |          |groupid                        | INT                  |           4 | TAG      |location                       | VARCHAR              |          24 | TAG      |
Query OK, 6 row(s) in set (0.001437s)

表结构中并不存在tbname列，直接使用tbname进行查询。

taos> select distinct tbname from meters limit 10;tbname             |
=================================d0                             |d6250                          |d8750                          |d2503                          |d1254                          |d6251                          |d7501                          |d6                             |d7                             |d8753                          |
Query OK, 10 row(s) in set (0.015408s)

_c0/_rowts

TDengine 使用 _c0/_rowts 作为第一列的伪列。
示例如下：

taos> select * from d0 limit 10;ts            |       current        |   voltage   |        phase         |
======================================================================================2017-07-14 10:40:00.000 |              9.96000 |         111 |              0.29722 |2017-07-14 10:40:00.001 |             10.12000 |         106 |              0.30278 |2017-07-14 10:40:00.002 |              9.96000 |         114 |              0.29167 |2017-07-14 10:40:00.003 |              9.96000 |         105 |              0.30000 |2017-07-14 10:40:00.004 |             10.04000 |         105 |              0.30833 |2017-07-14 10:40:00.005 |              9.80000 |         111 |              0.31667 |2017-07-14 10:40:00.006 |              9.92000 |         106 |              0.29722 |2017-07-14 10:40:00.007 |              9.88000 |         114 |              0.29444 |2017-07-14 10:40:00.008 |              9.96000 |         110 |              0.31111 |2017-07-14 10:40:00.009 |              9.92000 |         107 |              0.29167 |
Query OK, 10 row(s) in set (0.003178s)taos> select ts,_c0,_rowts from d0 limit 10;ts            |           _c0           |         _rowts          |
==============================================================================2017-07-14 10:40:00.000 | 2017-07-14 10:40:00.000 | 2017-07-14 10:40:00.000 |2017-07-14 10:40:00.001 | 2017-07-14 10:40:00.001 | 2017-07-14 10:40:00.001 |2017-07-14 10:40:00.002 | 2017-07-14 10:40:00.002 | 2017-07-14 10:40:00.002 |2017-07-14 10:40:00.003 | 2017-07-14 10:40:00.003 | 2017-07-14 10:40:00.003 |2017-07-14 10:40:00.004 | 2017-07-14 10:40:00.004 | 2017-07-14 10:40:00.004 |2017-07-14 10:40:00.005 | 2017-07-14 10:40:00.005 | 2017-07-14 10:40:00.005 |2017-07-14 10:40:00.006 | 2017-07-14 10:40:00.006 | 2017-07-14 10:40:00.006 |2017-07-14 10:40:00.007 | 2017-07-14 10:40:00.007 | 2017-07-14 10:40:00.007 |2017-07-14 10:40:00.008 | 2017-07-14 10:40:00.008 | 2017-07-14 10:40:00.008 |2017-07-14 10:40:00.009 | 2017-07-14 10:40:00.009 | 2017-07-14 10:40:00.009 |
Query OK, 10 row(s) in set (0.002263s)

_qstart/_qend

_qstart 用户在where条件中输入的时间范围查询起点。
_end 用户在where条件中输入的时间范围查询终点。

示例如下：

taos> select * from test1;ts            |     v1      |
========================================2023-01-01 00:00:00.001 |           1 |2023-01-01 00:00:00.003 |           3 |2023-01-01 00:00:00.005 |           5 |
Query OK, 3 row(s) in set (0.002071s)taos> select ts,_qstart,_qend from test1 where ts>='2023-01-01 00:00:00.002' and ts<'2023-01-01 00:00:00.005';ts            |         _qstart         |          _qend          |
==============================================================================2023-01-01 00:00:00.003 | 2023-01-01 00:00:00.002 | 2023-01-01 00:00:00.004 |
Query OK, 1 row(s) in set (0.002493s)

如果where中没有指定时间范围，则为NULL。

taos> select ts,_qstart,_qend from test1 where v1>2;ts            |         _qstart         |          _qend          |
==============================================================================2023-01-01 00:00:00.003 | NULL                    | NULL                    |2023-01-01 00:00:00.005 | NULL                    | NULL                    |
Query OK, 2 row(s) in set (0.002336s)taos> select ts,_qstart,_qend from test1 where ts>='2023-01-01 00:00:00.002' ;ts            |         _qstart         |          _qend          |
==============================================================================2023-01-01 00:00:00.003 | 2023-01-01 00:00:00.002 | NULL                    |2023-01-01 00:00:00.005 | 2023-01-01 00:00:00.002 | NULL                    |
Query OK, 2 row(s) in set (0.001709s)taos> select ts,_qstart,_qend from test1 where  ts<'2023-01-01 00:00:00.005';ts            |         _qstart         |          _qend          |
==============================================================================2023-01-01 00:00:00.001 | NULL                    | 2023-01-01 00:00:00.004 |2023-01-01 00:00:00.003 | NULL                    | 2023-01-01 00:00:00.004 |
Query OK, 2 row(s) in set (0.002183s)

_wstart/_wedn/_wduration

以上3个伪列分别表示查询时间窗口的起点、终点和范围。

这3个伪列仅用于时间窗口切分查询，如：interval(),state_window(),session()。

示例如下：

taos> desc d0;field              |         type         |   length    |   note   |
=================================================================================ts                             | TIMESTAMP            |           8 |          |current                        | FLOAT                |           4 |          |voltage                        | INT                  |           4 |          |phase                          | FLOAT                |           4 |          |groupid                        | INT                  |           4 | TAG      |location                       | VARCHAR              |          24 | TAG      |
Query OK, 6 row(s) in set (0.000873s)taos> select avg(voltage) from d0 interval(1s) limit 10;avg(voltage)        |
============================109.560000000 |109.536000000 |109.490000000 |109.464000000 |109.567000000 |109.547000000 |109.579000000 |109.618000000 |109.429000000 |109.395000000 |
Query OK, 10 row(s) in set (0.002761s)

如果不使用伪列，做时间窗口查询出的数据其实是没有任何意义的。

taos> select _wstart,_wend,_wduration,avg(voltage) from d0 interval(1s) limit 10;_wstart         |          _wend          |      _wduration       |       avg(voltage)        |
========================================================================================================2017-07-14 10:40:00.000 | 2017-07-14 10:40:01.000 |                  1000 |             109.560000000 |2017-07-14 10:40:01.000 | 2017-07-14 10:40:02.000 |                  1000 |             109.536000000 |2017-07-14 10:40:02.000 | 2017-07-14 10:40:03.000 |                  1000 |             109.490000000 |2017-07-14 10:40:03.000 | 2017-07-14 10:40:04.000 |                  1000 |             109.464000000 |2017-07-14 10:40:04.000 | 2017-07-14 10:40:05.000 |                  1000 |             109.567000000 |2017-07-14 10:40:05.000 | 2017-07-14 10:40:06.000 |                  1000 |             109.547000000 |2017-07-14 10:40:06.000 | 2017-07-14 10:40:07.000 |                  1000 |             109.579000000 |2017-07-14 10:40:07.000 | 2017-07-14 10:40:08.000 |                  1000 |             109.618000000 |2017-07-14 10:40:08.000 | 2017-07-14 10:40:09.000 |                  1000 |             109.429000000 |2017-07-14 10:40:09.000 | 2017-07-14 10:40:10.000 |                  1000 |             109.395000000 |
Query OK, 10 row(s) in set (0.002893s)

从以上可以看到 _wduration 的单位是ms，这个与数据库的时间精度有关。

为了演示会话窗口（session）和状态窗口（state_windows）需要创建一些特殊数据。

taos> select * from test2;ts            |     v1      |
========================================2023-01-01 00:00:01.000 |           1 |2023-01-01 00:00:02.000 |           1 |2023-01-01 00:00:03.000 |           1 |2023-01-01 00:00:05.000 |           2 |2023-01-01 00:00:06.000 |           2 |2023-01-01 00:00:07.000 |           2 |2023-01-01 00:00:11.000 |           3 |2023-01-01 00:00:12.000 |           3 |2023-01-01 00:00:13.000 |           3 |2023-01-01 00:00:14.000 |           3 |
Query OK, 10 row(s) in set (0.002408s)

状态窗口示例如下：

taos> select _wstart,_wend,_wduration,count(*),v1 from test2 state_window(v1);_wstart         |          _wend          |      _wduration       |       count(*)        |     v1      |
==================================================================================================================2023-01-01 00:00:01.000 | 2023-01-01 00:00:03.000 |                  2000 |                     3 |           1 |2023-01-01 00:00:05.000 | 2023-01-01 00:00:07.000 |                  2000 |                     3 |           2 |2023-01-01 00:00:11.000 | 2023-01-01 00:00:14.000 |                  3000 |                     4 |           3 |
Query OK, 3 row(s) in set (0.002160s)

会话窗口示例如下：

taos> select _wstart,_wend,_wduration,count(*) from test2 session(ts,2s);_wstart         |          _wend          |      _wduration       |       count(*)        |
====================================================================================================2023-01-01 00:00:01.000 | 2023-01-01 00:00:07.000 |                  6000 |                     6 |2023-01-01 00:00:11.000 | 2023-01-01 00:00:14.000 |                  3000 |                     4 |
Query OK, 2 row(s) in set (0.002159s)

_irowts

这个类似 _rowts，但仅用于 interp 函数。
interp 函数的槽点太多，已无力吐，有需要使用的同学请自行适应，吐吐就习惯了。

示例如下：

taos> select _irowts,interp(v1) from test2 range('2023-01-01 00:00:01.000','2023-01-01 00:00:14.000') every(1s) fill(prev);_irowts         | interp(v1)  |
========================================2023-01-01 00:00:01.000 |           1 |2023-01-01 00:00:02.000 |           1 |2023-01-01 00:00:03.000 |           1 |2023-01-01 00:00:04.000 |           1 |2023-01-01 00:00:05.000 |           2 |2023-01-01 00:00:06.000 |           2 |2023-01-01 00:00:07.000 |           2 |2023-01-01 00:00:08.000 |           2 |2023-01-01 00:00:09.000 |           2 |2023-01-01 00:00:10.000 |           2 |2023-01-01 00:00:11.000 |           3 |2023-01-01 00:00:12.000 |           3 |2023-01-01 00:00:13.000 |           3 |2023-01-01 00:00:14.000 |           3 |
Query OK, 14 row(s) in set (0.003052s)