Question

Source: https://stackoverflow.com/questions/70494696/window-function-based-on-transition-of-a-column-value

I have response query as follows:

I want to get window function to separate rows like this:

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

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:

View SPL source code.