[Fix][liveChart] 修正liveChart App 平均速率计算方式

2 files changed, 14 insertions, 2 deletions

diff --git a/src/main/java/com/mesalab/qgw/model/basic/DSLQueryContext.java b/src/main/java/com/mesalab/qgw/model/basic/DSLQueryContext.java
index d72f3f27..ce3a7894 100644
--- a/src/main/java/com/mesalab/qgw/model/basic/DSLQueryContext.java
+++ b/src/main/java/com/mesalab/qgw/model/basic/DSLQueryContext.java
@@ -38,6 +38,8 @@ public class DSLQueryContext implements Serializable {
 
         sql = StrUtil.isNotEmpty(table) ? sql.replace("$table", table) : sql;
 
+        sql = !CollectionUtils.isEmpty(this.intervals) ? sql.replace("$interval_seconds", Long.toString(getIntervalSeconds(this.intervals))) : sql;
+
         if (StrUtil.isEmpty(this.filter) && CollectionUtils.isEmpty(this.intervals)) {
             sql = sql.replace("$intervals_and_filter", " 1= 1 ");
         } else if (StrUtil.isNotEmpty(this.filter) && !CollectionUtils.isEmpty(this.intervals)) {
@@ -54,6 +56,16 @@ public class DSLQueryContext implements Serializable {
         return sql;
     }
 
+    private long getIntervalSeconds(List<String> intervals) {
+        if (CollectionUtils.isEmpty(intervals) || !intervals.get(0).contains("/")) {
+            throw new IllegalArgumentException();
+        }
+        String[] split = intervals.get(0).split("/");
+        long start = DateUtil.parse(split[0]).getTime() / 1000;
+        long end = DateUtil.parse(split[1]).getTime() / 1000;
+        return end - start;
+    }
+
     private String parseIntervals(List<String> intervals, String timeField, String timeFieldType) {
         if (CollectionUtils.isEmpty(intervals) || !intervals.get(0).contains("/")) {
             return null;
@@ -64,7 +76,7 @@ public class DSLQueryContext implements Serializable {
         StringBuffer whereOfTime = new StringBuffer();
         if ("unix_timestamp".equalsIgnoreCase(timeFieldType)) {
             return whereOfTime.append(timeField).append(">= ").append(DateUtil.parse(start).getTime() / 1000).append(" AND ").append(timeField).append("< ").append(DateUtil.parse(end).getTime() / 1000).toString();
-        } else{
+        } else {
             return whereOfTime.append(timeField).append(">= '").append(start).append("' AND ").append(timeField).append("< '").append(end).append("'").toString();
         }
     }
diff --git a/src/main/resources/dsl-sql-template.sql b/src/main/resources/dsl-sql-template.sql
index 1c5e606b..0929ee20 100644
--- a/src/main/resources/dsl-sql-template.sql
+++ b/src/main/resources/dsl-sql-template.sql
@@ -17,7 +17,7 @@ SELECT protocol_stack_id, SUM(sessions) as sessions,SUM(c2s_bytes) as c2s_bytes,
 SELECT FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity', 'zero')) as stat_time, protocol_stack_id as type, sum(c2s_bytes + s2c_bytes) as bytes from $table where $intervals_and_filter group by FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity', 'zero')), protocol_stack_id order by stat_time asc
 #end
 #sql("APPLICATION_AND_PROTOCOL_TOP_APPS")
-SELECT app_name as app_name, SUM(bytes) as bytes, SUM(sessions) as sessions, MAX(bytes_rate) as max_rate, AVG(bytes_rate) as avg_rate FROM (SELECT app_name as app_name, SUM(sessions) as sessions, SUM(c2s_bytes + s2c_bytes) as bytes, SUM(c2s_bytes + s2c_bytes) * 8 / $granularity_seconds as bytes_rate FROM $table WHERE $intervals_and_filter AND notEmpty(app_name) GROUP BY app_name, TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity') ORDER BY bytes DESC ) GROUP BY app_name ORDER BY bytes DESC $limit
+SELECT app_name as app_name, SUM(bytes) as bytes, SUM(sessions) as sessions, MAX(bit_rate) as max_rate, SUM(bytes)/$interval_seconds as avg_rate FROM (SELECT app_name as app_name, SUM(sessions) as sessions, SUM(c2s_bytes + s2c_bytes) as bytes, SUM(c2s_bytes + s2c_bytes) * 8 / $granularity_seconds as bit_rate FROM $table WHERE $intervals_and_filter AND notEmpty(app_name) GROUP BY app_name, TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity') ORDER BY bytes DESC ) GROUP BY app_name ORDER BY bytes DESC $limit
 #end
 #sql("APPLICATION_AND_PROTOCOL_APP_RELATED_INTERNAL_IPS")
 SELECT if(bitAnd(flags, 8) = 8, client_ip, server_ip) AS ip, SUM(sent_bytes + received_bytes) AS bytes FROM $table WHERE $intervals_and_filter GROUP BY ip ORDER BY bytes DESC $limit