根据时间段生成趋势数据

create table event(
id serial primary key,
event_type int,
start_time int,
end_time int
);


insert into event(event_type, start_time, end_time) values(1, 1, 1);
insert into event(event_type, start_time, end_time) values(2, 1, 1);
insert into event(event_type, start_time, end_time) values(3, 1, 1);
insert into event(event_type, start_time, end_time) values(4, 1, 1);
insert into event(event_type, start_time, end_time) values(1, 1, 2);
insert into event(event_type, start_time, end_time) values(2, 1, 2);
insert into event(event_type, start_time, end_time) values(3, 1, 2);
insert into event(event_type, start_time, end_time) values(4, 1, 2);
insert into event(event_type, start_time, end_time) values(1, 1, 3);
insert into event(event_type, start_time, end_time) values(2, 1, 3);
insert into event(event_type, start_time, end_time) values(3, 1, 3);
insert into event(event_type, start_time, end_time) values(4, 1, 3);




select event_type, json_object_agg(d, s) from (
select 1 as d, event_type, count(event_type) as s from event where start_time < 2 and end_time >= 1 group by event_type 
union all
select 2 as d, event_type, count(event_type) as s from event where start_time < 3 and end_time >= 2 group by event_type
union all
select 3 as d, event_type, count(event_type)as s from event where start_time < 4 and end_time >= 3 group by event_type 
) as t group by event_type