CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
hommage corbusier
hommage corbusier

Reputation: 13

Window function based on transition of a column value

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.

Upvotes: 1

Views: 911

Answers (2)

user18843334
user18843334

Reputation:

For such a scenario where data is ordered by a certain field, you create a new group whenever the emp value of the previous record is 1. It is complicated to implement the process in SQL. You need to create row numbers first and the marker column as needed, and perform the grouping according to the marker column and row numbers. A common alternative is to fetch the original data out of the database and process it in Python or SPL. SPL, the open-source Java package, is easier to be integrated into a Java program and generate much simpler code. It does the computation with only two lines of code:

A
1 =PG.query("select dest,emp from t3 order by sth")
2 =A1.group@i(emp[-1]==1)

Upvotes: 0

George Joseph
George Joseph

Reputation: 5922

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/

Upvotes: 2

Related Questions