代码收藏家 IoTDB标题优化：IoTDB：时序数据库的深度解析
视频学习地址: https://www.bilibili.com/video/BV1pr421W7vL/?p=44&spm_id_from=333.1007.top_right_bar_window_history.content.click&vd_source=d4194c0fd1f413251c909ec298ad4786
【数据库模型】
IoTDB 版本 1.3.3
【IoTDB】
IoTDB是一款低成本、高性能的物联网原生时序数据库. Timeseries database
可以解决企业组件物联网大数据平台管理时序数据时所遇到的应用场景复杂、数据体量大、采样评率高、数据乱序多、数据处理耗时长、分析需求多样、存储与运维成本高等多种问题。
官方文档地址: https://iotdb.apache.org/zh/
数据库模型
IoTDB数据逻辑模型:
叶子节点 属于【传感器】、
叶子节点上一级作为: 【设备Device】
-- 创建数据库 按照工厂
create database root.yaozaifactory
show databases
-- 删除数据库
delete database root.qd
-- 创建时间序列
create timeseries root.yaozaifactory.chejian1.oven.temp double
create timeseries root.yaozaifactory.chejian1.oven.humidity double
insert into root.yaozaifactory.chejian1.oven (timestamp,temp) values(100,15.1);
select * from root.yaozaifactory.chejian1.oven
create timeseries root.yaozaifactory.chejian1.pool.ph double
create timeseries root.yaozaifactory.chejian1.pool.temp double
create timeseries root.yaozaifactory.chejian1.elec.ph double
create timeseries root.yaozaifactory.chejian1.elec.temp double
create timeseries root.yaozaifactory.chejian2.pump.ph double
create timeseries root.yaozaifactory.chejian2.pump.temp double
create timeseries root.yaozaifactory.chejian2.comp.ph double
create timeseries root.yaozaifactory.chejian2.comp.temp double
show devices;
-- 按照工厂下的车间来创建数据库
create database root.yaozaifactory.chejian1
create database root.yaozaifactory.chejian2
delete database root.yaozaifactory.chejian1
【对齐时间序列】:
同一个时间： 不同设备发送数据。 —– 减少对时间的存储;
比如:
时间戳-值-值-值-值-值-……
(2024-04-18 12:00:00, 12hz, 55MPa,20℃)
create aligned timeseries root.yaozaigongchang.chejian2.pump(press FLOAT , pinlv FLOAT)
泵 下的压力、频率传感器 申明为对齐时间格式。把pump 泵这个设备设置为时间对齐序列。
普通创建时间序列就行不通了:
create timeseries root.yaozaifactory.chejian2.pump.ph double
需要改成:
create aligned timeseries timeseries root.yaozaifactory.chejian2.pump (ph double)
【创建时间序列和插入数据】
【IoTDB 常见的数据类型】
BOOLEAN（布尔值）
INT32（整型）
INT64（长整型）
FLOAT（单精度浮点数）
DOUBLE（双精度浮点数）
TEXT（字符串）
创建double类型的时间序列
create timeseries root.yaozaifactory.chejian1.oven.temp double
创建int32类型的时间序列
create timeseries root.yaozaifactory.chejian2.compressor.press int32
创建text类型
create timeseries root.yaozaifactory.chejian2.compressor.mess text
insert into root.yaozaifactory.chejian1.oven (time, temp) values ()
【时间戳数据库类型】
分为2种:
LONG类型 —– 毫秒数(在 IOTDB当中以北京时间为起点1970年1月1日8点0分0秒，从那一刻起，计
算机系统会计算经过的毫秒)
DATATIME类型
DATETIME-INPUT 类型类时间戳手动指定插入的时间
- insert into root.yaozaifactory.chejian1.oven (timestamp,temp) values(2024-03-10 12:12:12,18.2);
- insert into root.yaozaifactory.chejian1.oven (timestamp,temp) values(2024/03/10 12:13:12,18.2);
- insert into root.yaozaifactory.chejian1.oven (timestamp,temp) values(2024-03-10T12:15:12,18.2);
相对时间插入
#当前时间推一分钟
- insert into root.yaozaifactory.chejian1.oven (timestamp,temp) values(now() - 1m,19.2);
- insert into root.yaozaifactory.chejian1.oven (timestamp,temp) values(2024-03-10T12:15:12 - 1h,19.2);
【数据编码和压缩】
编码属于用计算时间换存储空间
二阶差分编码(TS_2DIFF)，比较适合编码单调递增或者递减的序列数据，不适合编码波动较大的数据 游程编码(RLE),比较适合存储某些数值连续出现的序列，不适合编码大部分情况下前后值不一样的序列数据 GORILLA 编码是一种无损编码，它比较适合编码前后值比较接近的数值序列，不适合编码前后波动较大的数据。
PLAIN 编码，默认的编码方式，
创建序列时指定编码:
create timeseries root.yaozaifactory.chejian1.oven.temp3 with datatype = double, encoding = RLE
查看时间序列: show timeseries;
【数据压缩】
数据压缩，主要是减少冗余信息
几种压缩方式：
创建序列时指定压缩方式
create timeseries root.yaozaifactory.chejian1.oven.temp4 with datatype = double, encoding = RLE , compressor = snappy
show timeseries
【数据分区和分片】
IoTDB 以数据分区（DataRegion）为单位对元数据和数据进行管理，从序列和时间两个维度进行数据划分。
分区分片
元数据分区方式：
【数据定义语言DDL】
【数据库管理】
【创建数据库】
create database root.sh //上海施工基地
create database root.qd //青岛施工基地
注意：
1、不允许再创建包含数据库名称的数据库
不允许 create database root.sh.jiading
2、 节点名只支持中英文字符、数字、下划线的组合
设置为纯数字或者包含其他字符，需要用反引号(``)把 database 名称引起来：
create database root.`666`
3、在 Windows 系统上部署，database 名是大小写不敏感的
同时创建root.qd 和 root.QD 是不被允许的
【查看数据库】
show databases
show databases root.*
【查看数据库详情】
show databases detalis
【删除数据库】
delete DATABASE
【时间序列管理】
【创建时间序列】
指定：序列名，数据类型，压缩方法，编码方法
create timeseries root.sh.jiading.tbm11_1.speed double
create timeseries root.sh.jiading.tbm11_1.tor with datatype = float, encoding =plain
create timeseries root.sh.jiading.tbm11_1.dist with datatype = float, encoding =plain
create timeseries root.sh.jiading.tbm11_2.speed with datatype = double
create timeseries root.sh.jiading.tbm11_2.tor with datatype = double, encoding =rle
create timeseries root.sh.jiading.tbm11_2.dist with datatype = float, encoding =plain, compressor = gzip
-- 可以省略with，空格分开
create timeseries root.sh.pudong.tbm3.speed double
create timeseries root.sh.pudong.tbm3.speed double encoding = plain
create timeseries root.sh.pudong.tbm3.dist float encoding =plain compressor = gzip
【创建对齐时间序列】
指定：序列名，数据类型，压缩方法，编码方法
-- 共享时间点, 对齐时间序列. 申明到设备级别 ; aligned 绑定到设备上。
create aligned timeseries root.sh.pudong.tbm4 (speed float, tor float, dist double)
create aligned timeseries root.sh.pudong.tbm5 (speed float encoding = plain compressor = gzip, tor float encoding = plain, dist double)
【删除时间序列】
delete timeseries root.sh.pudong.tbm4.speed
【查看时间序列】
show timeseries
查看指定路径下
show timeseries root.sh.**
show timeseries root.sh.jiading.**
带过滤条件的查询
show timeseries where timeseries contains “tor”
show timeseries root.sh.** where timeseries contains 'speed'
show timeseries root.sh.** where datatype=FLOAT
【时间序列统计】
count timeseries root.**
count timeseries root.sh.**
count timeseries root.sh.** where timeseries contains 'speed'
【给时间序列的设置别名】
create timeseries root.qd.huangdao.tbm1.speed (sudu) FLOAT
show timeseries
【给时间序列打标签】
我们可以在创建时间序列的时候，为它添加别名和额外的标签和属性信息。
**作用：**标签和属性用来辅助我们查询时间序列，相当于打个批注或者备注。 被打有标签的时间序列，会在内存中维护一个索引，就像给一本书加书签一样，可以方便检索和启动某种过滤条件。
属性就只是一个时间序列的说明文字。
create timeseries root.qd.huangdao.tbm2.speed with datatype=FLOAT, encoding=RLE, compression=SNAPPY tags(tag1=v1, tag2=v2) attributes(attr1=v1, attr2=v2)
show timeseries where TAGS (tag1)=v1
show timeseries where TAGS (tag1) contains 'v'
【使用模板创建时间序列】
模板一定是设备，不能是传感器
场景:
在青岛的崂山施工基地，root.qd.laoshan有1万台隧道掘进机TBM，每一台TBM都是速度speed，扭矩tor，位移dist三个量
方法1，手动创建1万个设备
方法2，使用设备模板功能：以1台tbm为例，其他的全都按照这个模板去复制去创建
【1.创建设备模板】
create device template laoshantbm (speed float , tor float, dist int64)
create device template laoshantbm2 (speed float encoding=RLE compressor = GZIP, tor float, dist int64)
【2.挂在模板到数据库上】
挂在模板到数据库上 后面的某个节点上 (包含数据库以后的节点上)
set device template laoshantbm to root.qd.laoshan
注意： 模板挂载路径下禁止创建普通序列，已创建了普通序列的前缀路径上不允许挂载模板
create timeseries root.qd.laoshan.tbm3.ss float
手动创建序列是不被允许的，必须使用模板创建
【3.使用模板创建设备】
create timeseries using device template on root.qd.laoshan.tbm1
create timeseries using device template on root.qd.laoshan.tbm2
//如果根据模板创建的设备，如果一个路径上已经存在设备模板，可以省略创建时间序列。直接插入数据都可以创建时间序列。
insert into root.qd.laoshan.tbm1 (time, speed) values (now(), 5.5)
insert into root.qd.laoshan.tbm3 (time, speed) values (now(), 5.5)
【4.解除设备模板】
删除使用模板创建的设备和时间序列
delete timeseries of device template laoshantbm from root.qd.laoshan.tbm1
delete timeseries of device template laoshantbm from root.qd.laoshan.tbm2
按照模板创建的序列，必须使用模板语句删除
【5.卸载设备模板】
unset device template laoshantbm from root.qd.laoshan
【6.删除模板】
drop device template laoshantbm
【设备操作】
查看现有设备: show devices
【数据操作语言DML】
【数据写入】
【常用数据插入】
【写入一个列】
insert into root.sh.pudong.tbm1 (time, speed) values(2024-04-10 12:00:00, 80.0);
【写入多个列】
insert into root.sh.pudong.tbm1 (timestamp, speed, tor) values(2024-04-10 12:01:00, 82.0, 1000);
【写入多个列，写入多行】
insert into root.sh.pudong.tbm1 (timestamp, speed, tor, dist) values (2024-04-10 12:03:00, 83.0, 1300, 2300), (2024-04-10 12:04:00, 84.0, 1420,2340);
select * from root.sh.pudong.tbm1
【省略时间的插入】
以系统当前时间为时间戳
insert into root.sh.pudong.tbm1 (speed, tor, dist) values(82.6, 2000, 600);
【对齐时间序列插入数据】
创建对齐时间序列需要使用关键字: aligned ,插入数据也需要使用aligned关键字
创建一个对齐的时间序列
create aligned timeseries root.sh.pudong.tbm4 (speed float, tor float, dist double)
insert into root.sh.pudong.tbm4 (time, speed, tor, dist) aligned values(now(), 80.0, 12.3, 800);
insert into root.sh.pudong.tbm4 (time, speed, tor) aligned values(now(), 80.1, 12.5);
insert into root.sh.pudong.tbm4 (time, speed, tor, dist) aligned values (2024-05-10 12:03:00, 83.0, 1300, 2300), (2024-05-10 12:04:00, 84.0, 1420,2340);
select * from root.sh.pudong.tbm4
这个版本中可以省略agined 关键字
【使用Insert更新数据】
insert into root.sh.pudong.tbm1 (time, speed) values(2024-04-10 13:00:00, 80.0);
在相同时间戳下，重新插入数据，覆盖写入
insert into root.sh.pudong.tbm1 (time, speed) values(2024-04-10 13:00:00, 90.0);
【删除数据】
删除某个时间戳的数据需要指定时间范围
delete from root.sh.pudong.tbm1.speed where time >=2024-04-10 13:00:00;
删除时间序列
delete timeseries root.sh.pudong.tbm1.tor
【查询数据-select】
准备数据:
insert into root.sh.pudong.tbm1 (timestamp, speed, tor, dist) values (2024-05-09 12:03:00, 83.0, 1300, 1301), (2024-05-09 12:04:00, 84.0, 1420,2335)
insert into root.sh.pudong.tbm1 (timestamp, speed, tor, dist) values (2024-05-10 12:05:00, 85.0, 1500, 2301), (2024-05-10 12:06:00, 86.0, 1520,2334)
insert into root.sh.pudong.tbm1 (timestamp, speed, tor, dist) values (2024-05-10 13:03:00, 99.0, 1600, 4320), (2024-05-10 13:04:00, 89.0, 1600,2333)
insert into root.sh.pudong.tbm1 (timestamp, speed, tor, dist) values (2024-05-10 14:03:00, 98.0, 1700, 1300), (2024-05-10 15:04:00, 89.0, 1820,2332)
insert into root.sh.pudong.tbm1 (timestamp, speed, tor) values (2024-05-10 15:03:00, 98.0, 1700), (2024-05-10 15:04:00, 89.0, 1820)
insert into root.sh.pudong.tbm4 (timestamp, speed, tor) values (2024-05-10 15:04:00, 98.0, 1700), (2024-05-10 15:05:00, 89.0, 1820)
【普通查询】
select * from root.sh.pudong.tbm1
select speed, tor, dist from root.sh.pudong.tbm1
指定查询后的别名
select speed as sd, tor as 力矩, dist as 位移 from root.sh.pudong.tbm1
【order by 排序查询】
按照降序
select speed from root.sh.pudong.tbm1 order by time desc
order by除了跟时间外，还能按照序列名、数据类型等，但是应用场景比较少
查询最新的点
select last tor from root.sh.pudong.tbm1
传感器节点前面添加 last
【过滤查询-where】
【时间过滤条件】
select speed from root.sh.pudong.tbm1 where time < 2024-05-10 13:03:00
select speed from root.sh.pudong.tbm1 where time > 2024-05-10 13:03:00 and time < 2024-05-10 15:00:00
【值过滤条件】
select speed from root.sh.pudong.tbm1 where speed > 90
select * from root.sh.pudong.tbm1 where speed between 85 and 90
select * from root.sh.pudong.tbm1 where tor in (‘1600’,’1500’)
select * from root.sh.pudong.tbm1 where tor not in (‘1600’,’1500’)
select * from root.sh.pudong.tbm1 where dist is null
select speed from root.sh.pudong.tbm1 where speed > 90 and time > 2024-05-10 13:03:00
【模糊匹配过滤】
如果数据类型是text这种文本类型的，还支持通过like或者正则表达式匹配查询
insert into root.sh.pudong.tbm1 (time, info) values (now(), “normal”)
insert into root.sh.pudong.tbm1 (time, info) values (now()+1000ms, “boom”)
insert into root.sh.pudong.tbm1 (time, info) values (now()+2000ms, “die”)
like的模糊匹配规则
% 表示任意0个或多个字符。
_ 表示任意单个字符。
select * from root.sh.pudong.tbm1 where info like ‘b%’
【正则表达式匹配】
select * from root.sh.pudong.tbm1 where info regexp ‘正则表达式’
【聚合查询】
group by 根据时间列Time 进行分组.
s 秒
m 分
d 天
mo 月
y 年
【两个参数聚合查询】
在IoTDB内描述时间分段两参数：开始时间、结束时间、时间窗口大小 = 窗口之间的间隔
([2024-5-01T00:00:00, 2024-6-01T00:00:00),2h);//时间范围内的时间间隔为2小时。
([2024-01-01T00:00:00, 2024-08-01T00:00:00),1d); //时间间隔为一天
– 每30天的平均速度
select avg(speed) from root.qd.laoshan.tbm1 group by ([2024-02-01T00:00:00, 2024-06-01T00:00:00), 30d);
Time |avg(root.qd.laoshan.tbm1.speed)|
-----------------------+-------------------------------+
2024-02-01 00:00:00.000| 37.333333333333336|
2024-03-02 00:00:00.000| 37.166666666666664|
2024-04-01 00:00:00.000| 35.166666666666664|
2024-05-01 00:00:00.000| 37.2|
2024-05-31 00:00:00.000| 31.0|
– 每月平均速度
select avg(speed) from root.qd.laoshan.tbm1 group by ([2024-02-01T00:00:00, 2024-06-01T00:00:00), 1mo);
Time |avg(root.qd.laoshan.tbm1.speed)|
-----------------------+-------------------------------+
2024-02-01 00:00:00.000| 36.93103448275862|
2024-03-01 00:00:00.000| 37.54838709677419|
2024-04-01 00:00:00.000| 35.166666666666664|
2024-05-01 00:00:00.000| 37.0|
– 每隔15天
select avg(speed),count(dist) from root.qd.laoshan.tbm1 group by ([2024-02-01T00:00:00, 2024-06-01T00:00:00), 15d);
Time |avg(root.qd.laoshan.tbm1.speed)|count(root.qd.laoshan.tbm1.dist)|
-----------------------+-------------------------------+--------------------------------+
2024-02-01 00:00:00.000| 37.86666666666667| 15|
2024-02-16 00:00:00.000| 36.8| 15|
2024-03-02 00:00:00.000| 33.333333333333336| 15|
2024-03-17 00:00:00.000| 41.0| 15|
2024-04-01 00:00:00.000| 33.06666666666667| 15|
2024-04-16 00:00:00.000| 37.266666666666666| 15|
2024-05-01 00:00:00.000| 40.666666666666664| 15|
2024-05-16 00:00:00.000| 33.733333333333334| 15|
2024-05-31 00:00:00.000| 31.0| 1|
– 每隔7天
select max_value(*) from root.qd.laoshan.tbm1 group by ([2024-02-01T00:00:00, 2024-06-01T00:00:00), 7d);
Time |max_value(root.qd.laoshan.tbm1.tor)|max_value(root.qd.laoshan.tbm1.dist)|max_value(root.qd.laoshan.tbm1.speed)|
-----------------------+-----------------------------------+------------------------------------+-------------------------------------+
2024-02-01 00:00:00.000| 39.0| 12.0| 56.0|
2024-02-08 00:00:00.000| 36.0| 13.0| 56.0|
2024-02-15 00:00:00.000| 40.0| 14.0| 60.0|
2024-02-22 00:00:00.000| 39.0| 13.0| 52.0|
2024-02-29 00:00:00.000| 40.0| 6.0| 49.0|
2024-03-07 00:00:00.000| 37.0| 3.0| 60.0|
2024-03-14 00:00:00.000| 38.0| 3.0| 51.0|
2024-03-21 00:00:00.000| 40.0| 4.0| 58.0|
2024-03-28 00:00:00.000| 40.0| 13.0| 58.0|
2024-04-04 00:00:00.000| 28.0| 13.0| 57.0|
2024-04-11 00:00:00.000| 40.0| 14.0| 59.0|
2024-04-18 00:00:00.000| 34.0| 14.0| 57.0|
2024-04-25 00:00:00.000| 38.0| 14.0| 49.0|
2024-05-02 00:00:00.000| 39.0| 4.0| 58.0|
2024-05-09 00:00:00.000| 40.0| 4.0| 48.0|
2024-05-16 00:00:00.000| 40.0| 3.0| 57.0|
2024-05-23 00:00:00.000| 38.0| 4.0| 46.0|
2024-05-30 00:00:00.000| 35.0| 4.0| 45.0|
– 每隔15天
select avg(speed),MAX_TIME(*) from root.qd.laoshan.tbm1 group by ([2024-02-01T00:00:00, 2024-06-01T00:00:00), 15d);
Time |avg(root.qd.laoshan.tbm1.speed)|MAX_TIME(root.qd.laoshan.tbm1.tor)|MAX_TIME(root.qd.laoshan.tbm1.dist)|MAX_TIME(root.qd.laoshan.tbm1.speed)|
-----------------------+-------------------------------+----------------------------------+-----------------------------------+------------------------------------+
2024-02-01 00:00:00.000| 37.86666666666667| 1707941977000| 1707941977000| 1707941977000|
2024-02-16 00:00:00.000| 36.8| 1709273850000| 1709273850000| 1709273850000|
2024-03-02 00:00:00.000| 33.333333333333336| 1710596665000| 1710596665000| 1710596665000|
2024-03-17 00:00:00.000| 41.0| 1711871476000| 1711871476000| 1711871476000|
2024-04-01 00:00:00.000| 33.06666666666667| 1713137687000| 1713137687000| 1713137687000|
2024-04-16 00:00:00.000| 37.266666666666666| 1714441216000| 1714441216000| 1714441216000|
2024-05-01 00:00:00.000| 40.666666666666664| 1715726807000| 1715726807000| 1715726807000|
2024-05-16 00:00:00.000| 33.733333333333334| 1717069443000| 1717069443000| 1717069443000|
2024-05-31 00:00:00.000| 31.0| 1717090134000| 1717090134000| 1717090134000|
【三个参数聚合查询】
三参数：开始时间、结束时间、时间窗口大小、窗口之间的间隔(滑动步长)
([2024-5-01T00:00:00, 2024-6-01T00:00:00),2d,7d);
select avg(speed),__endTime from root.qd.laoshan.tbm1 group by ([2024-01-29T00:00:00, 2024-06-01T00:00:00), 2d);
IoTDB> select avg(speed),__endTime from root.qd.laoshan.tbm1 group by ([2024-01-29T00:00:00, 2024-06-01T00:00:00), 2d);
+-----------------------------+-----------------------------+-------------------------------+
| Time| __endTime|avg(root.qd.laoshan.tbm1.speed)|
+-----------------------------+-----------------------------+-------------------------------+
|2024-01-29T00:00:00.000+08:00|2024-01-30T23:59:59.999+08:00| 54.0|
|2024-01-31T00:00:00.000+08:00|2024-02-01T23:59:59.999+08:00| 28.0|
|2024-02-02T00:00:00.000+08:00|2024-02-03T23:59:59.999+08:00| 53.0|
|2024-02-04T00:00:00.000+08:00|2024-02-05T23:59:59.999+08:00| 50.5|
|2024-02-06T00:00:00.000+08:00|2024-02-07T23:59:59.999+08:00| 13.5|
|2024-02-08T00:00:00.000+08:00|2024-02-09T23:59:59.999+08:00| 35.0|
|2024-02-10T00:00:00.000+08:00|2024-02-11T23:59:59.999+08:00| 13.0|
|2024-02-12T00:00:00.000+08:00|2024-02-13T23:59:59.999+08:00| 54.0|
|2024-02-14T00:00:00.000+08:00|2024-02-15T23:59:59.999+08:00| 48.0|
|2024-02-16T00:00:00.000+08:00|2024-02-17T23:59:59.999+08:00| 44.5|
|2024-02-18T00:00:00.000+08:00|2024-02-19T23:59:59.999+08:00| 30.0|
|2024-02-20T00:00:00.000+08:00|2024-02-21T23:59:59.999+08:00| 38.5|
|2024-02-22T00:00:00.000+08:00|2024-02-23T23:59:59.999+08:00| 31.5|
|2024-02-24T00:00:00.000+08:00|2024-02-25T23:59:59.999+08:00| 38.0|
|2024-02-26T00:00:00.000+08:00|2024-02-27T23:59:59.999+08:00| 33.5|
|2024-02-28T00:00:00.000+08:00|2024-02-29T23:59:59.999+08:00| 35.5|
|2024-03-01T00:00:00.000+08:00|2024-03-02T23:59:59.999+08:00| 38.5|
|2024-03-03T00:00:00.000+08:00|2024-03-04T23:59:59.999+08:00| 36.5|
|2024-03-05T00:00:00.000+08:00|2024-03-06T23:59:59.999+08:00| 15.0|
– 窗口大小为2天，每个窗口的间隔距离为7天(步长范围内的时间不参与计算)
IoTDB> select avg(speed),__endTime from root.qd.laoshan.tbm1 group by ([2024-01-29T00:00:00, 2024-06-01T00:00:00), 2d,7d);
+-----------------------------+-----------------------------+-------------------------------+
| Time| __endTime|avg(root.qd.laoshan.tbm1.speed)|
+-----------------------------+-----------------------------+-------------------------------+
|2024-01-29T00:00:00.000+08:00|2024-01-30T23:59:59.999+08:00| 54.0|
|2024-02-05T00:00:00.000+08:00|2024-02-06T23:59:59.999+08:00| 35.5|
|2024-02-12T00:00:00.000+08:00|2024-02-13T23:59:59.999+08:00| 54.0|
|2024-02-19T00:00:00.000+08:00|2024-02-20T23:59:59.999+08:00| 41.5|
|2024-02-26T00:00:00.000+08:00|2024-02-27T23:59:59.999+08:00| 33.5|
|2024-03-04T00:00:00.000+08:00|2024-03-05T23:59:59.999+08:00| 22.5|
|2024-03-11T00:00:00.000+08:00|2024-03-12T23:59:59.999+08:00| 30.5|
|2024-03-18T00:00:00.000+08:00|2024-03-19T23:59:59.999+08:00| 35.5|
|2024-03-25T00:00:00.000+08:00|2024-03-26T23:59:59.999+08:00| 46.5|
|2024-04-01T00:00:00.000+08:00|2024-04-02T23:59:59.999+08:00| 15.0|
|2024-04-08T00:00:00.000+08:00|2024-04-09T23:59:59.999+08:00| 14.5|
|2024-04-15T00:00:00.000+08:00|2024-04-16T23:59:59.999+08:00| 55.0|
|2024-04-22T00:00:00.000+08:00|2024-04-23T23:59:59.999+08:00| 35.5|
|2024-04-29T00:00:00.000+08:00|2024-04-30T23:59:59.999+08:00| 29.5|
|2024-05-06T00:00:00.000+08:00|2024-05-07T23:59:59.999+08:00| 39.0|
|2024-05-13T00:00:00.000+08:00|2024-05-14T23:59:59.999+08:00| 33.5|
|2024-05-20T00:00:00.000+08:00|2024-05-21T23:59:59.999+08:00| 38.0|
|2024-05-27T00:00:00.000+08:00|2024-05-28T23:59:59.999+08:00| 26.0|
+-----------------------------+-----------------------------+-------------------------------+
【滑动窗口 特殊情况:】
【滑动步长 < 时间窗口】
三参数：开始时间、结束时间、时间窗口大小、窗口之间的间隔(滑动步长)
select avg(speed) from root.qd.laoshan.tbm1 group by ([2024-02-01T00:00:00, 2024-06-01T00:00:00), 10d,7d);
窗口大小为10天，窗口之间的间隔(滑动步长)为7天,会带来数据重复。
IoTDB> select avg(speed),__endTime from root.qd.laoshan.tbm1 group by ([2024-02-01T00:00:00, 2024-06-01T00:00:00), 10d,7d);
+-----------------------------+-----------------------------+-------------------------------+
| Time| __endTime|avg(root.qd.laoshan.tbm1.speed)|
+-----------------------------+-----------------------------+-------------------------------+
|2024-02-01T00:00:00.000+08:00|2024-02-10T23:59:59.999+08:00| 34.9|
|2024-02-08T00:00:00.000+08:00|2024-02-17T23:59:59.999+08:00| 38.9|
|2024-02-15T00:00:00.000+08:00|2024-02-24T23:59:59.999+08:00| 37.4|
|2024-02-22T00:00:00.000+08:00|2024-03-02T23:59:59.999+08:00| 35.4|
|2024-02-29T00:00:00.000+08:00|2024-03-09T23:59:59.999+08:00| 32.8|
|2024-03-07T00:00:00.000+08:00|2024-03-16T23:59:59.999+08:00| 36.9|
|2024-03-14T00:00:00.000+08:00|2024-03-23T23:59:59.999+08:00| 36.1|
|2024-03-21T00:00:00.000+08:00|2024-03-30T23:59:59.999+08:00| 40.9|
|2024-03-28T00:00:00.000+08:00|2024-04-06T23:59:59.999+08:00| 36.4|
|2024-04-04T00:00:00.000+08:00|2024-04-13T23:59:59.999+08:00| 34.1|
|2024-04-11T00:00:00.000+08:00|2024-04-20T23:59:59.999+08:00| 41.4|
|2024-04-18T00:00:00.000+08:00|2024-04-27T23:59:59.999+08:00| 37.0|
|2024-04-25T00:00:00.000+08:00|2024-05-04T23:59:59.999+08:00| 40.4|
|2024-05-02T00:00:00.000+08:00|2024-05-11T23:59:59.999+08:00| 41.7|
|2024-05-09T00:00:00.000+08:00|2024-05-18T23:59:59.999+08:00| 36.7|
|2024-05-16T00:00:00.000+08:00|2024-05-25T23:59:59.999+08:00| 34.9|
|2024-05-23T00:00:00.000+08:00|2024-05-31T23:59:59.999+08:00| 35.333333333333336|
|2024-05-30T00:00:00.000+08:00|2024-05-31T23:59:59.999+08:00| 38.0|
+-----------------------------+-----------------------------+-------------------------------+
【结果过滤-having】
having子句必须使用聚合函数的结果过滤
select avg(speed) from root.qd.laoshan.tbm1 group by ([2024-01-29T00:00:00, 2024-06-01T00:00:00), 7d,7d)
select avg(speed) from root.qd.laoshan.tbm1 group by ([2024-01-29T00:00:00, 2024-06-01T00:00:00), 7d,7d) having avg(speed) > 40;
select avg(speed),avg(tor) from root.qd.laoshan.tbm1 group by ([2024-01-29T00:00:00, 2024-06-01T00:00:00), 7d,7d) having avg(speed) > 30;
IoTDB> select avg(speed),avg(tor),__endTime from root.qd.laoshan.tbm1 group by ([2024-01-29T00:00:00, 2024-06-01T00:00:00), 7d,7d) having avg(speed) > 30
+-----------------------------+-----------------------------+-------------------------------+-----------------------------+
| Time| __endTime|avg(root.qd.laoshan.tbm1.speed)|avg(root.qd.laoshan.tbm1.tor)|
+-----------------------------+-----------------------------+-------------------------------+-----------------------------+
|2024-01-29T00:00:00.000+08:00|2024-02-04T23:59:59.999+08:00| 45.0| 34.857142857142854|
|2024-02-12T00:00:00.000+08:00|2024-02-18T23:59:59.999+08:00| 43.857142857142854| 30.857142857142858|
|2024-02-19T00:00:00.000+08:00|2024-02-25T23:59:59.999+08:00| 37.42857142857143| 29.142857142857142|
|2024-02-26T00:00:00.000+08:00|2024-03-03T23:59:59.999+08:00| 36.42857142857143| 31.714285714285715|
|2024-03-04T00:00:00.000+08:00|2024-03-10T23:59:59.999+08:00| 30.571428571428573| 34.714285714285715|
|2024-03-11T00:00:00.000+08:00|2024-03-17T23:59:59.999+08:00| 38.285714285714285| 28.714285714285715|
|2024-03-18T00:00:00.000+08:00|2024-03-24T23:59:59.999+08:00| 36.42857142857143| 33.714285714285715|
|2024-03-25T00:00:00.000+08:00|2024-03-31T23:59:59.999+08:00| 44.285714285714285| 33.0|
|2024-04-08T00:00:00.000+08:00|2024-04-14T23:59:59.999+08:00| 35.714285714285715| 27.714285714285715|
|2024-04-15T00:00:00.000+08:00|2024-04-21T23:59:59.999+08:00| 37.714285714285715| 29.285714285714285|
|2024-04-22T00:00:00.000+08:00|2024-04-28T23:59:59.999+08:00| 41.142857142857146| 30.571428571428573|
|2024-04-29T00:00:00.000+08:00|2024-05-05T23:59:59.999+08:00| 40.57142857142857| 28.142857142857142|
|2024-05-06T00:00:00.000+08:00|2024-05-12T23:59:59.999+08:00| 40.142857142857146| 32.142857142857146|
|2024-05-13T00:00:00.000+08:00|2024-05-19T23:59:59.999+08:00| 31.285714285714285| 29.428571428571427|
|2024-05-20T00:00:00.000+08:00|2024-05-26T23:59:59.999+08:00| 39.42857142857143| 29.714285714285715|
+-----------------------------+-----------------------------+-------------------------------+-----------------------------+
select avg(speed),avg(tor) from root.qd.laoshan.tbm1 group by ([2024-01-29T00:00:00, 2024-06-01T00:00:00), 7d,7d)
IoTDB> select avg(speed),avg(tor) from root.qd.laoshan.tbm1 group by ([2024-01-29T00:00:00, 2024-06-01T00:00:00), 7d,7d)
+-----------------------------+-------------------------------+-----------------------------+
| Time|avg(root.qd.laoshan.tbm1.speed)|avg(root.qd.laoshan.tbm1.tor)|
+-----------------------------+-------------------------------+-----------------------------+
|2024-01-29T00:00:00.000+08:00| 45.0| 34.857142857142854|
|2024-02-05T00:00:00.000+08:00| 25.571428571428573| 28.285714285714285|
|2024-02-12T00:00:00.000+08:00| 43.857142857142854| 30.857142857142858|
|2024-02-19T00:00:00.000+08:00| 37.42857142857143| 29.142857142857142|
|2024-02-26T00:00:00.000+08:00| 36.42857142857143| 31.714285714285715|
|2024-03-04T00:00:00.000+08:00| 30.571428571428573| 34.714285714285715|
|2024-03-11T00:00:00.000+08:00| 38.285714285714285| 28.714285714285715|
|2024-03-18T00:00:00.000+08:00| 36.42857142857143| 33.714285714285715|
|2024-03-25T00:00:00.000+08:00| 44.285714285714285| 33.0|
|2024-04-01T00:00:00.000+08:00| 27.714285714285715| 26.0|
|2024-04-08T00:00:00.000+08:00| 35.714285714285715| 27.714285714285715|
|2024-04-15T00:00:00.000+08:00| 37.714285714285715| 29.285714285714285|
|2024-04-22T00:00:00.000+08:00| 41.142857142857146| 30.571428571428573|
|2024-04-29T00:00:00.000+08:00| 40.57142857142857| 28.142857142857142|
|2024-05-06T00:00:00.000+08:00| 40.142857142857146| 32.142857142857146|
|2024-05-13T00:00:00.000+08:00| 31.285714285714285| 29.428571428571427|
|2024-05-20T00:00:00.000+08:00| 39.42857142857143| 29.714285714285715|
|2024-05-27T00:00:00.000+08:00| 29.2| 30.6|
+-----------------------------+-------------------------------+-----------------------------+
【差值分段聚合查询】
group by variation (物理量，偏差，ignoreNull 对null值的处理)
当ignoreNull为false时，该null值会被视为新的值ignoreNull为true时，则直接跳过对应的点，默认
不用时间列聚合查询了。
select __endTime,count(dist),avg(dist),avg(speed) from root.qd.laoshan.tbm1 where time < 2024-06-01T00:00:00 group by variation (dist,5)
将第一个点作为一个组的基准点，遍历后续的数据点，若数据点和基准点的数值差在一定范围内则划分到一个组内，否则创建新的组。重复此过程
select __endTime,avg(speed),avg(dist),avg(speed) from root.qd.laoshan.tbm1 where time < 2024-06-01T00:00:00 group by variation (dist,6)
【条件分段聚合】
用法：根据指定条件对数据进行筛选，并将连续的符合条件的行分为一组进行聚合运算，可以使用GROUP BY CONDITION的分段方式
group by condition (物理量判断条件，连续满足条件的数量，空值处理)
group by condition(predict,[keep>/>=/=/<=/<]threshold,[,ignoreNull=true/false])
参数1：predict、返回boolean数据类型的合法表达式，用于分组的筛选
参数2：keep表达式用来指定形成分组所需要连续满足predict条件的数据行数，只有行数满足keep表达式的分组才会被输出
参数3：指定遇到predict为null的数据行时的处理方式，为true则跳过该行，为false则结束当前分组
select __endTime, count(dist),avg(seed) from root.qd.laoshan.tbm1 where time < 2024-06-01T00:00:00 group by condition (dist > 5, keep > 4)
keep 是关键字哈。需要写的哈。
IoTDB> select __endTime, count(dist) as 满足条件个数, max_value(speed) as 最大速度 from root.qd.laoshan.tbm1 where time < 2024-06-01T00:00:00 group by condition (dist > 5, keep < 3)
+-----------------------------+-----------------------------+------------+--------+
| Time| __endTime|满足条件个数|最大速度|
+-----------------------------+-----------------------------+------------+--------+
|2024-02-01T08:47:48.000+08:00|2024-02-01T08:47:48.000+08:00| 1| 34.0|
|2024-02-03T08:06:20.000+08:00|2024-02-03T08:06:20.000+08:00| 1| 51.0|
|2024-02-05T03:07:20.000+08:00|2024-02-05T03:07:20.000+08:00| 1| 56.0|
|2024-02-07T19:16:36.000+08:00|2024-02-08T16:35:18.000+08:00| 2| 18.0|
|2024-02-10T02:01:21.000+08:00|2024-02-10T02:01:21.000+08:00| 1| 11.0|
|2024-04-07T13:31:26.000+08:00|2024-04-08T15:32:50.000+08:00| 2| 16.0|
+-----------------------------+-----------------------------+------------+--------+
IoTDB> select * FROM root.qd.laoshan.tbm1
+-----------------------------+------------------------+-------------------------+--------------------------+
| Time|root.qd.laoshan.tbm1.tor|root.qd.laoshan.tbm1.dist|root.qd.laoshan.tbm1.speed|
+-----------------------------+------------------------+-------------------------+--------------------------+
|2024-01-01T05:57:43.000+08:00| 39.0| 4.0| 45.0|
|2024-01-02T21:19:35.000+08:00| 30.0| 2.0| 37.0|
|2024-01-03T06:48:54.000+08:00| 37.0| 0.0| 20.0|
|2024-01-04T09:44:23.000+08:00| 27.0| 0.0| 11.0|
【点数分段聚合】
用法：根据点数分组进行聚合运算，将连续的指定数量数据点分为一组，即按照固定的点数进行分组
group by count(物理量，点数，空值处理)
select __endTime, count(dist) from root.qd.laoshan.tbm1 where time < 2024-06-01T00:00:00 group by count (dist, 5)
IoTDB> select __endTime, count(dist),sum(speed) as 速度总和, min_value (tor) as 最小力矩 from root.qd.laoshan.tbm1 where time < 2024-06-01T00:00:00 group by count (dist, 10)
+-----------------------------+-----------------------------+--------------------------------+--------+--------+
| Time| __endTime|count(root.qd.laoshan.tbm1.dist)|速度总和|最小力矩|
+-----------------------------+-----------------------------+--------------------------------+--------+--------+
|2024-01-01T05:57:43.000+08:00|2024-01-10T09:11:00.000+08:00| 10| 337.0| 25.0|
|2024-01-11T11:23:52.000+08:00|2024-01-20T07:57:55.000+08:00| 10| 339.0| 20.0|
|2024-01-21T16:16:08.000+08:00|2024-01-30T20:03:10.000+08:00| 10| 380.0| 23.0|
|2024-01-31T23:38:39.000+08:00|2024-02-09T06:07:14.000+08:00| 10| 360.0| 21.0|
|2024-02-10T02:01:21.000+08:00|2024-02-19T09:54:22.000+08:00| 10| 379.0| 23.0|
|2024-02-20T14:40:19.000+08:00|2024-02-29T22:00:36.000+08:00| 10| 354.0| 20.0|
|2024-03-01T14:17:30.000+08:00|2024-03-10T09:06:43.000+08:00| 10| 331.0| 28.0|
|2024-03-11T04:16:57.000+08:00|2024-03-20T09:21:02.000+08:00| 10| 366.0| 20.0|
|2024-03-21T00:44:23.000+08:00|2024-03-30T11:15:47.000+08:00| 10| 409.0| 25.0|
|2024-03-31T15:51:16.000+08:00|2024-04-09T23:28:33.000+08:00| 10| 281.0| 20.0|
|2024-04-10T06:24:58.000+08:00|2024-04-19T12:22:53.000+08:00| 10| 437.0| 20.0|
|2024-04-20T07:43:33.000+08:00|2024-04-29T07:36:04.000+08:00| 10| 378.0| 21.0|
|2024-04-30T09:40:16.000+08:00|2024-05-09T06:25:52.000+08:00| 10| 387.0| 20.0|
|2024-05-10T05:37:27.000+08:00|2024-05-19T03:14:12.000+08:00| 10| 355.0| 20.0|
|2024-05-20T03:07:25.000+08:00|2024-05-29T12:01:43.000+08:00| 10| 346.0| 21.0|
+-----------------------------+-----------------------------+--------------------------------+--------+--------+
【设备分组查询】
【普通查询】
查询结果集默认按照时间对齐，包含一列时间列和若干个值列，结果表里每一行数据各列的时间戳相同。
select * from root.qd.laoshan.** where time > 2024-05-01T00:00:00 and time < 2024-08-01T00:00:00;
select speed,info from root.qd.laoshan.** where time > 2024-05-01T00:00:00 and time < 2024-08-01T00:00:00;
【align by device】
设备名会单独作为一列出现，查询结果集包含一列时间列、一列设备列和若干个值列。如果 SELECT 子句中选择了 N 列，则结果集包含 N + 2 列（时间列和设备名字列）
在展示的结果内，Time + Device名称作为key，标志一条数据
IoTDB> select * from root.qd.laoshan.** where time > 2024-05-01T00:00:00 and time < 2024-08-01T00:00:00 align by device
+-----------------------------+--------------------+------+----+-----+------+
| Time| Device| tor|dist|speed|status|
+-----------------------------+--------------------+------+----+-----+------+
|2024-05-01T18:42:25.000+08:00|root.qd.laoshan.tbm1| 33.0| 0.0| 44.0| null|
|2024-05-02T07:04:03.000+08:00|root.qd.laoshan.tbm1| 20.0| 4.0| 28.0| null|
|2024-05-03T04:17:50.000+08:00|root.qd.laoshan.tbm1| 33.0| 3.0| 58.0| null|
|2024-05-04T00:48:07.000+08:00|root.qd.laoshan.tbm1| 20.0| 2.0| 44.0| null|
|2024-05-05T20:33:11.000+08:00|root.qd.laoshan.tbm1| 27.0| 2.0| 51.0| null|
|2024-05-06T17:57:49.000+08:00|root.qd.laoshan.tbm1| 23.0| 0.0| 23.0| null|
|2024-05-07T22:05:29.000+08:00|root.qd.laoshan.tbm1| 39.0| 3.0| 55.0| null|
|2024-05-08T08:16:38.000+08:00|root.qd.laoshan.tbm1| 38.0| 4.0| 41.0| null|
|2024-05-09T06:25:52.000+08:00|root.qd.laoshan.tbm1| 20.0| 4.0| 26.0| null|
|2024-05-10T05:37:27.000+08:00|root.qd.laoshan.tbm1| 37.0| 4.0| 48.0| null|
|2024-05-11T09:24:29.000+08:00|root.qd.laoshan.tbm1| 40.0| 0.0| 43.0| null|
|2024-05-12T05:39:45.000+08:00|root.qd.laoshan.tbm1| 28.0| 0.0| 45.0| null|
【缺失值查询】
数据准备:
insert into root.qd.laoshan.tbm2 (timestamp, speed, tor) values (2024-05-09 12:03:00, 83.0, 1300)
insert into root.qd.laoshan.tbm2 (timestamp, tor,status) values (2024-05-09 12:04:00, 1400,false)
insert into root.qd.laoshan.tbm2 (timestamp, speed) values (2024-05-09 12:05:00, 85.0)
insert into root.qd.laoshan.tbm2 (timestamp, speed, tor,status) values (2024-05-09 12:06:00, 86.0, 1500,true)
填充查询的缺失值 fill用途：用某些值代替select 显示结果中为null的值
【1.使用缺失值的前一个时刻点填充数据:】
select * from root.qd.laoshan.tbm2
Time |root.qd.laoshan.tbm2.tor|root.qd.laoshan.tbm2.speed|root.qd.laoshan.tbm2.status|
-----------------------+------------------------+--------------------------+---------------------------+
2024-05-09 12:03:00.000| 1300.0| 83.0| |
2024-05-09 12:04:00.000| 1400.0| |false |
2024-05-09 12:05:00.000| | 85.0| |
2024-05-09 12:06:00.000| 1500.0| 86.0|true |
select * from root.qd.laoshan.tbm2 fill (previous); # 用上一个时刻点的值来填充本行位空的值
Time |root.qd.laoshan.tbm2.tor|root.qd.laoshan.tbm2.speed|root.qd.laoshan.tbm2.status|
-----------------------+------------------------+--------------------------+---------------------------+
2024-05-09 12:03:00.000| 1300.0| 83.0| |
2024-05-09 12:04:00.000| 1400.0| 83.0|false |
2024-05-09 12:05:00.000| 1400.0| 85.0|false |
2024-05-09 12:06:00.000| 1500.0| 86.0|true |
select * from root.qd.laoshan.tbm2 fill (previous, 5s);# 前5秒时刻的值来填充
Time |root.qd.laoshan.tbm2.tor|root.qd.laoshan.tbm2.speed|root.qd.laoshan.tbm2.status|
-----------------------+------------------------+--------------------------+---------------------------+
2024-05-09 12:03:00.000| 1300.0| 83.0| |
2024-05-09 12:04:00.000| 1400.0| |false |
2024-05-09 12:05:00.000| | 85.0| |
2024-05-09 12:06:00.000| 1500.0| 86.0|true |
select * from root.qd.laoshan.tbm2 fill (previous, 1m); # 前1分钟时刻的值来填充
select * from root.qd.laoshan.tbm2 fill (previous, 59s);
【2.使用前一个非空值和下一个非空值的线性插值】
select speed,tor from root.qd.laoshan.tbm2
Time |root.qd.laoshan.tbm2.speed|root.qd.laoshan.tbm2.tor|
-----------------------+--------------------------+------------------------+
2024-05-09 12:03:00.000| 83.0| 1300.0|
2024-05-09 12:04:00.000| | 1400.0|
2024-05-09 12:05:00.000| 85.0| |
2024-05-09 12:06:00.000| 86.0| 1500.0|
select speed,tor from root.qd.laoshan.tbm2 fill(LINEAR);
Time |root.qd.laoshan.tbm2.speed|root.qd.laoshan.tbm2.tor|
-----------------------+--------------------------+------------------------+
2024-05-09 12:03:00.000| 83.0| 1300.0|
2024-05-09 12:04:00.000| 84.0| 1400.0|
2024-05-09 12:05:00.000| 85.0| 1450.0|
2024-05-09 12:06:00.000| 86.0| 1500.0|
select * from root.qd.laoshan.tbm2 fill(LINEAR);
Time |root.qd.laoshan.tbm2.tor|root.qd.laoshan.tbm2.speed|root.qd.laoshan.tbm2.status|
-----------------------+------------------------+--------------------------+---------------------------+
2024-05-09 12:03:00.000| 1300.0| 83.0| |
2024-05-09 12:04:00.000| 1400.0| 84.0|false |
2024-05-09 12:05:00.000| 1450.0| 85.0| |
2024-05-09 12:06:00.000| 1500.0| 86.0|true |
【3.常量填充】
如果某列数据类型与常量类型不兼容，既不填充该列，也不报错，将该列保持原样。
select * from root.qd.laoshan.tbm2
Time |root.qd.laoshan.tbm2.tor|root.qd.laoshan.tbm2.speed|root.qd.laoshan.tbm2.status|
-----------------------+------------------------+--------------------------+---------------------------+
2024-05-09 12:03:00.000| 1300.0| 83.0| |
2024-05-09 12:04:00.000| 1400.0| |false |
2024-05-09 12:05:00.000| | 85.0| |
2024-05-09 12:06:00.000| 1500.0| 86.0|true |
select * from root.qd.laoshan.tbm2 fill(1500);
Time |root.qd.laoshan.tbm2.tor|root.qd.laoshan.tbm2.speed|root.qd.laoshan.tbm2.status|
-----------------------+------------------------+--------------------------+---------------------------+
2024-05-09 12:03:00.000| 1300.0| 83.0| |
2024-05-09 12:04:00.000| 1400.0| 1500.0|false |
2024-05-09 12:05:00.000| 1500.0| 85.0| |
2024-05-09 12:06:00.000| 1500.0| 86.0|true |
select * from root.qd.laoshan.tbm2 fill(0)
Time |root.qd.laoshan.tbm2.tor|root.qd.laoshan.tbm2.speed|root.qd.laoshan.tbm2.status|
-----------------------+------------------------+--------------------------+---------------------------+
2024-05-09 12:03:00.000| 1300.0| 83.0| |
2024-05-09 12:04:00.000| 1400.0| 0.0|false |
2024-05-09 12:05:00.000| 0.0| 85.0| |
2024-05-09 12:06:00.000| 1500.0| 86.0|true |
select * from root.qd.laoshan.tbm2 fill(true)
Time |root.qd.laoshan.tbm2.tor|root.qd.laoshan.tbm2.speed|root.qd.laoshan.tbm2.status|
-----------------------+------------------------+--------------------------+---------------------------+
2024-05-09 12:03:00.000| 1300.0| 83.0|true |
2024-05-09 12:04:00.000| 1400.0| |false |
2024-05-09 12:05:00.000| | 85.0|true |
2024-05-09 12:06:00.000| 1500.0| 86.0|true |
【4.支持的数据类型】
PREVIOUS填充：使用该列前一个非空值进行填充。LINEAR填充：使用该列前一个非空值和下一个非空值的线性插值进行填充。常量填充：使用指定常量填充。
【5.支持聚合查询填充】
select SUM(speed) from root.qd.laoshan.tbm2 group by ([2024-05-09 12:03:00, 2024-05-09 12:10:00), 2m);
Time |SUM(root.qd.laoshan.tbm2.speed)|
-----------------------+-------------------------------+
2024-05-09 12:03:00.000| 83.0|
2024-05-09 12:05:00.000| 171.0|
2024-05-09 12:07:00.000| |
2024-05-09 12:09:00.000| |
select SUM(speed) from root.qd.laoshan.tbm2 group by ([2024-05-09 12:03:00, 2024-05-09 12:10:00), 2m) FILL (previous);
Time |SUM(root.qd.laoshan.tbm2.speed)|
-----------------------+-------------------------------+
2024-05-09 12:03:00.000| 83.0|
2024-05-09 12:05:00.000| 171.0|
2024-05-09 12:07:00.000| 171.0|
2024-05-09 12:09:00.000| 171.0|
【6.填充结果写回数据库】
【6.1 普通查询填充结果回写数据库】
通过fill填充完成后，调用into子句，将填充的结果写回数据库另外的存储表里
select speed,tor from root.qd.laoshan.tbm2
Time |root.qd.laoshan.tbm2.speed|root.qd.laoshan.tbm2.tor|
-----------------------+--------------------------+------------------------+
2024-05-09 12:03:00.000| 83.0| 1300.0|
2024-05-09 12:04:00.000| | 1400.0|
2024-05-09 12:05:00.000| 85.0| |
2024-05-09 12:06:00.000| 86.0| 1500.0|
select speed,tor from root.qd.laoshan.tbm2 fill (linear);
Time |root.qd.laoshan.tbm2.speed|root.qd.laoshan.tbm2.tor|
-----------------------+--------------------------+------------------------+
2024-05-09 12:03:00.000| 83.0| 1300.0|
2024-05-09 12:04:00.000| 84.0| 1400.0|
2024-05-09 12:05:00.000| 85.0| 1450.0|
2024-05-09 12:06:00.000| 86.0| 1500.0|
select speed,tor into root.qd.laoshan.tbm22 (speed,tor) from root.qd.laoshan.tbm2 fill (linear);
SourceColumn |TargetTimeseries |Written|
--------------------------+---------------------------+-------+
root.qd.laoshan.tbm2.speed|root.qd.laoshan.tbm22.speed| 4|
root.qd.laoshan.tbm2.tor |root.qd.laoshan.tbm22.tor | 4|
select speed,tor from root.qd.laoshan.tbm22
Time |root.qd.laoshan.tbm22.speed|root.qd.laoshan.tbm22.tor|
-----------------------+---------------------------+-------------------------+
2024-05-09 12:03:00.000| 83.0| 1300.0|
2024-05-09 12:04:00.000| 84.0| 1400.0|
2024-05-09 12:05:00.000| 85.0| 1450.0|
2024-05-09 12:06:00.000| 86.0| 1500.0|
按时间对齐（默认）：全部 into跟随的字段，包含的目标序列数量要与查询结果集的列数（除时间列外）一致，且按照表头从左到右的顺序一一对应。
【6.2 聚合查询回写数据库】
select avg(speed), count(speed) from root.qd.laoshan.tbm1 group by ([2024-02-01T00:00:00, 2024-06-01T00:00:00), 2d,7d) order by time desc
Time |avg(root.qd.laoshan.tbm1.speed)|count(root.qd.laoshan.tbm1.speed)|
-----------------------+-------------------------------+---------------------------------+
2024-05-30 00:00:00.000| 38.0| 2|
2024-05-23 00:00:00.000| 45.5| 2|
2024-05-16 00:00:00.000| 22.0| 2|
2024-05-09 00:00:00.000| 37.0| 2|
2024-05-02 00:00:00.000| 43.0| 2|
2024-04-25 00:00:00.000| 38.0| 2|
2024-04-18 00:00:00.000| 41.5| 2|
2024-04-11 00:00:00.000| 45.0| 2|
2024-04-04 00:00:00.000| 29.5| 2|
2024-03-28 00:00:00.000| 36.5| 2|
2024-03-21 00:00:00.000| 38.0| 2|
2024-03-14 00:00:00.000| 24.5| 2|
2024-03-07 00:00:00.000| 47.0| 2|
2024-02-29 00:00:00.000| 39.5| 2|
2024-02-22 00:00:00.000| 31.5| 2|
2024-02-15 00:00:00.000| 53.5| 2|
2024-02-08 00:00:00.000| 35.0| 2|
2024-02-01 00:00:00.000| 44.5| 2|
将聚合查询结果写回
select avg(speed), count(speed) into root.qd.laoshan.tbm11 (平均速度,数量) from root.qd.laoshan.tbm1 group by ([2024-02-01T00:00:00, 2024-06-01T00:00:00), 2d,7d) order by time desc
SourceColumn |TargetTimeseries |Written|
---------------------------------+--------------------------+-------+
avg(root.qd.laoshan.tbm1.speed) |root.qd.laoshan.tbm11.平均速度| 18|
count(root.qd.laoshan.tbm1.speed)|root.qd.laoshan.tbm11.数量 | 18|
select * from root.qd.laoshan.tbm11
Time |root.qd.laoshan.tbm11.数量|root.qd.laoshan.tbm11.平均速度|
-----------------------+------------------------+--------------------------+
2024-02-01 00:00:00.000| 2| 44.5|
2024-02-08 00:00:00.000| 2| 35.0|
2024-02-15 00:00:00.000| 2| 53.5|
2024-02-22 00:00:00.000| 2| 31.5|
2024-02-29 00:00:00.000| 2| 39.5|
2024-03-07 00:00:00.000| 2| 47.0|
2024-03-14 00:00:00.000| 2| 24.5|
2024-03-21 00:00:00.000| 2| 38.0|
2024-03-28 00:00:00.000| 2| 36.5|
2024-04-04 00:00:00.000| 2| 29.5|
2024-04-11 00:00:00.000| 2| 45.0|
2024-04-18 00:00:00.000| 2| 41.5|
2024-04-25 00:00:00.000| 2| 38.0|
2024-05-02 00:00:00.000| 2| 43.0|
2024-05-09 00:00:00.000| 2| 37.0|
2024-05-16 00:00:00.000| 2| 22.0|
2024-05-23 00:00:00.000| 2| 45.5|
2024-05-30 00:00:00.000| 2| 38.0|
提示：
先不带into子句，查询当前的结果内容
然后按照结果内容，在into子句后面，手动写入目标序列名称
【分页查询】
【1.分页查询limit、offset】
用途：当查询结果集数据量很大，放在一个页面不利于显示，可以使用 LIMIT/SLIMIT 子句和 OFFSET/SOFFSET 子句进行分页控制。
select * from root.qd.laoshan.tbm1
默认按照时间升序，只查看10行数据
select * from root.qd.laoshan.tbm1 limit 10
select * from root.qd.laoshan.tbm1 limit 10 offset 50
select * from root.qd.laoshan.tbm1 order by time desc limit 10
select avg(speed) from root.qd.laoshan.tbm1 group by ([2024-02-01T00:00:00, 2024-06-01T00:00:00), 2d,7d) order by time desc limit 3
【2.分页查询slimit、soffset按列分页查询】
用途：当查询结果集数据量很大，放在一个页面不利于显示，可以使用 LIMIT/SLIMIT 子句和 OFFSET/SOFFSET 子句进行分页控制。
select * from root.qd.laoshan.tbm1
select * from root.qd.laoshan.tbm1 limit 5
select * from root.qd.laoshan.tbm1 limit 5 slimit 2
select * from root.qd.laoshan.tbm1 limit 5 slimit 2 soffset 1
slimit 控制查询的列的个数
3.0|
2024-05-09 00:00:00.000| 2| 37.0|
2024-05-16 00:00:00.000| 2| 22.0|
2024-05-23 00:00:00.000| 2| 45.5|
2024-05-30 00:00:00.000| 2| 38.0|
提示：
先不带into子句，查询当前的结果内容
然后按照结果内容，在into子句后面，手动写入目标序列名称
#### 【分页查询】
##### <u>【1.分页查询limit、offset】</u>
<u>用途：当查询结果集数据量很大，放在一个页面不利于显示，可以使用 LIMIT/SLIMIT 子句和 OFFSET/SOFFSET 子句进行分页控制。</u>
<u>select * from root.qd.laoshan.tbm1</u>
<u>默认按照时间升序，只查看10行数据</u>
<u>select * from root.qd.laoshan.tbm1 limit 10</u>
<u>select * from root.qd.laoshan.tbm1 limit 10 offset 50</u>
<u>select * from root.qd.laoshan.tbm1 order by time desc limit 10</u>
<u>select avg(speed) from root.qd.laoshan.tbm1 group by ([2024-02-01T00:00:00, 2024-06-01T00:00:00), 2d,7d) order by time desc limit 3</u>
##### 【2.分页查询slimit、soffset按列分页查询】
<u>用途：当查询结果集数据量很大，放在一个页面不利于显示，可以使用 LIMIT/SLIMIT 子句和 OFFSET/SOFFSET 子句进行分页控制。</u>
<u>select * from root.qd.laoshan.tbm1</u>
<u>select * from root.qd.laoshan.tbm1 limit 5</u>
<u>select * from root.qd.laoshan.tbm1 limit 5 slimit 2</u>
<u>select * from root.qd.laoshan.tbm1 limit 5 slimit 2 soffset 1</u>
> slimit 控制查询的列的个数
作者：zxDD敲代码
