今天在编写timescale查询代码实现与influxdb查询代码相同时,碰到了聚合函数的问题,在此记录一下。
首先是influxdb
ajdlbd_statistical表里面存储的是ajdlbd站的点位的统计值,如d1Avg表示1天的平均值、m5Min表示五分钟的最小值等,如下截图,查询的是YC173和YC1741点位的统计值
现在需求,将m5Min的值每十五分钟做一个聚合,并按照点位区分开,sql如下,其中 tz('Asia/Shanghai')是将时区改为中国时区
SELECT time,MIN(value) FROM "ajdlbd_statistical" where "time">='2019-12-05 00:00:00' and ("name"= 'YC1743' or "name"= 'YC1741') and "time"<='2019-12-06 14:15:00' and "unit"='m5Min' GROUP BY time(900s) ,"name" tz('Asia/Shanghai')
结果如下,还是挺智能的
结果集按照两个点位分成了两个group
我把处理结果集的Java代码也贴出来了
public List getDatasForForecast(String stationName, String pointName, String begintime, String endtime, String searchType, String timeType) {
List resultList = new ArrayList();
InfluxDB influxDB = InfluxDBUtils.influxDbBuild();
if (influxDB != null) {
String influxdbDBName = InfluxDBUtils.getInfluxdbDBName();
if (influxdbDBName != null) {
String tableName = "\"" + stationName + "_statistical" + "\"";
Query query = null;
String stype = "";
String unitType = "";
String unitTime = "";
String unit = "";
if (searchType.equals("max")) {
stype = "MAX";
unitType = "Max";
} else if (searchType.equals("min")) {
stype = "MIN";
unitType = "Min";
} else if (searchType.equals("avg")) {
stype = "MEAN";
unitType = "Avg";
} else if (searchType.equals("diff")) {
stype = "SUM";
unitType = "Diff";
}
if (timeType.equals("quarter")) {
unitTime = "m5";
unit = unitTime + unitType;
query = new Query("SELECT time," + stype + "(value) FROM " + tableName + " where \"time\">='" + begintime + "' and " + pointName + " and \"time\"<='" + endtime + "' and \"unit\"='" + unit + "' GROUP BY time(900s) ,\"name\" tz('Asia/Shanghai')", influxdbDBName);
} else {
unitTime = "d1";
unit = unitTime + unitType;
query = new Query("SELECT time,\"value\" FROM " + tableName + " where \"time\">='" + begintime + "' and " + pointName + " and \"time\"<='" + endtime + "' and \"unit\"='" + unit + "' GROUP BY \"name\" tz('Asia/Shanghai')", influxdbDBName);
}
QueryResult queryResult = influxDB.query(query);
if (queryResult != null) {
try {
List<QueryResult.Series> series = queryResult.getResults().get(0).getSeries();
if (series != null) {
for (QueryResult.Series s : series) {
Map pointMap = new HashMap();
String rep = stationName + "." + s.getTags().get("name");
List<List<Object>> list = s.getValues();
if (list != null && !list.isEmpty()) {
Map valueMap = new HashMap();
for (List<Object> listO : list) {
String formatTime;
String time = listO.get(0).toString();
if (timeType.equals("quarter")) {
String beformatTime = time.split("\\+")[0].replace("T", " ");
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = format.parse(beformatTime);
Calendar cal = Calendar.getInstance();
cal.setTime(date);
cal.add(Calendar.MINUTE, 15);
date = cal.getTime();
formatTime = format.format(date);
} else {
formatTime = time.split("T")[0];
}
double value = NumberUtils.toDouble(String.valueOf(listO.get(1)));
valueMap.put(formatTime, value);
pointMap.put(rep, valueMap);
}
}
resultList.add(pointMap);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
return resultList;
}
然后是timescaledb,timescale与postgreSQL是一样的
timescaledb主要讲sql的生成过程
1,先查询一下数据
SELECT * FROM "ajdlbd_statistical" WHERE time>='2019-12-05 00:00:00' and time<='2019-12-06 14:15:00' and unit ='m5Min' and (name= 'YC1743' or name= 'YC1741')
2,时间聚合,extract(epoch from time)/900
先不group,先select 这个计算结果,看每行返回什么,同一组的结果是否相同
SELECT time ,extract(epoch from time)/900 times, value FROM "ajdlbd_statistical" WHERE name= 'YC1743'and time>='2019-12-06 00:00:00' and time<='2019-12-06 14:15:00' and unit ='m5Min' order by time asc
查询结果如下
发现把时间取整就可以聚合了
SELECT time ,floor(extract(epoch from time)/900) times, value FROM "ajdlbd_statistical" WHERE name= 'YC1743'and time>='2019-12-06 00:00:00' and time<='2019-12-06 14:15:00' and unit ='m5Min' order by time asc
接下来对查询结果进行聚合
select min(a.time) m15,min(a.value) from
(SELECT time ,floor(extract(epoch from time)/900) times, value FROM "ajdlbd_statistical" WHERE name= 'YC1743'and time>='2019-12-06 00:00:00' and time<='2019-12-06 14:15:00' and unit ='m5Min' order by time asc) as a
group by a.times
order by m15
然后优化sql
SELECT min(time) grouptime ,min(value) FROM "ajdlbd_statistical" WHERE name= 'YC1743'and time>='2019-12-06 00:00:00' and time<='2019-12-06 14:15:00' and unit ='m5Min' group by floor(extract(epoch from time)/900) order by grouptime
然后进行多点查询
SELECT min(time) grouptime ,name,min(value) FROM "ajdlbd_statistical" WHERE name= 'YC1743'or name='YC1741' and time>='2019-12-06 00:00:00' and time<='2019-12-06 14:15:00' and unit ='m5Min' group by floor(extract(epoch from time)/900),name order by grouptime
结果发现查询错误,时间不对
这里一定要注意and和or的顺序关系,经过修改,最后sql如下
SELECT min(time) grouptime ,name,min(value) FROM "ajdlbd_statistical" WHERE (name= 'YC1743'or name='YC1741') and time>='2019-12-06 00:00:00' and time<='2019-12-06 14:15:00' and unit ='m5Min' group by floor(extract(epoch from time)/900),name order by grouptime