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