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;
    }
}