influxdb与timescale的聚合函数

今天在编写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

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值