Tdenginee CQ

-- 1.设备主状态 status
CREATE STABLE IF NOT EXISTS stihl_status_stb (ts TIMESTAMP, val TINYINT) TAGS (sn BINARY(64),group_id INT);
-- 2.建图子状态 status_mapping
CREATE STABLE IF NOT EXISTS stihl_status_mapping_stb (ts TIMESTAMP, val TINYINT) TAGS (sn BINARY(64),group_id INT);
-- 3.故障码 fault
CREATE STABLE IF NOT EXISTS stihl_fault_stb (ts TIMESTAMP, val INT) TAGS (sn BINARY(64),group_id INT);
-- 4.剩余电量 bat_soc
CREATE STABLE IF NOT EXISTS stihl_bat_soc_stb (ts TIMESTAMP, val TINYINT) TAGS (sn BINARY(64),group_id INT);
-- 5.雨淋开关 rain_delay_enable
CREATE STABLE IF NOT EXISTS stihl_rain_delay_enable_stb (ts TIMESTAMP, val BOOL) TAGS (sn BINARY(64),group_id INT);
-- 6.雨淋延时 rain_delay_time
CREATE STABLE IF NOT EXISTS stihl_rain_delay_time_stb (ts TIMESTAMP, val INT) TAGS (sn BINARY(64),group_id INT);
-- 7.区域总面积 work_area_sq
CREATE STABLE IF NOT EXISTS stihl_work_area_sq_stb (ts TIMESTAMP, val INT) TAGS (sn BINARY(64),group_id INT);
-- 8.当日工作时长 day_work_time
CREATE STABLE IF NOT EXISTS stihl_day_work_time_stb (ts TIMESTAMP, val INT) TAGS (sn BINARY(64),group_id INT);
-- 9.当日作业面积 day_work_sq
CREATE STABLE IF NOT EXISTS stihl_day_work_sq_stb (ts TIMESTAMP, val INT) TAGS (sn BINARY(64),group_id INT);
-- 10.本周时长 week_work_time
CREATE STABLE IF NOT EXISTS stihl_week_work_time_stb (ts TIMESTAMP, val INT) TAGS (sn BINARY(64),group_id INT);
-- 11.本周面积 week_work_sq
CREATE STABLE IF NOT EXISTS stihl_week_work_sq_stb (ts TIMESTAMP, val INT) TAGS (sn BINARY(64),group_id INT);
-- 12.当前任务时长 task_work_time
CREATE STABLE IF NOT EXISTS stihl_task_work_time_stb (ts TIMESTAMP, val INT) TAGS (sn BINARY(64),group_id INT);
-- 13.当前任务面积 task_work_sq
CREATE STABLE IF NOT EXISTS stihl_task_work_sq_stb (ts TIMESTAMP, val INT) TAGS (sn BINARY(64),group_id INT);
-- 14.累计总时长 total_work_time
CREATE STABLE IF NOT EXISTS stihl_total_work_time_stb (ts TIMESTAMP, val INT) TAGS (sn BINARY(64),group_id INT);
-- 15.累计总面积 total_work_sq
CREATE STABLE IF NOT EXISTS stihl_total_work_sq_stb (ts TIMESTAMP, val INT) TAGS (sn BINARY(64),group_id INT);
-- 16.任务类型 task_type
CREATE STABLE IF NOT EXISTS stihl_task_type_stb (ts TIMESTAMP, val TINYINT) TAGS (sn BINARY(64),group_id INT);

CREATE STABLE IF NOT EXISTS stihl_device_runtime_stb (
ts TIMESTAMP,
dev_status TINYINT,
map_sub_status TINYINT,
fault_code INT,
bat_soc TINYINT,
rain_delay_enable BOOL,
rain_delay_time INT,
work_area_sq INT,
day_work_time INT,
day_work_sq INT,
week_work_time INT,
week_work_sq INT,
task_work_time INT,
task_work_sq INT,
total_work_time INT,
total_work_sq INT,
task_type TINYINT
) TAGS (
sn BINARY(64),
group_id INT
);

CREATE CONTINUOUS QUERY cq_stihl_status INTO stihl_device_runtime_stb(ts, dev_status) AS SELECT TS, LAST(val) AS dev_status FROM stihl_status_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_status_mapping INTO stihl_device_runtime_stb(ts, map_sub_status) AS SELECT TS, LAST(val) AS map_sub_status FROM stihl_status_mapping_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_fault INTO stihl_device_runtime_stb(ts, fault_code) AS SELECT TS, LAST(val) AS fault_code FROM stihl_fault_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_bat_soc INTO stihl_device_runtime_stb(ts, bat_soc) AS SELECT TS, LAST(val) AS bat_soc FROM stihl_bat_soc_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_rain_delay_enable INTO stihl_device_runtime_stb(ts, rain_delay_enable) AS SELECT TS, LAST(val) AS rain_delay_enable FROM stihl_rain_delay_enable_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_rain_delay_time INTO stihl_device_runtime_stb(ts, rain_delay_time) AS SELECT TS, LAST(val) AS rain_delay_time FROM stihl_rain_delay_time_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_work_area_sq INTO stihl_device_runtime_stb(ts, work_area_sq) AS SELECT TS, LAST(val) AS work_area_sq FROM stihl_work_area_sq_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_day_work_time INTO stihl_device_runtime_stb(ts, day_work_time) AS SELECT TS, LAST(val) AS day_work_time FROM stihl_day_work_time_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_day_work_sq INTO stihl_device_runtime_stb(ts, day_work_sq) AS SELECT TS, LAST(val) AS day_work_sq FROM stihl_day_work_sq_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_week_work_time INTO stihl_device_runtime_stb(ts, week_work_time) AS SELECT TS, LAST(val) AS week_work_time FROM stihl_week_work_time_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_week_work_sq INTO stihl_device_runtime_stb(ts, week_work_sq) AS SELECT TS, LAST(val) AS week_work_sq FROM stihl_week_work_sq_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_task_work_time INTO stihl_device_runtime_stb(ts, task_work_time) AS SELECT TS, LAST(val) AS task_work_time FROM stihl_task_work_time_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_task_work_sq INTO stihl_device_runtime_stb(ts, task_work_sq) AS SELECT TS, LAST(val) AS task_work_sq FROM stihl_task_work_sq_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_total_work_time INTO stihl_device_runtime_stb(ts, total_work_time) AS SELECT TS, LAST(val) AS total_work_time FROM stihl_total_work_time_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_total_work_sq INTO stihl_device_runtime_stb(ts, total_work_sq) AS SELECT TS, LAST(val) AS total_work_sq FROM stihl_total_work_sq_stb GROUP BY sn, group_id, INTERVAL(1s);
CREATE CONTINUOUS QUERY cq_stihl_task_type INTO stihl_device_runtime_stb(ts, task_type) AS SELECT TS, LAST(val) AS task_type FROM stihl_task_type_stb GROUP BY sn, group_id, INTERVAL(1s);

SHOW CONTINUOUS QUERIES;
DROP CONTINUOUS QUERY IF EXISTS cq_stihl_status;

tdengine:
url: jdbc:TAOS-RS://10.0.2.131:6041/stihl_iot
username: root
password: taosdata
batch:
enabled: true
mode: high-performance
flush-interval: 500

INSERT INTO {childTableName} USING {stableName} TAGS(?,?) VALUES(?,?)
场景：新增电机温度 motor_temp

新建窄超级表

sql

CREATE STABLE IF NOT EXISTS stihl_motor_temp_stb (ts TIMESTAMP, val INT) TAGS (sn BINARY(64),group_id INT);

宽表追加字段

sql

ALTER STABLE stihl_device_runtime_stb ADD COLUMN motor_temp INT;

创建 CQ

sql

CREATE CONTINUOUS QUERY cq_stihl_motor_temp
INTO stihl_device_runtime_stb(ts, motor_temp)
AS SELECT TS, LAST(val) AS motor_temp
FROM stihl_motor_temp_stb
GROUP BY sn, group_id, INTERVAL(1s);

Java 枚举 DeviceMetricEnum 添加配置
VO、查询 SQL、前端适配
可选：离线脚本回填 CQ 创建前历史数据