Simple Steps for Sequence

When coming to serial number relational computations, esProc greatly simplifies the calculation difficulty and makes users without high technical capacity easily carry out the seemingly complex calculations. There are a large number of computational problems related to serial number actually, such as over the previous period, from the same period, moving average, which is difficult for SQL to express such calculations due to its inherent flaws.

In esProc, sequence number is the basic properties of the data, so it is easy to express the absolute serial number, the relative serial number, the serial number within a relative scope, and the current serial number in operation.

Locate by serial numbers

  • The Being Ordered refers to the data is stored in a certain order. Every piece of data and every member have their absolute or relative numbers. You can access the data with these serial numbers and operate on the data conveniently, such as locating, ranking, or sorting.
  • With serial numbers, you can access to the member of sets more easily.
  • Get the sales value field of the last record: sales.m(-1).(amount)
  • Get records whose sales value are over 1000: sales.pselect@a(amount>1000)
  • Get the record with the highest sales amount: sales.pmax@a(amount)
A
1 =tdb.query(“select * from Employee”)
2
=A1.sort(EntryDate)
Sort by EntryDate
3
=A2.pmin(Birthday)
The position of the eldest employee in above sequence
4 =A2(to(A3-1)) The employees on-boarding earlier than that of the eledest employee
5 =tdb.query(“select * from StockPrice where StockID=’000062′”)
6
=A5.sort(TradeDate)
Sort by TradeDate
7
=A6.pmax(ClosingPrice)
The position of the date record with the highest stock price in the set
8
=A6.calc(A7,ClosingPrice/ClosingPrice[-1]-1)
Increase of the highest stock price compared with price of the previous trading day
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