One line code to get same period ratio which is hard to get in SQL


Group by year and month to get the aggregate value of each month, and it's a regular group operation without any difficulty. The next step is to find the record of the same month in last year. If the data is in order, the previous 12th record will be the one. However, SQL can't express the record object alone, and it doesn't have the computing capability of relative position in the set, so it has to change the problem into self join. Join two records to be calculated into one record, and then do multi field calculation in the record:

with t as (select year(f1) y, month(f1) m, sum(f2) n
       from A
       group by y,m)
select t1.*,t1.n/t2.n r
from t t1 join t t2
       on t1.y=t2.y+1 and t1.m=t2.m

In this way, it will increase the burden of thinking and the difficulty of problem solving.

It would be much simpler if you use esProc SPL language. It supports the calculation of ordered sets thoroughly, and can clearly express the records at any position in the set. Through absolute / relative positioning, it is easy to describe the record at the previous 12th row as follows, and one line of code will solve the problem:

=connect(”mysqlDB”).query(“select * from A”).groups(year(f1):y,month(f1):m;sum(f2):n).sort(y,m).derive(n/~[-12].n:r)

In addition to location-based calculation, ordered set also has a variety of calculation means, such as unconventional iterative aggregation, ordered loop, ordered ranking, etc. Please refer to SPL Order , these technologies are very helpful to improve calculation performance and simplify calculation programming.


When the data is not in the database, it is still convenient for SPL to perform complex calculations:


It's also easy to embed esProc into Java applicationsplease refer to How to Call an SPL Script in Java

For specific usage, please refer to  Getting started with esProc