I have response query like below

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

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.

CodePudding user response：

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/