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 |