【Question】
Here’s a simple-structure database table:

1   id   A   B

2   1   101   12

3   2   105   2

4   3   106   6

5   4   107   6

6   6   109   15

7   7   111   15

8   8   112   15

9   9   113   10

10  10  114   9

11  11  115   7

12  12  116   20

13  13  119   27

14  14  120   3

15  18  121   5

16  19  122   5

17  20  123   1

18  20  124   0

Note: id field values and A field values are not always continuous.
Below are desired queries and their results:

1.       Find values and frequency of a specified B within a continuous value range of field A. For example, if B=15, then:

1   id   A   B

2   7   111  15

3   8   112  15

And

1   ID   Astart   Aend   count   B

2   1    111     112    2      15

 

2.       Find values and frequency for a specified interval of B within a continuous value range of field A. For example, if 0<B<7, then:

1   id   A   B

2   2   105  2

3   3   106  6

4   4   107  6

5   14  120  3

6   18  121  5

7   19  122  5

8   20  123  1

And

1   ID   Astart   Aend   count

2   1   105   107   3

3   2   120   123   4

By the way, are there great differences between queries in Sqlite3, Mysql and SQL? Thanks.

 

【Answer】

Different database products have very different syntax for getting both computing tasks done. It’s almost impossible to code order-based calculations if a language doesn’t support window functions. Alternatively, data would be imported to be handled in an advanced language, or with a stored procedure. But, even if a window function is available, a roundabout subquery is unavoidable. That makes code hard to read. SPL (Structured Process Language) enables intuitive and smooth code in a universal style. Here’s the SPL script:

	A
1	$select id,A,B from  t0047 order by id
2	=A1.select(B==15)
3	=A2.group@i(A[-1]+1!=A).select(~.len()>1)
4	=A3.conj()
5	=A3.new(#:id,~(1).A:Astart,~.m(-1).A:Aend,~.count():count,B)

A1: Retrieve data ordered by id in SQL;

A2: Get rows where B=15;

A3: Compare each row with its next one and put them in same group if the two values of field A are continuous; then get groups containing more than one row;

A4: Concatenate members of all groups;

A5: Create a new table sequence having 5 fields – id, Astart, Aend, count and B. Astart is value of field A in each group’s first row; Aend is value of field A in each group’s last row; count is the number of members of a group.

A4 is the result of the first structure:

A5 is the result of the second structure:

If B is an interval, just change B==15 in A2 to B>0 && B<7. It’s easy to integrate an SPL script with another application. See How to Call an SPL Script in Java to learn more.