删除flow_statistic表老的数据，只留下最新的统计数据

BEGIN

/***************start**********************/

INSERT INTO flow_statistic (

idx, ipaddr1, ipaddr2, port1, port2, proto, protocol, 

down_bytes, up_bytes, flow, packet, 

up_rate, down_rate, packet_rate, updatetime

)

SELECT 

idx, ipaddr1, ipaddr2, port1, port2, proto, protocol, 

SUM(down_bytes) as down_bytes, SUM(up_bytes) as up_bytes, SUM(flow) as flow, SUM(packet) as packet,

up_rate, down_rate, packet_rate, updatetime

FROM flow

GROUP BY idx 

ORDER BY updatetime DESC;

/***************end**********************/

 

/***************将flow_statistic表的原数据和新数据合并**************************/

/***************start**********************/

INSERT INTO flow_statistic (

idx, ipaddr1, ipaddr2, port1, port2, proto, protocol, 

down_bytes, up_bytes, flow, packet, 

up_rate, down_rate, packet_rate, updatetime

)

SELECT 

idx, ipaddr1, ipaddr2, port1, port2, proto, protocol, 

SUM(down_bytes) as down_bytes, SUM(up_bytes) as up_bytes, SUM(flow) as flow, SUM(packet) as packet,

up_rate, down_rate, packet_rate, updatetime

FROM flow_statistic

GROUP BY idx 

ORDER BY updatetime DESC;

/***************end**********************/

 

/***************删除flow_statistic表老的数据，只留下最新的统计数据**************************/

/***************start**********************/

DELETE fd FROM flow_statistic fd INNER JOIN

(

SELECT aid FROM 

(

SELECT f.id as aid, f1.id as bid FROM flow_statistic f LEFT JOIN

(SELECT MAX(id) as id FROM flow_statistic GROUP BY idx ORDER BY id) f1

ON f.id = f1.id 

) as res

WHERE bid IS NULL ORDER BY aid

) f2

ON fd.id = f2.aid;

/***************end**********************/

 

/***************删除flow表数据**************************/

/***************start**********************/

TRUNCATE TABLE flow;

/***************end**********************/

END