esProc Simplifies SQL-style Computations – Record Splitting

Programmers often encounter complicated SQL-style computations during database application development. Record splitting, for example, is to split a record separated by a certain separator into multiple ones. For different databases, SQL has various problems like poor syntax support and the need of writing nested and multilayered statements. esProc boasts rich class library, has the ability to produce intuitive script step by step and thus makes the handling of the case much easier. An example will be used to illustrate how it works.

The application stores the operations of each user at a single login in the user operation record table – user_op – by separating them with commas. Some of the original data are as follows:

LOGTIME             USERID OPID

2014/1/3 11:10:12  100001    a,d,h

2014/1/3 9:23:12    100002    a,e,g,p

2014/1/3 10:35:11  100003    a,r,n

      

Now we want to split each record separated by commas into multiple ones. For instance the first record will become this after splitting:

LOGTIME             USERID OPID

2014/1/3 11:10:12  100001    a

2014/1/3 11:10:12  100001    d

2014/1/3 11:10:12  100001    h

SQL needs recursive queries to handle this kind of operation, but it is extremely difficult when the database supports recursion poorly. Even Oracle that supports recursion well has difficulty in doing this. The following SQL statements are used to prove this point:

SELECT logtime,userid,REGEXP_SUBSTR(opid,'[^,]+',1,rn) opid
FROM user_op,(SELECT LEVEL rn FROM DUAL
CONNECT BY LEVEL<=(SELECT MAX(length(trim(translate(opid,replace(opid,','),' '))))+1 FROM user_op))
WHERE REGEXP_SUBSTR(opid,'[^,]+',1,rn) IS NOT NULL

OR

select logtime,userid,regexp_substr(opid,'[^,]+',1,level) opid
from user_op
connect by level <= length(opid)-length(regexp_replace(opid,'[^,]+',''))
and rowid= prior rowid
and prior dbms_random.value is not null

      

They demonstrate the complexity featuring SQL-style computations. Now let’s look at how esProc will handle this:

In this esProc script:

A1=esProc.query("SELECT LOGTIME,USERID,OPID FROM USER_OP")

Retrieve data of user_op from the database. Part of the result is as follows: 

A2= A1.create()

Based on A1, create a new table sequence with the same structure to store the final result set. The new table sequence is as follows: 

A3=A1.(OPID.array().(A2.record([A1.LOGTIME,A1.USERID,~])))

Loop through each record of A1’s sequence table to split each value of OPID field and convert it into a sequence. Then write the splitting results to A2’s result set. The result of A2 is as follows: 

It can be seen that esProc uses merely three lines of code to complete the record splitting operation. By the way, esProc can be called by the reporting tool or Java program much like they call a database, and provides JDBC interface to return the result in the form of ResultSet to Java main program. See related documents for details. 

esProc Simplifies SQL-style Computations – Interval Merging

There are many complicated SQL-style computations in database application development. Interval merging is one of them, such as the summation of non-overlapping time intervals and merging of overlapping time intervals, etc. Due to the lack of orderliness of an SQL set, SQL solves the problems by resorting to recursive method that is difficult to be used with a database that supports not enough recursive functions. Besides, we need to write a nested SQL statement containing multilayered subqueries that is too lengthy for realization and modification.

In contrast, esProc has an easier way for the computation. It merges intervals step by step through intuitive script. Let’s look at an example. 

Table Udetail stores user operation records in detail. Some of the original data are as follows:

ID     UID           ST                          ET

1       1001         2014-9-1 10:00:00   2014-9-2 11:30:00

2       1001         2014-9-1 10:30:00   2014-9-2 11:00:00

3       1001         2014-9-3 11:00:00   2014-9-4 12:00:00

4       1001         2014-9-4 10:00:00   2014-9-5 13:00:00

5       1001         2014-9-4 15:00:00   2014-9-5 18:00:00

6       1002         2014-9-1 11:00:00   2014-9-2 11:30:00

7       1002         2014-9-1 10:30:00   2014-9-2 11:00:00

    

In this table, ST and ET represent respectively the starting time and ending time of the operations and each user may have overlapping operational time intervals. Based on a specified user, we are to:

1.Merge the overlapping time intervals so as to create a new record of time intervals;

2.Summarize the total time of non-overlapping intervals.

esProc does it as follows:

An explanation of the script：

A1=db1.query("select * from udetail where UID=?",arg1)

Retrieve data of the specified user from the database. arg1 is an external parameter. Suppose the value of it is 1001, then the query result is as follows: 

A2= A1.sort(ST).select(ET>ET[-1]).run(max(ST,ET[-1]):ST)

This line of code first sorts A1’s table by the starting time (.sort(ST)) and selects records by the condition that each ET is greater than the previous one, i.e. to delete the intervals completely covered by others. For every two overlapping records, get the minimum ST value and the maximum ET value to create a new time interval. The result is as follows: 

It can be seen that sets are ordered in esProc, so the previous record can be referenced by its number. It is very different from SQL. If the continuous time intervals need to be combined into one interval, A2’s code can be like this：A2=A1.sort(ST).select(ET>ET[-1]).run(if(ST<ET[-1],ST[-1],ST):ST).group(ST;~.m(-1).ET:ET)

And the following result will be got: 

A3=A2.sum(interval@s(ST,ET))

Summarize the total overlapping time. The result is as follows: 

If the total non-overlapping time is wanted (without details), the code can be

A1.sort(ST).select(ET>ET[-1]).sum(interval@s(max(ST,ET[-1]),ET))

If the whole step of time is relatively small, the code can be modified as

A1.(periods@s(ST,ET)).union().len()-2, which gets the result by counting the number of time points.

Finally, an esProc script can be called by the reporting tool or the Java program much like they call a database. It returns a result in the form of ResultSet through JDBC provided esProc. See related documents for more details.

esProc Simplifies SQL-style Computations – Multi-layered Data Grouping with Specified Criteria

During database application development, we are often faced with complicated SQL-style computations, to which the multi-layered data grouping with specified criteria belong. In SQL, the key method for realizing the operation is to group the source data according to specified criteria using left join statement. The problem is that this method usually involves handling data grouping and summarizing, inter-row computations, completing data, and, moreover, multi-layered data. So we need to write rather complicated SQL statements to express it.

In esProc, the operation can be realized with simple and easy code. Its ability will be shown through the following example.

Here is a table – stocklog – in which all the warehouse-in and -out records of various products every day are stored. Now we are asked to produce a stock report of all the products for every day of a specified time period. Some of the records in stocklog are as follows:

In the table, if the INDICATOR value of a record is null, it is a warehouse-in record; if the INDICATOR value is ISSUE, it is a warehouse-out record. Note that though some dates are missing, which means there are no corresponding records in these days, the stock report must include all the dates continuously.

The stock report includes the following categories for each product each day: the opening stock (Open), warehouse-in quantity (Enter), stock in its highest level (Total), warehouse-out quantity (Issued) and the closing stock (Close). The "Open" of the current day is the "Close" of the day before; "Enter" and "Issued" come from stocklog; "Total" is equal to "Open+Enter"; "Close" is equal to "Open+Enter-Issued" or "Total-Issued".

esProc script is shown below:

A1：Query the database and compute the total Enter and toal Issued of each product each day based on stocklog. As only data grouping and summarizing is needed in this step and the computation is simple, a SQL statement can be used to perform it. Notice that the two parameters – start and end – correspond respectively to the two quotation marks in the SQL statement and represent the time periods passed from the external, which may be a Java program or a reporting tool. Suppose values of start and end are 2014-04-01 and 2014-04-10 respectively, result of A1 will be as follows:

A2=A1.group(Lname)

This line of code groups the result of A1 by Lname, with each group being all the records of the Enter and Issued of each product each day of the specified time period. Please note it is not necessary to summarize each group of data. Result of A2 is shown in the left part of the following figure and detail data of each group are listed to the right.

esProc provides two functions for grouping data – groups and group. Similar to SQL's group by statement, groups groups and summarizes data. While group only groups data without summarizing them, which is a function SQL hasn't.

The final result should include the stock statistics of all days during the time period specified by start and end. But, in the source data, not all days have the warehouse-in and -out records, thus the result of A2 should be aligned with the continuous dates. The following code is to generate the time sequence first.

B2=periods(start,end,1)

periods function can be used to create a time sequence, which requires three parameters: start, end and interval. By default, a sequence of dates will be generated. By using other options, a time sequence of years, seasons, months and ten-day periods can also be created. Result of A3 is as follows:

A3=for A2. This is a loop statement, which performs loop on the result of A2, with each loop aiming at a product.

B3-B6 is a loop body that aligns each product's warehouse-in and -out records with the time sequence in B2 and then computes each product's stock statistics each day and finally append the result to B6. Note that a loop body in esProc is represented visually by an indentation instead of the braces or identifiers like begin/end.

B3=A3.align(A3,Date)

This line of code aligns the current product's warehouse-in and -out records with the time sequence in B2. Note that A3 wears two hats; it is both a loop statement and a loop variable, that is, the current product'’s warehouse-in and -out records. Take item3 as an example, the left part of the following figure shows the records before alignment and the right part shows the records after it: 

B4>c=0

It assigns an initial value – zero – to the variable c, which represents the Open field in each record of the current product. The Open field value of the initial date is zero and will be modified continuously in B5.

B5=B3.new(A3.Lname:Lname,B2(#):LDate, c:Opening, Enter,(b=c+Enter):Total,Issue,(c=b-Issue):Close)

This line of code computes the stock statistics. B3.new(…) means creating a new table sequence, that is, the stock statistics of the current product, based on the result of B3. The new table sequence has 7 fields:

A3.Lname:Lname ---- Fetch Lname field from A3 – the warehouse-in and -out records of the current product. The new field is named Lname.

B2 (#):LDate ---- Insert the time sequence in B2 into the new table sequence in order and make it a new field with the name LDate. Note that # represents the record numbers in A3 and B2(N) represents the Nth record in B2. So B2(#) means inserting B2 into the new table sequence according to the record numbers in A3.

c:Open ---- Make variable c the value of Open field. In the first record, c is zero.

Enter ---- Take the Enter field in B3 directly as a new field. Because the new table sequence is created based on the result of B3, it is unnecessary to rename the new field as Lname field was named.

(b=c+Enter):Total ---- Compute Total field according to the formula Open+Enter. The expression here is surrounded by parentheses to make it clearer.

Issue --- Take Issue field in B3 directly as a new field

(c=b-Issue):Close --- Compute Close field according to the formula Total-Issued. Note that variable c has been modified so that it will be qualified for computing the next record as the value of Open field, which is got according to the business rule that “Open” of the current day is equal to “Close” of the day before.

Take item 3 as example, result of B5 is as follows:

B6=@|B5

Continuously, this line of code appends the result of B5 to the current cell B6, which is represented by @. The final result is as follows:

B6 is the final result of this example.

In addition, the esProc script can be called by the reporting tool or a Java program in a way similar to that in which a Java program calls an ordinary database. The JDBC provided by esProc can be used to return a computed result in the form of ResultSet to the Java main program. Please refer to related documents for details.

esProc Simplifies SQL-style Computations – Multi-level Relationships

Multi-level relationships are one of the complicated SQL-style computations which we often need to deal with during database application development. The relatively abstract SQL JOIN statement is suitable for expressing simple relationships between tables, but once the multi-level relationships are involved, the code becomes rather complicated. esProc uses object references to express the relationships, thus its code is easier to read. The following example will illustrate this.

Table channel stores the correspondence between a certain website's all channels and their parent channels, which are respectively represented by id field and parent field. There are four levels of correspondence at most and root represents the website itself (i.e. the root node). Please list a certain channel's next level, next two and three levels of channels, which will be separated from each other by commas, according to the input parameters. Some of the data of channel are as follows:

esProc code:

A1: Query channel and name the selected data as data, part of which are as follows:

B1: =data.switch(parent,data:id). This line of code establishes a self-join. switch function is used to switch the parent field to the corresponding records in data, as shown below:

After the switch, parent.id can be used to refer to a parent channel, and parent.parent.parent.id represents the channel three levels up. This kind of self-join can be expressed with SQL JOINs, but confusion could arise when there are many levels of relations.

A2: =create(id,level,sub). This line of code creates an empty table sequence for storing the final computed result. Different from data - the explicit definition variable, cell name A2 is by default the variable name of the table sequence. The current value of A2 is as follows:

A3: =data.select(parent.id==arg1 ). This line of code selects records in which the parent channel is equal to parameter arg1, that is, the next-level channel of arg1, from data. arg1 is a pre-defined external parameter, which can be got from a Java program or a report. Suppose the value of arg1 is p1, then the result of A3 is as follows:

B3: =A2.insert(0,arg1,1,A3.(name).string()). This line of code inserts a record into the table sequence in A2. The record's first field value is arg1 (whose value is supposed to be p1), the second one is 1, which represents the first-level sub-channel, and the third one is an expression A3.(name).string(), which represents drawing out the column - name - from A3 and concatenating the column data into stings which separated from each other by commas. The result of B3 is as follows:

A4: =data.select(parent.parent.id==arg1). This line of code is similar to that in A3. It selects from data the next two levels of channels of arg1. Result is as follows:

A5 is similar to A4 by selecting the next three levels of channels of arg1. By doing so, the next N levels of channels will be selected.

As what was done in B3, both B4 and B5 insert new records into the table sequence in A2, with the value of level field being 2 and 3 respectively. When B5 is executed, the final result of this operation can be seen in A2:

If the value of arg1 is c11, the final result will be as follows:

Sometimes more detail data is wanted. For example, list all sub-channels of a certain channel and marking the cascade relationships between them. This operation can be realized using the following code:

The modified code is colored in red. The code in B3 becomes =A2.insert(0,arg1,1,A3), meaning storing the records in A3 in A2 directly. Suppose the value of arg1 is p1, the result will be as follows:

Click the record in sub field and details will be displayed:

It can be seen that the field values in esProc is of genericity, which can store a set of records or a single record. Please note the essential role of switch function is to switch the foreign key to a single record in the primary table.

When B5 is executed, the result of A2 is as follows:

A6: =A2.(~.sub.new(A2.id,A2.level,id:subid,name)). This line of code joins values of id field and level field to members of each set of records of sub field in A2. A2.() means performing computation on A2, "~" represents each record in A2 and ~.sub represents the sub field of each record (a set of records). new function is used to generate a new table sequence which consists of the id field, level field as well as the id field and name field of sub field. The computed result of A6 is as follows:

A7: =A6.union(). This line of code concatenates all groups of records in A6 together to form the final result:

In some other occasions, all sub-channels of each channel need to be listed directly rather than using parameters. This operation can be realized with esProc's for statement. The corresponding code is shown below:

for data.(id) in A3 means fetching each record of id field of data by loop. The loop variable can be represented by cell A3 where for statement settles. The working scope of a loop statement is determined by the indentation, which is B4-C6 in this case. The final result is in A8 and some of its data are as follows:

In addition, an esProc program can be called by a reporting tool or a Java program in a way similar to that in which a Java program calls an ordinary database. The JDBC provided by esProc can be used to return a computed result of the form of ResultSet to the Java main program. For more details, please refer to the related documents. 

esProc Simplifies SQL-style Computations – Relational Computation on Groups

During developing database applications, we are often faced with complicated SQL-style computations like relational computation on multilayered groups. As SQL requires summarizing during data grouping and it doesn't support object-style relational access, it is difficult to deal with these computations. We have to resort to an advanced technique of window functions nested with multilayered subqueries. esProc, however, can handle them more easily by supporting real grouping and visual object-style relational access.

In practice, there are a lot of cases in which relational computation on groups is needed, such as the one presented on the website http://forums.bit-tech.net/showthread.php?t=207052. Based on this practical example, we have designed a more common one for illustrating in detail how esProc works to realize the relational computation on groups.

Computing target: Query branches of a DVD store where there are less than four categories of DVD copies.

Data Structure：Branch Table stores information of the branch stores. DVD Table stores titles and categories of DVDs, in which DVDs, like Transformers IV, are virtual data items instead of physical disks. DVDCopy Table stores physical DVD copies branch stores have. Note: DVDCopy Table is related to Branch Table through BranchID field, and to DVD Table through DVDID field. The following is part of the data.

Branch Table：

DVD Table：

DVDCopy Table：

Description：

1. Computed results should be certain records in Branch Table.

2.  That the Status field of a record in DVDCopy shows Miss means the DVD is missing, and that a record’s LastDateReturned field is empty means the DVD has been rented out but not returned. Obviously the DVDs that are missing and haven't been returned are outside of our computing objects and should be filtered away.

3.  We should consider the situation that there may be certain branch stores whose information isn't included in DVDCopy Table, though it is rarely seen.

Solution:

1. Select valid, existing DVD copies the branch stores have from DVDCopy Table.

2. Group DVDCopy Table by BID. Each group will contain all DVD copies a branch store has.

3. Select the DVDs corresponding to the DVD copies each branch store has, and compute the number of categories to which these DVDs belong.

4. Select branch stores where the number of categories of existing DVDs is less than four. These branch stores are eligible.

5.  Select branch stores which DVDCopy Table hasn't. They are also eligible.

6.  Combine the two kinds of eligible branch stores.

Code written in esProc:

A1-A3：Query data from three tables in the database. The three tables are made variables which are named respectively as Branch, DVD and DVDCopy. Computed results are as follows:

A4： Switch the DVDID field and BID field in DVDCopy Table to corresponding records in DVD Table and Branch Table respectively. Note: This step is the basis of object-style relational access, which requires the use of switch function. After computing, DVDCopy becomes as follows:

Fields in blue have corresponding records. Click one and you can see the details, as shown in the following figure:

Now we can perform object-style relational access only with the operator ".". For instance, DVDCopy.(DVDID). (Category) represents the category of each DVD copy, and DVDCopy.(BID) gets the detailed information (complete record) about the branch store corresponding to each DVD copy.

A5：=DVDCopy.select(Statues!="Miss" && LastDateReturend!=null). This line of code aims to filter away the DVD copies that are missing and that haven’t been returned. After the code is executed, values of A5 are shown as follows:   

A6：=A5.group(BID) is to group data in A5 by BID, with each row representing all DVD copies a branch store has. The result is as follows:

Click the data in blue and you'll see members of each group: 

It can be seen that group function only groups data, but doesn't summarize the data at the same time. In this point, it is different from the function for grouping in SQL. Sometimes, we need to further process the grouped data, rather than simply summarizing them. To do this, esProc's group function is more convenient to use, as shown in the code in A7:

A7：=A6.new( ~.BID:BonList, ~.(DVDID).id(Category).count():CatCount )

The above line of code computes the number of categories of DVD copies to which each branch store corresponds. new function can generate a new object A7 based on the data in A6. A7 has two columns: BonList and CatCount. BonList originates directly from column BID of the grouped data in A6, and CatCount originates from column DVDID of the grouped data. There are three steps to compute CatCount: ~.(DVDID) finds the DVD records corresponding to all DVD copies each branch store has; id(Category) removes repeated records of Category from these DVD records; and count() computes the number of categories. The result is as follows:

That is, branch store B002 has three categories of DVD copies, B003 also has three categories and B001 has four categories.

A8：A7.select(CatCount<4).This line of code makes query to select branch stores whose CatCount is less than 4. The result is as follows:

The above branch stores that are in short supply are computed according to DVDCopy Table. But maybe some branch stores with serious supply shortage are not in the DVDCopy, such as the cases that all the DVD copies in the branch store has been rented out, or that the branch store hasn't any DVD copies. So these branch stores should also be counted. The code for this step is as follows:

A9：=A8.(BonList) | (Branch \ A7.(BonList))

In the above code, operator "|" represents the union operation of two data sets (which can be replaced by union function); operator "\" represents the complement operation (which can be replaced by diff function). A8.(BonList), Branch and A7.(BonList) represent respectively branch stores with supply shortage in DVDCopy Table, all branch stores and branch stores which are included in DVDCopy Table. Their respective values are:

A9 computes the final result of this example. Its values are:

Or we can realize the computation indirectly. For instance, first compute "branch stores that are not in short supply", and then compute the complement of the result and Branch Table. The final result should be the same as that of A9.

Please note variables like A8 or Branch cannot be used to represent a data set in SQL because it doesn't support explicit set. Thus the simple code in the above have to be replaced by lines of complicated SQL statements.

Besides, esProc can be called by a Java program. The method is similar to that with which a Java program calls an ordinary database. The JDBC provided by esProc can be used to return a computed result of the form of ResultSet to Java main program. For more details, please refer to the related documents 

esProc Simplifies the SQL-style Computations– Get Top N Rows of Each Group

The operation of getting the top N rows of data is common, such as the biggest salary raise of each employee, the three lowest scores in playing golf and the five days of each month when each product has its biggest sales. SQL resorts to advanced techniques, like window functions and keep/top/row number, to solve this kind of problem. Therefore the code is quite difficult. And as many databases (like MYSQL) doesn't support these advanced techniques, they can only realize this operation using complicated JOIN statement and nested subquery.If multi-layered groups or multi-level relationships are involved, the computation will become even more complicated. 

Yet esProc's top function can select the top N rows of data of each group according to the row numbers as well as the maximum and minimum values, thus the computation will be made simpler. The following example will teach you how esProc works. 

golf, a database table, stores scores members have got in playing golf. Please select the three highest scores each member has had. Some of the data are as follows:

esProc Code:

A1：Select data from the database. Click the cell and you can see the result in detail:

A2：=A1.group(User_ID). The code groups the selected data in A1. Result is as follows:

As shown in the above figure, the selected data have been divided into multiple groups by User_ID, with each row representing a group. Click the hyperlinks in blue and you can see members of each group:

A3：=A2.(~.top(-Score;3)). The code gets the top three records of Score field of each group. Here “~” represents each group of data and ~.top() represents that top function is used to compute each group of data in order. top function can get the top N records of a data set. For example, top(Score;3) means sorting the records in ascending order by Score field and getting the top 3 records (i.e. the three smallest values); top(-Scroe;3) means sorting the records in descending order by Score field and getting the top 3 records (i.e. the three biggest values); and top(#;3) means getting the top three records according to the original order. The computed result of this step is as follows:

A4：=A3.union(). It performs union operation on each group of data. Result is as follows:

The above four steps can also be combined into one step for the convenience of maintenance and debugging. Thus the code will be db.query("select * from golf").group(User_ID). (~.top(-Score;3)).union().

In addition, esProc program can be called by the reporting tool or a Java program in a way similar to that in which a Java program calls an ordinary database. The JDBC provided by esProc can be used to return a computed result in the form of ResultSet to the Java main program. Please refer to related documents for more details.