SQL:索引/分组具有双重清除条件的事件

Question

(PostgreSQL 9.3) 我有一个表“事件”，其中包含数百万个复杂事件，存储为设备接收到的事件。出于示例目的:

+-----------+-------+
| Timestamp | Event |
+-----------+-------+
| 1         | A     |
| 2         | A     |
| 2         | B     |
| 3         | B     |
| 10        | A     |
| 11        | A     |
| 11        | 0     |
| 11        | C     |
| 12        | A     |
+-----------+-------+

在这种情况下，我有四种不同类型的事件:A、B、C 和 0。我想做的是对它们进行索引，这样我就可以为每个事件设置开始/停止时间戳。停止条件是:不再在给定的时间戳上报告事件，或者“0”甚至进来，清除所有这些。最终输出:

+------+----+-------+
| From | To | Event |
+------+----+-------+
| 1    | 3  | A     |
| 2    | 10 | B     |
| 10   | 11 | A     |
| 11   | 11 | C     |
| 12   |    | A     |
+------+----+-------+

在这种情况下，A 在 1 时被提升，并在 3 时被清除，因为此时它不再被报告。由于类似的原因，B 在 2 点被加注，并在 10 点被清算。 A 在 10 点再次被引发并在 11 点被 0 事件清除(尽管当时也被报告了!)。 C 在 11 时被提升并同时被清除(需要进行一些排序以在同一时间戳处理 0)。最后，A 在 12 点再次被提升并且当前处于事件状态，因此它获得 NULL 结束时间戳。

我确实有一些有用的东西，但它的 CTE 很重，因此不能很好地扩展到数百万条记录。我一直在试验 LATERAL(取得了很好的结果)并且我愿意接受任何 9.3 特定的建议。此外，“事件”本身对于这个问题也进行了极大的简化，实际上它是一组复杂的列。窗口函数也可能适用于此。

Answer 1

这里跳出框框思考，为什么不用触发器来维护汇总表？

这是您的案例的示例(省略了 FK 等)

create table event_type (
    event_type_id serial,
    event_name varchar(255)
);

create table event (
    event_time timestamp(0),
    event_type_id int
);

create table event_summary (
    event_summary_id serial,
    sum_from timestamp(0),
    sum_to timestamp(0),
    event_type_id int
);

create language plpgsql;

create or replace function event_insertion() returns trigger as $$
    declare
        var_event_summary_id integer;
    begin
        -- find out if event was fired during the previous second
        select
            event_summary_id
        into
            var_event_summary_id
        from
            event_summary s
        where
            new.event_type_id = s.event_type_id
            and sum_to >= new.event_time - interval '1 seconds';

        if found then
            --update existing summary to include this timestamp
            update event_summary set sum_to = new.event_time where event_summary_id = var_event_summary_id;
        else
            --create new summary for just this timestamp
            insert into event_summary(sum_from,sum_to,event_type_id) values (new.event_time,new.event_time,new.event_type_id);
        end if;

    return null;
    end;
$$ language plpgsql;

create trigger event_insertion after insert on event
    for each row execute procedure event_insertion();

-- some initial data
insert into event_type(event_name) values ('a');
insert into event_type(event_name) values ('b');
insert into event_type(event_name) values ('c');
insert into event_type(event_name) values ('0');

-- fire the events
insert into event(event_time,event_type_id) values (now(),(select event_type_id from event_type where event_name = 'a'));
select pg_sleep(1);
insert into event(event_time,event_type_id) values (now(),(select event_type_id from event_type where event_name = 'a'));
insert into event(event_time,event_type_id) values (now(),(select event_type_id from event_type where event_name = 'b'));
select pg_sleep(1);
insert into event(event_time,event_type_id) values (now(),(select event_type_id from event_type where event_name = 'b'));
select pg_sleep(7);
insert into event(event_time,event_type_id) values (now(),(select event_type_id from event_type where event_name = 'a'));
select pg_sleep(1);
insert into event(event_time,event_type_id) values (now(),(select event_type_id from event_type where event_name = 'a'));
insert into event(event_time,event_type_id) values (now(),(select event_type_id from event_type where event_name = '0'));
insert into event(event_time,event_type_id) values (now(),(select event_type_id from event_type where event_name = 'c'));
select pg_sleep(1);
insert into event(event_time,event_type_id) values (now(),(select event_type_id from event_type where event_name = 'a'));

-- query the summary table
select extract (seconds from s.sum_from), extract (seconds from s.sum_to), t.event_name from event_summary s inner join event_type t on (t.event_type_id = s.event_type_id);