Window function based on transition of a column value

Question

I have response query like below

dest	emp
893106	0
717205	1
888305	0
312301	1
645100	0
222001	0
761104	1

And I want to get window function to separate rows like below:

dest	emp
893106	0
717205	1

dest	emp
888305	0
312301	1

dest	emp
645100	0
222001	0
761104	1

So each window has to begin with emp value = 0 and end with emp value = 1. It has to detect a transition of a column value.

Answer 1

The response query would be ordered by some field which maintains the order given in your result set,for the query to work.

You would look for patterns in data where the current value is 0 and the previous value is 1 and start a new grp as below.

Here is a way to do this.

create table t(id int, dest int, emp int);

insert into t 
select 1,893106,0 union all
select 2,717205,1 union all
select 3,888305,0 union all
select 4,312301,1 union all
select 5,645100,0 union all
select 6,222001,0 union all
select 7,761104,1;

commit;

with main_data
as (
select *,case when emp=0 and lag(emp) over(order by id)=1 then
                   1
                   else 0
         end as grp_val
  from t
    )
select *,sum(grp_val) over(order by id) as grp
  from main_data;

+====+========+=====+=========+=====+
| id | dest   | emp | grp_val | grp |
+====+========+=====+=========+=====+
| 1  | 893106 | 0   | 0       | 0   |
+----+--------+-----+---------+-----+
| 2  | 717205 | 1   | 0       | 0   |
+----+--------+-----+---------+-----+
| 3  | 888305 | 0   | 1       | 1   |
+----+--------+-----+---------+-----+
| 4  | 312301 | 1   | 0       | 1   |
+----+--------+-----+---------+-----+
| 5  | 645100 | 0   | 1       | 2   |
+----+--------+-----+---------+-----+
| 6  | 222001 | 0   | 0       | 2   |
+----+--------+-----+---------+-----+
| 7  | 761104 | 1   | 0       | 2   |
+----+--------+-----+---------+-----+

https://sqlize.online/sql/psql14/053971a469e423ef65d97984f9017fbf/