物联网设备数据流转之数据如何查询：TDengine条件查询, 聚合查询, 分页查询, TopN查询, 降采样

这篇文章基于我们搭建好的后端脚手架，实现后端核心的查询接口，并详细介绍 TDengine 的高级查询功能：海量数据的条件查询、按时间范围查询、聚合查询、分页查询、TopN查询、降采样。根据业务需求，我们涉及以下查询接口。

1. 分页查询
2. 条件查询：查询传入设备组的最新数据
3. 条件、聚合查询：查询指定城市的数据量，数据指标：电压、电流、温度平均值
4. 分页、条件查询：查询传入设备在指定时间范围内的数据
5. TopN查询：见/power/select接口，实际未使用
6. 降采样：将在Echarts数据可视化一节中实现

依赖、配置文件、实体类不变。

@Configuration
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        //TODO: mybatis-plus do not support TDengine, use postgresql Dialect
        paginationInterceptor.setDialectType("postgresql");
        return paginationInterceptor;
    }
}

• PowerMapper.java

@Mapper
@Repository
public interface PowerMapper extends BaseMapper {
    @Insert("insert into device${sn}(ts,voltage,currente,temperature) values(#{ts}, #{voltage}, #{currente}, #{temperature})")
    int insertOne(Power one);
    
    Map getAllDataAvg();
    Map getAllDataAvgInCity(String city);

    List getSubTablesInCity(String city);
    List> getLastValue(List tableNames);
}

Note: Insert 操作这里仅在 Mapper 层添加了一个插入接口，并没有在 Controller 层调用；我们将在 EMQX 的 WebHook 处使用 Spring 事件与监听机制当 WebHook 收到实时数据时执行插入操作。

• PowerMapper.xml

    
        
        
        
        
    

    
        ts, voltage, currente, temperature, city, groupid
    

    
        last(ts) ts, last(voltage) voltage, last(currente) currente, last(temperature) temperature
    

    
        select avg(voltage) voltage, avg(currente) currente, avg(temperature) temperature from iot.power;
    

    
    
        select count(1) total, avg(voltage) voltage, avg(currente) currente, avg(temperature) temperature from iot.power where city=#{city}
    

    
    
        select tbname from iot.power where city=#{city}
    

    
    
        
            select
            
            from iot.#{tbname}
            
                union all
            
        
    

• PowerController.java

@RestController
@RequestMapping("power")
public class PowerController {
    @Autowired
    private PowerMapper powerMapper;

    
    @GetMapping("select")
    public Result selectList() {
        QueryWrapper wrapper = new QueryWrapper<>();
        wrapper.last("limit 10");
        List powerList = powerMapper.selectList(wrapper);
        powerList.forEach(System.out::println);
        return Result.success(powerList);
    }

    
    @GetMapping("total")
    public Result selectCount() {
        int count = powerMapper.selectCount(null);
        return Result.success(count);
    }

    
    @GetMapping("page")
    public Result selectPage() {
        // Test
        QueryWrapper wrapper = new QueryWrapper<>();
        wrapper.eq("sn", 1100);
        wrapper.eq("city", "西安");
        wrapper.eq("groupid", 2);

        IPage page = new Page(1, 3);
        IPage powerIPage = powerMapper.selectPage(page, null);

        System.out.println("total : " + powerIPage.getTotal());
        System.out.println("pages : " + powerIPage.getPages());

        for (Power power : powerIPage.getRecords()) {
            System.out.println(power);
        }
        return Result.success(powerIPage);
    }

    
    @GetMapping("lastValue/{city}")
    public Result getLastValue(@PathVariable String city) {
        List tableNames = powerMapper.getSubTablesInCity(city);
        List> lastValues = powerMapper.getLastValue(tableNames);
        Map result = new HashMap<>();
        result.put("tableNames", tableNames);
        result.put("powers", lastValues);
        return Result.success(result);
    }

    
    @GetMapping("totalInCity/{city}")
    public Result selectCountInCity(@PathVariable String city) {
        Map allDataAvgInCity = powerMapper.getAllDataAvgInCity(city);
        return Result.success(allDataAvgInCity);
    }

    
    @GetMapping("getDeviceData")
    public Result getDeviceData(@RequestParam(defaultValue = "0") Integer page, @RequestParam(defaultValue = "10") Integer size, @RequestParam Map params) {
        QueryWrapper wrapper = new QueryWrapper<>();
//        wrapper.select("")
        wrapper.eq("sn", params.get("sn"));
//        wrapper.between("ts", "2021-09-07 09:15:11.138", "2021-09-07 10:15:11.138");
        wrapper.between("ts", params.get("startTime"), params.get("endTime"));
        wrapper.orderByDesc("ts");
        IPage deviceData = powerMapper.selectPage(new Page<>(page, size), wrapper);
        return Result.success(deviceData);
    }
}

至此，我们已经完成 TDengine 的高级查询功能：

1. 分页查询
2. 条件查询：查询传入设备组的最新数据
3. 条件、聚合查询：查询指定城市的数据量，数据指标：电压、电流、温度平均值
4. 分页、条件查询：查询传入设备在指定时间范围内的数据
5. TopN查询：见/power/select接口，实际未使用（前端未调用）

• https://www.taosdata.com/cn/getting-started/

If you have any questions or any bugs are found, please feel free to contact me.

Your comments and suggestions are welcome!