【Question】

Here’s a table:

-1 20141226 1

-1 20141225 1

-1 20141224 1

-1 20141223 1

-1 20141222 1

-1 20141219 1

-1 20141218 1

-1 20141217 1

-1 20141216 1

-1 20141215 1

-1 20141212 1

-1 20141211 1

-1 20141210 1

-1 20141209 1

-1 20141208 1

-1 20141205 1

-1 20141204 1

-1 20141203 1

-1 20141202 1

-1 20141201 1

0 20141128 1

0 20141127 1

0 20141126 1

-1 20141125 1

-1 20141124 1

0 20141121 1

0 20141120 1

This is the result I want: finding continuous records where the 1^st field value is 0 and the 3^rd field value is 1:

0 20141128 1

0 20141127 1

0 20141126 1

0 20141121 1

0 20141120 1

 

【Answer】

Group data by comparing each value to its next neighbor and get the group where the members are continuous and their number is greater than 1 and the 1^st field value is 0 and the 3^rd field value is 3. We can do this in SQL window functions. The workaround involves multilevel subqueries. It is really a hassle. In this case, we can retrieve data and handle it in SPL:

	A
1	$select no,date,num from T order by date  desc
2	=A1.group@o(no,num)
3	=A2.select(~.no==0&&~.num==1&&~.len()>1)
4	=A3.conj()

A1: Retrieve data in SQL;

A2: Group and merge data by no and num;

A3: Get the group where the 1^st field value is 0 and the 3^rd field value is 1 and the number of members is greater than 1;

A4: Concatenate the eligible members in A3’s group.

It’s also easy to embed the SPL script into a Java main program. See How to Call an SPL Script in Java.