QueryBuilder写influxdb查询语句
1.demo
//按小时和指定字段获取每小时中的最大值
SelectQueryImpl selectQuery = select()
.max("horiValue").as("horiValue")
.max("vertValue").as("vertValue")
.from("jsnl","static_deflection")
.where(gte("time",startTimeStr)).and(lt("time",endTimeStr)).
and(eq("bridgeName",bridgeName))
.groupBy(time(1L,HOUR),"pointGroup","stationName").fill(0)
.tz("Asia/Shanghai").orderBy(asc());
max()
聚合函数求最大值
as
取别名
from()
from（"数据库名","表名"）
where()
加查询条件，gte大于等于，lt小于等于
groupby
例子中将时间按1小时进行分组，多个分组条件用,隔开
fill()
fill()用于填充没有数据的时序序列的值，如缺失01点的数据，则将01这个时间点的数据填充为0
例如
time mean
---- ----
2021-09-17T00:00:00Z 2.2341273798866914
2021-09-17T01:00:00Z 0
2021-09-17T02:00:00Z 2.7063496857597222
2021-09-17T03:00:00Z 2.837302089909386
2021-09-17T04:00:00Z 2.1944448331746718
2021-09-17T05:00:00Z 2.400794076077173
2021-09-17T06:00:00Z 1.3485784579888
2021-09-17T07:00:00Z 2.063492429025008
2021-09-17T08:00:00Z 2.198413087845874
2021-09-17T09:00:00Z 2.269841671927509
2021-09-17T10:00:00Z 2.472222660158808
2021-09-17T11:00:00Z 1.9841273356009694
其选项为：
- null: 默认，显示时间戳但value=null的序列；
- none：在结果中不显示该时间戳的序列；
- 数值：fill(0)，填充0；
- linear: 线性插入数值；
- previous: 填充前一个序列的值；
orderBy()
默认为时间升序排序，按时间降序为orderBy(desc())，这里只能按时间排序，若要按其他字段排序，则需要将结果集进行单独按指定字段排序
tz()
通过tz()子句将时序数据的时间戳从UTC时间转换为Asia/Shanghai时区的本地时间
转之前：
time host value
---- ---- -----
2019-11-03T16:08:00Z server01 0.67
转之后：
time host value
---- ---- -----
2019-11-04T00:08:00+08:00 server01 0.67
2.将sql语句进行执行获取结果集
QueryResult query1 = influxDB.query(selectQuery1);
//这里自己写了一个方法去获取数据表中的每一个字段
List<Map<String,Object>> data = InfluxDBUtils.queryResultProcess1(query1);
InfluxDBUtils类
import org.influxdb.dto.QueryResult;
import java.util.*;
public class InfluxDBUtils {
/**
*获取查询结果条数（方便分页）
*/
public static long countResultProcess(QueryResult queryResult,String column) {
long count = 0;
List<Map<String, Object>> list = queryResultProcess(queryResult);
if (list != null && list.size() != 0) {
Map<String, Object> map = list.get(0);
double num = (Double) map.get(column);
count = new Double(num).longValue();
}
return count;
}
/**
* @param queryResult
* @desc 查询结果处理（仅获取想要查询的字段）
* @date 2021/5/12
*/
public static List<Map<String, Object>> queryResultProcess(QueryResult queryResult) {
List<Map<String, Object>> mapList = new ArrayList<>();
List<QueryResult.Result> resultList = queryResult.getResults();
//把查询出的结果集转换成对应的实体对象，聚合成list
for (QueryResult.Result query : resultList) {
List<QueryResult.Series> seriesList = query.getSeries();
if (seriesList != null && seriesList.size() != 0) {
for (QueryResult.Series series : seriesList) {
List<String> columns = series.getColumns();
String[] keys = columns.toArray(new String[columns.size()]);
List<List<Object>> values = series.getValues();
if (values != null && values.size() != 0) {
for (List<Object> value : values) {
Map<String, Object> map = new HashMap(keys.length);
for (int i = 0; i < keys.length; i++) {
map.put(keys[i], value.get(i));
}
mapList.add(map);
}
}
}
}
}
return mapList;
}
/**
* @param queryResult
* @desc 查询结果处理（获取表中所有的字段）
* @date 2021/5/12
*/
public static List<Map<String, Object>> queryResultProcess1(QueryResult queryResult) {
List<Map<String, Object>> mapList = new ArrayList<>();
List<QueryResult.Result> resultList = queryResult.getResults();
//把查询出的结果集转换成对应的实体对象，聚合成list
for (QueryResult.Result query : resultList) {
List<QueryResult.Series> seriesList = query.getSeries();
if (seriesList != null && seriesList.size() != 0) {
for (QueryResult.Series series : seriesList) {
Map<String, Object> map;
List<String> columns = series.getColumns();
Map<String, String> tags = series.getTags();
String[] keys = columns.toArray(new String[columns.size()]);
List<List<Object>> values = series.getValues();
if (values != null && values.size() != 0) {
for (List<Object> value : values) {
map = new HashMap(keys.length);
Set<Map.Entry<String, String>> entries = tags.entrySet();
for (Map.Entry<String, String> entry : entries) {
map.put(entry.getKey(), entry.getValue());
}
for (int i = 0; i < keys.length; i++) {
map.put(keys[i], value.get(i));
}
mapList.add(map);
}
}
}
}
}
return mapList;
}
}
