一、历史统计数据表字段介绍
CREATE TABLE STAT_TABLE (
ID_F INT;//数据点ID,
NAME_F STRING;//数据点名称,
AVG_F DOUBLE;//统计平均值,
ON_DURATION_F INT;//持续时间,
MIN_F DOUBLE;//统计最小值,
TIME_F STRING;//数据时间,
LAST_VAL_F DOUBLE;//最后值,
ULTRA_LIMIT_DURATION_F INT;//超限制时间,
INTERVAL_F INT;//历史数据时间间隔,
MAX_F DOUBLE;//统计最大值,
FIRST_VAL_F DOUBLE;//第一值,
LAST_TIME_F DATE;//最后时间,
FIRST_TIME_F DATE;//第一值时间,
FLOW_F DOUBLE;//流量值,
COUNT_F INT;//统计数据数量,
ULTRA_LIMIT_COUNT_F INT;//超限制数量,
QUALITY_F STRING;//数据质量,
OUTPUT_F DOUBLE;//产量
)
二、查询历史统计数据
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test {
public static void main(String[] args) {
try {
Class.forName("com.processdb.jdbc.JDBC");
Connection connection = null;
Statement statement = null;
try {
// 对数据库进行连接
connection = DriverManager.getConnection("jdbc:processdb://localhost:8301/instance01", "root", "root");
statement = connection.createStatement();
// 设置超时时间
statement.setQueryTimeout(30); // set timeout to 30 sec.
ResultSet rs = null;
int i = 0;
while (i < 1) {
i++;
// 查询sql
/**查询统计数据表 STAT_TABLE
* 所需字段
* interval_f=3600 时间间隔 以秒为单位(不填写默认3600秒)
* name_f='public.sys.PDB_OS_CPU_USAGE' 数据点的位置(必填)
* time_f>'2022-10-26 00:00:00' and time_f<'2022-10-26 15:35:18'数据时间段(必填)
* STAT_BIT_MAP = 1000 统计控制单位 如要查询产量,则值为1000 查询超限为100 若都需要查询多个条件 则值例如为1100(1000+100)
* VALUE_F 查询超限时,所需的条件,设置超限界限
* ultralimitInterval 查询超限时,所需的条件,设置最短超限时长
* 可查询字段
* NAME_F 数据点位置
* TIME_F 时间
* AVG_F 统计平均值
* MAX_F 统计最大值
* MIN_F 统计最小值
* FLOW_F 流量值
* COUNT_F 统计数据数量
* FIRST_VAL_F 第一值
* FIRST_TIME_F 第一值时间
* LAST_VAL_F 最后值
* LAST_TIME_F 最后时间
* OUTPUT_F 产量
* ON_DURATION_F 持续时间
* ULTRA_LIMIT_DURATION_F 超限制时间
* ULTRA_LIMIT_COUNT_F 超限制数量
*/
// String sql = "select NAME_F,TIME_F,OUTPUT_F,AVG_F,MAX_F,MIN_F,FLOW_F,COUNT_F,FIRST_VAL_F,FIRST_TIME_F,LAST_VAL_F,LAST_TIME_F,OUTPUT_F,ON_DURATION_F,ULTRA_LIMIT_DURATION_F,ULTRA_LIMIT_COUNT_F from STAT_TABLE where VALUE_F >0 and STAT_BIT_MAP = 1000 and name_f='D30.T31.P30' and interval_f = 180 and time_f>'2022-11-08 09:50:00' and time_f<'2022-11-08 12:00:00'";
String sql = "select * from STAT_TABLE where VALUE_F >0 and STAT_BIT_MAP = 1000 and name_f='D30.T31.P30' and interval_f = 180 and time_f>'2022-11-08 09:50:00' and time_f<'2022-11-08 12:00:00'";
// 运行sql发起请求
rs = statement.executeQuery(sql);
// 查询结果存储在ResultSet
System.out.println("Query statdata from table: STAT_TABLE.");
while (rs.next()) {
// read the result set
System.out.println("NAME_F:" + rs.getString("NAME_F")+",TIME_F:"+rs.getString("TIME_F")+",AVG_F:"+rs.getString("AVG_F")+",MAX_F:"+rs.getString("MAX_F")+",MIN_F:"+rs.getString("MIN_F")+",FLOW_F:"+rs.getString("FLOW_F")
+",COUNT_F:"+rs.getString("COUNT_F")+",FIRST_VAL_F:"+rs.getFloat("FIRST_VAL_F")+",FIRST_TIME_F:"+rs.getTime("FIRST_TIME_F")+",LAST_VAL_F:"+rs.getFloat("LAST_VAL_F")+",LAST_TIME_F:"+rs.getTime("LAST_TIME_F")
+",OUTPUT_F:"+rs.getString("OUTPUT_F")+",ON_DURATION_F:"+rs.getFloat("ON_DURATION_F")+",ULTRA_LIMIT_DURATION_F:"+rs.getFloat("ULTRA_LIMIT_DURATION_F")+",ULTRA_LIMIT_COUNT_F:"+rs.getFloat("ULTRA_LIMIT_COUNT_F")); }
System.out.println("Count: " + rs.getRow());
}
} catch (SQLException e) {
System.err.println(e.getMessage());
e.printStackTrace();
} finally {
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
System.err.println(e);
}
}
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
return;
}
return;
}
}
示例运行结果:
Query statdata from table: STAT_TABLE.
NAME_F:D30.T31.P30,TIME_F:2022-11-08 09:55:52.0,AVG_F:0,MAX_F:66.0,MIN_F:66.0,FLOW_F:0.0,COUNT_F:1,FIRST_VAL_F:66.0,FIRST_TIME_F:09:55:52,LAST_VAL_F:66.0,LAST_TIME_F:09:55:52,OUTPUT_F:12.0,ON_DURATION_F:68.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-08 09:57:00.0,AVG_F:0,MAX_F:66.0,MIN_F:66.0,FLOW_F:0.0,COUNT_F:1,FIRST_VAL_F:66.0,FIRST_TIME_F:09:55:52,LAST_VAL_F:66.0,LAST_TIME_F:09:55:52,OUTPUT_F:12.0,ON_DURATION_F:180.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-08 10:00:00.0,AVG_F:0,MAX_F:66.0,MIN_F:66.0,FLOW_F:0.0,COUNT_F:1,FIRST_VAL_F:66.0,FIRST_TIME_F:09:55:52,LAST_VAL_F:66.0,LAST_TIME_F:09:55:52,OUTPUT_F:12.0,ON_DURATION_F:180.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-08 10:03:00.0,AVG_F:0,MAX_F:66.0,MIN_F:66.0,FLOW_F:0.0,COUNT_F:1,FIRST_VAL_F:66.0,FIRST_TIME_F:09:55:52,LAST_VAL_F:66.0,LAST_TIME_F:09:55:52,OUTPUT_F:12.0,ON_DURATION_F:180.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-08 10:06:00.0,AVG_F:0,MAX_F:66.0,MIN_F:66.0,FLOW_F:0.0,COUNT_F:1,FIRST_VAL_F:66.0,FIRST_TIME_F:09:55:52,LAST_VAL_F:66.0,LAST_TIME_F:09:55:52,OUTPUT_F:12.0,ON_DURATION_F:180.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-08 10:09:00.0,AVG_F:0,MAX_F:66.0,MIN_F:66.0,FLOW_F:0.0,COUNT_F:1,FIRST_VAL_F:66.0,FIRST_TIME_F:09:55:52,LAST_VAL_F:66.0,LAST_TIME_F:09:55:52,OUTPUT_F:12.0,ON_DURATION_F:180.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-08 10:12:00.0,AVG_F:0,MAX_F:66.0,MIN_F:66.0,FLOW_F:0.0,COUNT_F:1,FIRST_VAL_F:66.0,FIRST_TIME_F:09:55:52,LAST_VAL_F:66.0,LAST_TIME_F:09:55:52,OUTPUT_F:12.0,ON_DURATION_F:180.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-08 10:15:00.0,AVG_F:0,MAX_F:66.0,MIN_F:66.0,FLOW_F:0.0,COUNT_F:1,FIRST_VAL_F:66.0,FIRST_TIME_F:09:55:52,LAST_VAL_F:66.0,LAST_TIME_F:09:55:52,OUTPUT_F:12.0,ON_DURATION_F:180.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-08 10:18:00.0,AVG_F:282.8,MAX_F:1000.0,MIN_F:78.0,FLOW_F:1414.0,COUNT_F:5,FIRST_VAL_F:78.0,FIRST_TIME_F:10:18:00,LAST_VAL_F:150.0,LAST_TIME_F:10:20:33,OUTPUT_F:72.0,ON_DURATION_F:154.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
Count: 9
三、速度测试
这里我查询的是数据库自带的,PUBLIC库的SYS表的PDB_OS_CPU_USAGE点,历史采样数据共有817776条,我们按照180的统计周期进行统计
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test {
public static void main(String[] args) {
try {
Class.forName("com.processdb.jdbc.JDBC");
Connection connection = null;
Statement statement = null;
try {
// 对数据库进行连接
connection = DriverManager.getConnection("jdbc:processdb://localhost:8301/instance01", "root", "root");
statement = connection.createStatement();
// 设置超时时间
statement.setQueryTimeout(30); // set timeout to 30 sec.
ResultSet rs = null;
int i = 0;
while (i < 1) {
i++;
String sql = "select * from STAT_TABLE where name_f='PUBLIC.SYS.PDB_OS_CPU_USAGE' and interval_f = 180 and time_f>'2022-11-01 00:00:00' and time_f<'2022-11-23 14:33:50'";
// 运行sql发起请求
long beginTime = System.currentTimeMillis();
System.out.println("======开始时间======="+beginTime);
rs = statement.executeQuery(sql);
long endTime = System.currentTimeMillis();
System.out.println("==消耗时间=="+(endTime-beginTime)+"ms");
// 查询结果存储在ResultSet
System.out.println("Query statdata from table: STAT_TABLE.");
System.out.println("Count: " + rs.getRow());
}
} catch (SQLException e) {
System.err.println(e.getMessage());
e.printStackTrace();
} finally {
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
System.err.println(e);
}
}
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
return;
}
return;
}
}
运行速度如下:
======开始时间=======1669182182687
==消耗时间==146ms
Query statdata from table: STAT_TABLE.
Count: 10652