背景
其实，到现在为止，前面的页面已经实现了对设备数据的展现。这一篇文章中对当前的项目做一个增强：
- 绘制
Echarts图表展示数据变化趋势； - 导出设备数据到
Excel，体验TDengine的查询效率；
这两个功能的实现均需要前后端配合，完成数据交互，我们先实现后端接口，然后在前端调用。
绘制Echarts图表
人毕竟是视觉动物，通过图表对数据的可视化人们可以直观发现数据中隐藏的密码；数据技术时代， Echarts 数据可视化将是你的必备技能。
测试数据
打开 taos 客户端，通过命令行插入以下数据（数据来源于城市西安下的一个设备device1，共16条数据），用在 Echarts 绘图。
insert into iot.device1 values("2021-11-11 14:05:11.138", 225, 9.475, 13.52135);
insert into iot.device1 values("2021-11-11 14:05:41.138", 208, 6.44464, 0.99233);
insert into iot.device1 values("2021-11-11 14:06:11.138", 237, 8.77516, 1.33402);
insert into iot.device1 values("2021-11-11 14:06:41.138", 222, 9.52215, 9.24064);
insert into iot.device1 values("2021-11-11 14:07:11.138", 231, 9.46184, 27.11575);
insert into iot.device1 values("2021-11-11 14:07:41.138", 224, 1.08054, 19.80646);
insert into iot.device1 values("2021-11-11 14:08:11.138", 235, 9.41199, 29.77552);
insert into iot.device1 values("2021-11-11 14:08:41.138", 238, 9.17465, 28.60737);
insert into iot.device1 values("2021-11-11 14:09:11.138", 225, 9.475, 13.52135);
insert into iot.device1 values("2021-11-11 14:09:41.138", 208, 6.44464, 0.99233);
insert into iot.device1 values("2021-11-11 14:10:11.138", 237, 8.77516, 1.33402);
insert into iot.device1 values("2021-11-11 14:10:41.138", 222, 9.52215, 9.24064);
insert into iot.device1 values("2021-11-11 14:11:11.138", 231, 9.46184, 27.11575);
insert into iot.device1 values("2021-11-11 14:11:41.138", 224, 1.08054, 19.80646);
insert into iot.device1 values("2021-11-11 14:12:11.138", 235, 9.41199, 29.77552);
insert into iot.device1 values("2021-11-11 14:12:41.138", 238, 9.17465, 28.60737);
后端接口
通过 TDengine 的降采样与插值，完成在指定时间范围内的设备数据的变化趋势查询。实际应用场景中可能涉及更多维度的统计分析。
- PowerController.java
/**
* 降采样：查询设备数据趋势
* @param params
* @return
*/
@GetMapping("getDeviceDataTrend")
public Result getDeviceDataTrend(@RequestParam Map<String, Object> params) {
String sn = (String) params.get("sn");
String startTime = (String) params.get("startTime");
String endTime = (String) params.get("endTime");
List<Map<String, Object>> deviceDataTrend = powerMapper.getDeviceDataTrend(sn, startTime, endTime);
List<Object> axisX = new ArrayList<>();
List<Object> voltage = new ArrayList<>();
List<Object> currente = new ArrayList<>();;
List<Object> temperature = new ArrayList<>();;
deviceDataTrend.forEach(item -> {
axisX.add(item.get("ts"));
voltage.add(item.get("voltage"));
currente.add(item.get("currente"));
temperature.add(item.get("temperature"));
});
Map<String, Object> result = new HashMap<>();
Map<String, Object> series = new HashMap<>();
series.put("电压", voltage);
series.put("电流", currente);
series.put("温度", temperature);
result.put("axisX", axisX);
result.put("series", series);
return Result.success(result);
}
- PowerMapper.java
List<Map<String, Object>> getDeviceDataTrend(String sn, String startTime, String endTime);
- PowerMapper.xml
这里用到了 TDengine 的降采样与插值函数：interval()与fill()。
<!--查询设备数据趋势-->
<select id="getDeviceDataTrend" resultType="java.util.Map">
select last(voltage) voltage, last(currente) currente, last(temperature) temperature from iot.power
where sn = #{sn} and ts > #{startTime} and ts < #{endTime}
interval(60s) fill(value, 0);
</select>
前端页面
- 在
components目录下新建一个PowerTrend.vue。
<template>
<div class="ch-body">
<div class="chart" id="trend"></div>
</div>
</template>
<script>
import * as echarts from "echarts";
import axios from "axios";
export default {
name: "PowerTrend",
props: ["sn"],
data() {
return {
option: null,
xAxisData: [],
categories: ["电压", "电流", "温度"],
series: [],
};
},
mounted() {
this.getData();
},
methods: {
getData() {
axios
.get(
`/api/power/getDeviceDataTrend?startTime=2021-11-11 14:05:11.138&endTime=2021-11-11 14:13:11.138`,
{
params: { sn: this.sn },
}
)
.then((res) => {
if (res.data) {
this.xData = res.data.data.axisX;
let seriesData = res.data.data.series;
for (const category of this.categories) {
this.series.push({
name: category,
type: "line",
data: seriesData[category].map((x) => x.toFixed(2)),
areaStyle: {},
smooth: true,
});
}
this.getEchart();
}
})
.catch((err) => {
console.log(err);
});
},
getEchart() {
let myChart = echarts.init(document.getElementById("trend"));
this.option = {
color: ["#00c86c", "#ff9235", "#1b40e3"],
tooltip: {
trigger: "axis",
axisPointer: {
type: "cross",
},
// tooltip透明背景色
backgroundColor: "rgba(0,0,0,0.8)",
borderColor: "gray",
// tooltip里的文字左对齐
textStyle: {
align: "left",
color: "#FFFFFF",
},
},
legend: {
data: this.categories,
// top:"bottom",
left: "center",
icon: "diamond",
},
grid: {
left: "3%",
right: "4%",
bottom: "3%",
containLabel: true,
},
xAxis: {
type: "category",
boundaryGap: false,
data: this.xData,
axisLabel: {
formatter: "{value}",
color: "#000000",
},
},
yAxis: {
type: "value",
axisLabel: {
color: "#000000",
},
axisLine: {
show: true,
lineStyle: {
color: "#999",
},
},
},
series: this.series,
};
myChart.setOption(this.option, true);
window.addEventListener("resize", () => {
myChart.resize();
});
},
},
beforeUnmount() {
console.log("Unmout!");
},
};
</script>
<style lang="scss" scoped>
.chart {
width: 100%;
height: 350px;
}
</style>
然后在 PowerView.vue 中引入即可。注意接口调用时传入的时间段参数： /api/power/getDeviceDataTrend?startTime=2021-11-11 14:05:11.138&endTime=2021-11-11 14:13:11.138 , 我是为了使用测试数据（所在的时间范围）绘图特意置了时间段，实际根据需要传入数据范围即可。
Reference
If you have any questions or any bugs are found, please feel free to contact me.
Your comments and suggestions are welcome!
