本文介绍了一种数据库中流量统计数据的更新与维护方法，包括如何插入新的统计数据、合并新旧数据以及清理过期数据的过程。具体操作涉及使用SQL语句进行数据汇总、更新和删除等。
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
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
