更有效的SQLite触发器“汇总”多行

我正在收集路由器的传输数据;它提供每日，每月和每分钟（120秒的间隔）摘要 . 如果我在一天中间（因此在一个月中）重新启动路由器，那么这些报告将不完整 . 但是，我仍然会有间隔数据，并且可以在启动之前和之后对记录求和 .

以前，我在更新三个单独的表后使用脚本执行了此汇总操作 . 这很慢，因为我必须查询给定日间隔的总和，然后是月间隔 . 更新间隔时执行汇总会更快 . 所以我把一个触发器放在一起 . 问题是我编写触发器的方式，它更新每个间隔插入的每日和每月行 . 理想情况下，每个事务只会发生一次并处理刚刚添加的行 .

因此，下面是一个包含的示例，显示了我拥有的内容 . count列就是为了说明汇总发生的次数超出了需要 . 有没有办法简化这一点？

BEGIN TRANSACTION;

CREATE TABLE monthly  (count INTEGER DEFAULT 0, date DATE NOT NULL, interface TEXT NOT NULL, upload INTEGER DEFAULT 0, download INTEGER DEFAULT 0, rollup_upload INTEGER DEFAULT 0, rollup_download INTEGER DEFAULT 0, PRIMARY KEY (date, interface));
CREATE TABLE daily    (count INTEGER DEFAULT 0, date DATE NOT NULL, interface TEXT NOT NULL, upload INTEGER DEFAULT 0, download INTEGER DEFAULT 0, rollup_upload INTEGER DEFAULT 0, rollup_download INTEGER DEFAULT 0, PRIMARY KEY (date, interface));
CREATE TABLE interval (count INTEGER DEFAULT 0, date DATE NOT NULL, interface TEXT NOT NULL, upload INTEGER DEFAULT 0, download INTEGER DEFAULT 0, interval INTEGER, PRIMARY KEY (date, interface));

CREATE TRIGGER rollup_interval_trigger AFTER INSERT ON interval
BEGIN

INSERT OR REPLACE INTO daily (count, date, interface, upload, download, rollup_upload, rollup_download)
SELECT
    COALESCE((SELECT count FROM daily WHERE date IS strftime('%Y-%m-%d', NEW.date, 'localtime') AND interface IS 'wan'),0)+1,
    strftime('%Y-%m-%d', NEW.date, 'localtime'),
    'wan',
    COALESCE((SELECT upload FROM daily WHERE date IS strftime('%Y-%m-%d', NEW.date, 'localtime') AND interface IS 'wan'),0),
    COALESCE((SELECT download FROM daily WHERE date IS strftime('%Y-%m-%d', NEW.date, 'localtime') AND interface IS 'wan'),0),
    sum(upload) as rollup_upload,
    sum(download) as rollup_download
FROM interval
WHERE strftime('%Y-%m-%d', date, 'localtime') = strftime('%Y-%m-%d', NEW.date, 'localtime') AND interface IS 'vlan2';

INSERT OR REPLACE INTO monthly (count, date, interface, upload, download, rollup_upload, rollup_download)
SELECT
    COALESCE((SELECT count FROM monthly WHERE date IS strftime('%Y-%m-01', NEW.date, 'localtime') AND interface IS 'wan'),0)+1,
    strftime('%Y-%m-01', NEW.date, 'localtime'),
    'wan',
    COALESCE((SELECT upload FROM monthly WHERE date IS strftime('%Y-%m-01', NEW.date, 'localtime') AND interface IS 'wan'),0),
    COALESCE((SELECT download FROM monthly WHERE date IS strftime('%Y-%m-01', NEW.date, 'localtime') AND interface IS 'wan'),0),
    sum(upload) as rollup_upload,
    sum(download) as rollup_download
FROM interval
WHERE strftime('%Y-%m', date, 'localtime') = strftime('%Y-%m', NEW.date, 'localtime') AND interface IS 'vlan2';

END;

COMMIT;

insert into daily (date, interface, download, upload) values ('2012-10-02', 'wan', 10, 20);
insert into monthly (date, interface, download, upload) values ('2012-10-01', 'wan', 30, 40);

.headers ON

select * from daily;
select * from monthly;

begin transaction;
insert into interval (date, interval, download, upload, interface) values ('2012-10-02 11:00:00', 120, 10, 20, 'vlan2');
insert into interval (date, interval, download, upload, interface) values ('2012-10-02 12:00:00', 120, 10, 20, 'vlan2');
insert into interval (date, interval, download, upload, interface) values ('2012-10-02 13:00:00', 120, 10, 20, 'vlan2');
insert into interval (date, interval, download, upload, interface) values ('2012-10-02 14:00:00', 120, 10, 20, 'vlan2');
insert into interval (date, interval, download, upload, interface) values ('2012-10-01 12:00:00', 120, 10, 20, 'vlan2');
insert into interval (date, interval, download, upload, interface) values ('2012-10-03 12:00:00', 120, 10, 20, 'vlan2');
commit;

select * from interval;

select * from daily;
select * from monthly;