Orderbased Aggregation by Intervals
【Question】
The following data table has two fields – a and b.
a 
b 
10 
3 
21 
4 
33 
6 
46 
7 
52 
10 
This is the result I want:
a<10 
sum(b) 
a<20 
sum(b) 
a<30 
sum(b) 
a<40 
sum(b) 
a<50 
sum(b) 
Values of a field can be divided into over 1000 intervals.
【Answer】
To aggregate data by intervals in SQL:
with
cr as
(
select “a<10” x, b b from test3 where a < 10 union all
select “a<20” x, b b from test3 where a < 20 union all
select “a<30” x, b b from test3 where a < 30 union all
select “a<40” x, b b from test3 where a < 40 union all
select “a<50” x, b b from test3 where a < 50
)
select x,sum(b) from cr group by x;
It’s complicated when there are many conditional intervals.
SPL can divide a series of numbers into multiple intervals dynamically to generate a conditional sequence, perform aggregation by each condition, and generate the resulting table sequence. No matter the number of the intervals, the SPL solution is always a piece of servalline code:
A 

1 
=connect(“db”) 
2 
=A1.query(“select a,b from test3 order by a”) 
3 
=(A2.m(1).a – 1)\10+1 
4 
=A3.new(~*10:C,A2.select(a<C).sum(b):D) 
A1: Connect to database db;
A2: Retrieve data from the data table and order data by a field;
A3: Find the last interval number. A2.m() function finds a certain record from A2’s table sequence with the parameter being the record’s sequence number; a negative number represents a reciprocal. Here we get the value of a field in the last record and find the interval where the largest value of a field falls. The last a value – 52– is contained in the interval [51,60], whose sequence number is 6;
A4: new() function generates a new table sequence by calculating field values of A3’s record sequence. Each parameter corresponds to a new field value; the colon introduces a new field name. As the result of A3 is 6, A3.new() loops over the records 6 times; the sign ~ represents the loop number. Values of C field are loop number*10; values of D field are sums of the results of query performed over A2’s table sequence according to the condition a<C.
A2
A3
A4