如何获得的开始时间和结束时间为每个状态
问题描述:
我期待列出开始时间和结束时间当门被OPEN这个样本数据如何获得的开始时间和结束时间为每个状态
下面是一些示例数据表
currenttime, door_status,
12-10-15 12:02:00 Open
12-10-15 12:01:00 Open
12-10-15 12:00:30 Open
12-10-15 11:59:30 Open
12-10-15 11:59:00 Open
12-10-15 11:58:30 Open
12-10-15 11:58:00 Closed
12-10-15 11:57:30 Closed
12-10-15 11:57:00 Open
12-10-15 11:56:00 Open
12-10-15 11:55:30 Open
12-10-15 11:55:00 Open
这里是预期的结果
start_Time end_Time time_it_was_open
12-10-15 11:58:30 12-10-15 12:02:00 3 mins 30 secs
12-10-15 11:55:00 12-10-15 11:57:00 2 mins
试图查询
查询1:
SELECT current_time_open, current_time_closed, door1 FROM(
SELECT
CASE
WHEN door1 = 'Open' THEN currenttime
END AS current_time_open,
CASE
WHEN door1 = 'Closed' THEN currenttime
END AS current_time_closed,
door1
FROM
(SELECT
r1.currenttime, r1.temp, r1.door1
FROM
rcs_data r1) as g1 GROUP BY currenttime DESC) as t1;
查询2:
SELECT MAX(current_time_open), MAX(current_time_closed), TIMEDIFF(MAX(current_time_open),MAX(current_time_closed)) FROM (SELECT
CASE
WHEN door1 = 'Open' THEN currenttime
END AS current_time_open,
CASE
WHEN door1 = 'Closed' THEN currenttime
END AS current_time_closed
FROM
(SELECT
currenttime, temp, door1
FROM
rcs_data) as g1 GROUP BY currenttime DESC) as t1;
答
这里一个变种做到这一点:
select *, unix_timestamp(endtime)-unix_timestamp(starttime) as dur_seconds
from (
select min(currenttime) as starttime, max(currenttime) as endtime
from (
select currenttime, coalesce(
(select min(currenttime) from t as b where b.currenttime > a.currenttime and b.door_status != a.door_status),
(select max(currenttime) from t as b where b.currenttime > a.currenttime and b.door_status = a.door_status),
a.currenttime) as mxt
from t as a
where a.door_status = 'Open'
) as q
group by mxt
) as q2
因此,主要想法是为“打开”状态组“分配”一些唯一ID,在这种特定情况下,其第一次关闭状态或最后一次打开状态,或当前时间为最后一次“打开” “表格中的状态
+0
美丽,非常感谢@Lashane,它的工作原理 –
到目前为止您尝试过了什么?编辑您的问题并添加您的尝试查询。 – BrokenBinary
请添加一个“预期结果”区块,因为它可以帮助我们使用您的虚拟数据来达到您的期望。 –
我已经添加了。 @MarcoAurélioDeleu –