1：如果不考虑当天刚开始是关播或者连续的关播，当天最后是开播或者连续的开播
select id,sum(length) as all_time
from
(select id,( case when status=0 and (lag(status,1)over(partition by id order by time)=1 or lag(status,1)over(partition by id order by time) is null ) then 0 else time - lag(time,1)over(partition by id order by time) END ) as length
from table) temp
group by id
2：如果当天刚开始是关播或者连续的关播 丢弃，如果当天最后是开播或者连续的开播 也丢弃。
select id, sum(length) as all_time
from
(select id,( case when (sum(status - 1)over(partition by id order by time) =0 or sum(status)over(partition by id order by time ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) =0) then 0 when status=0 and lag(status,1)over(partition by id order by time)=1 then 0 else time - lag(time,1)over(partition by id order by time) END ) as length
from table) temp
group by id
3：如果当天刚开始是关播或者连续的关播，则认为在当天零点开播了，如果当天最后是开播或者连续的开播 则认为在当天24.00 关播了
select id, sum(length) as all_time
from
(select id, (case when status =1 and lag(status,1)over(partition by id order by time) is null then time - unix_timestamp(from_unixtime(time,'yyyy-MM-dd'),'yyyy-MM-dd')
when status =0 and lead(status,1)over(partition by id order by time) is null then unix_timestamp(from_unixtime(time,'yyyy-MM-dd'),'yyyy-MM-dd') + 86400000 - time
when status=0 and lag(status,1)over(partition by id order by time)=1 then 0
else time - lag(time,1)over(partition by id order by time) END ) as length
from table) temp
group by id
