mysql - How to summarise event types with defined hierarchy per day and week (Use SQL or ETL) -
if have following events table , want summarise each id, eventday,eventweek 1 row such eventstatus reported severe/concerning (high>low>normal). solution need cope more event weeks , event days
is can accomplished example mysql or mariadb compatible sql or etl processes better suited task (with outline of steps)?
sampletable
id eventstatus eventday eventweek 1 normal 1 1 1 low 2 1 1 high 2 1 1 normal 2 1 2 normal 1 1 2 normal 2 1 3 normal 2 1 3 low 2 1 desired output
id eventstatus eventday eventweek 1 normal 1 1 1 high 2 1 2 normal 1 1 2 normal 2 1 3 low 2 1 would solution cope additional columns constrain events (eventlocation) in addition eventday , eventweek
here's 1 idea. index on (id,eventweek,eventday) help. if store priority (h,l,n) intergers (e.g. 3,2,1 respectively) add index too, further enhance performance - although there's other ways too...
drop table if exists my_table; create table my_table (id int not null ,eventstatus varchar(12) not null ,eventday int not null ,eventweek int not null ,primary key(id,eventweek,eventday,eventstatus) ); insert my_table values (1,'normal',1,1), (1,'low',2,1), (1,'high',2,1), (1,'normal',2,1), (2,'normal',1,1), (2,'normal',2,1), (3,'normal',2,1), (3,'low',2,1); select a.* my_table join ( select id , max(case eventstatus when 'high' 3 when 'low' 2 else 1 end) eventstatus , eventday , eventweek my_table group id , eventweek , eventday ) b on b.id = a.id , b.eventstatus = case a.eventstatus when 'high' 3 when 'low' 2 else 1 end , b.eventweek = a.eventweek , b.eventday = a.eventday; +----+-------------+----------+-----------+ | id | eventstatus | eventday | eventweek | +----+-------------+----------+-----------+ | 1 | normal | 1 | 1 | | 1 | high | 2 | 1 | | 2 | normal | 1 | 1 | | 2 | normal | 2 | 1 | | 3 | low | 2 | 1 | +----+-------------+----------+-----------+
Comments
Post a Comment