Accumulation by Interval
【Question】
Here’s a table of two fields – a and b:
a b
10 3
21 4
33 6
46 7
52 10
The expected result:
a<10 sum(b)
a<20 sum(b)
a<30 sum(b)
a<40 sum(b)
a<50 sum(b)
That is:
10 0
20 3
30 7
40 13
50 20
Is there any idea about how to do this in SQL? There are 1000 intervals for values of a field.
【Answer】
You can create an interval table to use with an analytic function for handling your problem. But the process is too complicated. It’s simple and easy to do it in SPL (Structured Process Language), as shown below:
A |
|
1 |
$SELECT A,B FROM TB1 ORDER BY A |
2 |
=A1.groups((A)\10+1;sum(B)).new(#1*10:C,sum(#2[:0]):D) |
A1: Data retrieval;
A2: Group records by the intervals and sum values within the current interval to generate a new table sequence.
The final result:
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL