我有一组数据告诉我是否有几个系统可用,或者每5或15分钟增量.目前,时间增量无关紧要.
数据如下所示：
Status Time System_ID T 10:00 S01 T 10:15 S01 F 10:30 S01 F 10:45 S01 F 11:00 S01 T 11:15 S01 T 11:30 S01 F 11:45 S01 F 12:00 S01 F 12:15 S01 T 12:30 S01 F 10:00 S02 F 10:15 S02 F 10:30 S02 F 10:45 S02 F 11:00 S02 T 11:15 S02 T 11:30 S02
我想创建一个视图,告诉系统什么时候不可用(即什么时候是F),从什么时间到什么时间,以及从…到的时间.
期望的结果：
System_ID From To Duration S01 10:30 11:00 00:30 S01 11:45 12:15 00:30 S02 10:00 11:00 01:00
这是脚本数据：
DROP SCHEMA IF EXISTS Sys_data CASCADE;
CREATE SCHEMA Sys_data;
CREATE TABLE test_data (
status BOOLEAN,dTime TIME,sys_ID VARCHAR(10),PRIMARY KEY (dTime,sys_ID)
);
INSERT INTO test_data (status,dTime,sys_ID) VALUES (TRUE,'10:00:00','S01');
INSERT INTO test_data (status,'10:15:00',sys_ID) VALUES (FALSE,'10:30:00','10:45:00','11:00:00','11:15:00','11:30:00','11:45:00','12:00:00','12:15:00','12:30:00','S02');
INSERT INTO test_data (status,'S02');
先感谢您！
解决方法
也许不是最优的,但它的工作原理:)
select sys_id,first_time as down_from,max(dTime) as down_to
from ( select status,sys_id,(select min(td_add2.dTime)
from test_data td_add2
where td_add2.dtime <= x.dTime
and td_add2.dtime >= COALESCE(x.prev_time,x.min_time)
and td_add2.status = x.status
and td_add2.sys_id = x.sys_id ) as first_time
from ( select td_main.status,td_main.sys_id,td_main.dTime,(select max(td_add.dTime)
from test_data td_add
where td_add.dtime < td_main.dTime
and td_add.status != td_main.status
and td_add.sys_id = td_main.sys_id ) as prev_time,(select min(td_add.dTime)
from test_data td_add
where td_add.dtime < td_main.dTime
and td_add.sys_id = td_main.sys_id ) as min_time
from test_data td_main) x
) y
where status = false
and first_time is not null
group by sys_id,first_time
order by sys_id,first_time
+--------+-----------+----------+
| sys_id | down_from | down_to |
+--------+-----------+----------+
| S01 | 10:30:00 | 11:00:00 |
| S01 | 11:45:00 | 12:15:00 |
| S02 | 10:00:00 | 11:00:00 |
+--------+-----------+----------+
3 rows in set (0.00 sec)
