Cross-row computing in loop

  • Relative Locating refers to locating to a position that is a certain number of cells/groups before or after the current records/groups. This kind of data computation is usually the typical SQL puzzles. However, esProc solves it easily.
  • For example, to compute the month-on-month increase of sales volume of each month compared with that of previous month, you can simply use sales.(amount-amount[-1]) to represent it. Of which the relative number is represented as [n].
  • Just another example, to find 3 months with the highest sales volume and their respective month-on-month increases.
  • The Comparing with Last Period, Moving Average, and Add Up are the typical applications of relative locating. Please find the below example:
A
1 =[1,2,3,4,5]
2 =A1.(~/~[-1]-1) Growth rete [-1,1.0,0.5,0.33333326,0.25]
3 =A1.(~{-1,1}.avg()) Moving average [1.5,2.0,3.0,4.0,4.5]
4 =A1.(~{,0}.sum()) Summation [1,3,6,10,15]
5 =db.query(“select * from StockPrice where StockID=’000792′”)
6 =A5.sort(TradeDate)
7
=A6.(ClosingPrice/ClosingPrice[-1]-1)
Stocks increase
8 =db.query(“select * from Deposit”)
9 =A8.sort(TransactionDate)
10
=A9.new(TransactionDate,Amount{,0}.sum():remain)
Sum up operation amount for balance calculation