Now, I need the output
as below:
start_time end_time count 10:01 10:04 3 10:05 10:07 2
For this purpose, I wrote a query but it is not giving me the desired sequence. My query is as below:
with on_off as ( select time,status,case when status!=lag(status) over(order by time) then 1 else 0 end as continuous_count from time_status ) , grp as ( select *, row_number() over(partition by continuous_count order by time) rnk from on_off ) select * from grp order by time
It generates the output as below:
But in the rank
section I need something as below:
So, what exactly am I doing wrong here?
Here are the PostgresSQL
DDLs:
create table time_status(time varchar(10) null, status varchar(10) null); INSERT into time_status(time,status) values('10:01','ON'); INSERT into time_status(time,status) values('10:02','ON'); INSERT into time_status(time,status) values('10:03','ON'); INSERT into time_status(time,status) values('10:04','OFF'); INSERT into time_status(time,status) values('10:05','ON'); INSERT into time_status(time,status) values('10:06','ON'); INSERT into time_status(time,status) values('10:07','OFF');
Advertisement
Answer
Try this query:
SELECT min(time) as start_time, max(time) as end_time, sum(case when status = 'ON' then 1 else 0 end) as cnt FROM (SELECT time, status, sum(case when status = 'OFF' then 1 else 0 end) over (order by time desc) as grp FROM time_status) _ GROUP BY grp ORDER BY min(time);
->Fiddle