esProc Helps Process Structured Text in Java – Grouping and Summarizing

Following problems will arise if you perform conditional filtering on text files in Java alone: 

1. The text file is not a database, so it cannot be accessed by SQL. The code needs to be modified if the expression of grouping and summarizing is changed. Besides, if you want a flexible expression, you have to self-program the dynamic expression parsing and evaluating, resulting in a great amount of programming work.

2. The grouping result produced during traversing will be recorded. If the result is small in size, it can be stored directly in the memory; if the size of the result is too big, an intermediate result will have to be stored as cache files which should be merged later. The process will be quite complicated.

These problems can be solved with ready-made class library by introducing esProc to the programming in Java. Now let’s look at in detail how this will happen.

The text file employee.txt has the employee information. It is required to group by DEPT, count the employees and sum up the total amount of their salary in each group.

The format of text file employee.txtis as follows:

EID   NAME       SURNAME        GENDER  STATE        BIRTHDAY        HIREDATE         DEPT         SALARY

1       Rebecca   Moore      F       California 1974-11-20       2005-03-11       R&D          7000

2       Ashley      Wilson      F       New York 1980-07-19       2008-03-16       Finance    11000

3       Rachel      Johnson   F       New Mexico     1970-12-17       2010-12-01       Sales         9000

4       Emily         Smith        F       Texas        1985-03-07       2006-08-15       HR    7000

5       Ashley      Smith        F       Texas        1975-05-13       2004-07-30       R&D          16000

6       Matthew Johnson   M     California 1984-07-07       2005-07-07       Sales         11000

7       Alexis        Smith        F       Illinois       1972-08-16       2002-08-16       Sales         9000

8       Megan     Wilson      F       California 1979-04-19       1984-04-19       Marketing        11000

9       Victoria    Davis        F       Texas        1983-12-07       2009-12-07       HR    3000

10     Ryan         Johnson   M     Pennsylvania    1976-03-12       2006-03-12       R&D          13000

11     Jacob        Moore      M     Texas        1974-12-16       2004-12-16       Sales         12000

12     Jessica     Davis        F       New York 1980-09-11       2008-09-11       Sales         7000

13     Daniel       Davis        M     Florida      1982-05-14       2010-05-14       Finance    10000

…

Implementation approach: call esProc script with Java, import and compute the data, then return the result in the form of ResultSet to Java. Because esProc supports dynamic expression parsing and evaluating, it enables Java to process data from the text file as flexibly as SQL does.

For example, you are required to group by DEPT, count the employees and sum up the total amount of their salary in each group. esProc can use an input parameter "groupBy" as the dynamic grouping and summarizing condition, which is shown below:

The value of "groupBy" is DEPT:dept;count(~):count,sum(SALARY):salary. And the code written in esProc is as follows:

A1: Define a file object and import the data, with the first row being the title. tab is used as the field separator by default. esProc's IDE can display the imported data visually, as shown in the right part of the above figure.

A2: Group and summarize according to specified fields, using macro to realize parsing the expression dynamically. The "groupBy" in this process is an input parameter. In executing, esProc will first compute the expression enclosed by ${…}, then replace ${…} with the computed result acting as the macro string value and interpret and execute the code. The final code to be executed in this example is=A1.groups(DEPT:dept;count(~):count,sum(SALARY):salary).

A3: Return the eligible result set to the external program.

You just need to modify the parameter –"groupBy" when grouping fields are changed. For example, you are required to group by DEPT and GENDER, count the employees and sum up the total amount of salary in each group. The value of "groupBy' can be written as DEPT:dept,GENDER:gender;count(~):count,sum(SALARY):salary.

The simple summarizing on all data can be regarded as a special case of grouping and summarizing operation. For example, when counting the number of employees and summating the total amount of salary, the value of parameter "groupBy" can be written as ;count(~):count,sum(SALARY):salary. That the parameter part for grouping is omitted means all data is put into one group. The advantage by doing so is that multiple summarizing results of these data can be computed by traversing them once.

The code of calling this piece of code (which is saved as test.dfx) in Java with esProc JDBC is as follows:

// create a connection usingesProcJDBC

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

//call the program in esProc (the stored procedure); test is the name of file dfx

com.esproc.jdbc.InternalCStatementst;

st =(com.esproc.jdbc.InternalCStatement)con.prepareCall("call test(?)");

//set the parameters

st.setObject(1,"DEPT:dept,GENDER:gender;count(~):count,sum(SALARY):salary");// the parameters are the dynamic grouping and summarizing fields

//execute the esProc stored procedure

st.execute();

//get the result set

ResultSet set = st.getResultSet();

If the script is simple, the code can be written directly into the program in Java that calls the esProc JDBC. It won’t be necessary to write a special script file (test.dfx):

st=(com. esproc.jdbc.InternalCStatement)con.createStatement();

          ResultSet set=st.executeQuery("=file(\"D:/employee.txt\").cursor@t().groups(DEPT:dept,GENDER:gender;count(~):count,sum(SALARY):salary)");

This piece of code in Java calls a line of code in esProc script directly, that is, get the data from the text file and return the result set to set– the object ofResultSet.

         

If the result set of grouping is still too big to be entirely loaded to the memory, groupx statement will return the grouping result using file cursor. Thus the code written in esProc will be modified like this:

groups function puts the grouping and summarizing result completely in the memory.groupx will write the result into temporary files if the grouping and summarizing result is beyond the boundary of buffer rows, redistribute the memory, and then merge the temporary files. Here the parameter 1000000 refers to buffer rows. The principle of assigning value to it is to make the best of the memory, trying to reduce the number of temporary files as far as possible. The number of temporary files is related to the size of both the physical memory and the record, and should be evaluated during programming. Generally, the recommended number is between magnitudes of several hundred thousand to a magnitude of one million.

Though cell A3 returns a cursor, instead of a result set, to Java, it is no need to modify the calling program of Java. esProc will automatically fetch the data corresponding to the cursor while Java is traversing the data with ResultSet.

This piece of program can be further improved to support filtering before and after the grouping. Now the role of the program is similar to that of where and having in SQL. For example, the statistical object becomes female employees (GENDER=="F"), and it is required to retain only the departments where the number of female employees is greater than ten after grouping and summarizing operation. The code is as follows:

Cellset parameters are made absent here for easy understanding, yet the code is the same as that in the above:A2.groupx(${groupBy}). The parameter of select function can be written as the macro which will be passed from Java program. 

Comparative Test Report on esProc, Hive, Impala Clusters (part III)

5.2 Use Case for Group

This case is used for testing performance of big data grouping, while separately taking integer, digital, string, date and other grouping into account. The number of grouped results in this case is relatively small, less than physical memory of node.

Use Case Number Description:

Tested Results:

Data Feature:

In general, esProc, Impala and Hive degrade in turn. At top level of performances, esProc is 2-3 times higher than Impala, while 3-4 times higher than Hive. Apart from Use cases 32, 36, 40 which are used for wide table with large amount data at this point, Hive presents higher performance than Impala.

Principle and Use of External Memory Grouping in esProc

After data are imported from a data table, they are usually grouped as required and grouping and summarizing result is needed to be worked out. In esProc, groups function is used to compute the result of data grouping and summarizing; or the function will first group the data, then further analysis and computation are to be performed later.

But the case will be different in processing huge data, for the records cannot be loaded to the memory all together and distributed into each group. Other times the number of groups is huge and the grouping and summarizing result cannot even be returned all at once. In these two occasions, the external memory grouping is required.

1.Grouping with cursor by directly specifying group numbers

Let’s create a big, simple data table containing employee information, which includes three fields: employee ID, state and birthday. The serial numbers are generated in order and the states are written in their abbreviated forms obtained arbitrarily from the STATES table of demo database; birthdays are the dates selected arbitrarily within 10,000 days before 1994-12-31.The data table will be stored as a binary file for convenience.

Altogether 1,000,000 rows of data are generated. The result of reading the 50,001^th ~51,000^th rows of data with cursor can be seen in C10 as follows:

In the following, we'll take the generated data file, BirthStateRecord, as an example to explore how to group in cursor computing by directly specifying group numbers. Because the data of the big data table cannot be loaded all together into the memory, we cannot perform grouping on it as we do on an ordinary table sequence. To solve this problem, esProc offers cs.groupx(x) function which can distribute the records in cursor cs according to the computed result of expression x into groups with specified serial numbers and return the sequence of cursor. For example:

To explain the way in which cs.groupx(x) function performs grouping by using external memory to specify group numbers, the code will be executed step by step by clicking in the debugging area of the toolbar until A6. A2 creates a cursor with the binary data file BirthStateRecord. A4 creates a sequence using the states' abbreviations in STATES table.A5 uses groupx function to groupdata of the cursor; in this process, we need to find the corresponding serial numbers of the groups in A4 according to states and make them as the group numbers. During the execution of groupx function in A5,a temporary file will be generated for each group to record the grouping result and the sequence of temporary cursor files will be returned as follows:

While the code in A5 is executed, external files are generated in the directory of temporary files:

In the operation of groupx function, the number of temporary files equals that of the groups of records. We can import data from one of the temporary files:

The data A2 imports are as follows:

It can be seen that the data of a temporary file is, in fact, the employee information of a state. Here it is the data of the state of Missouri. Click  on the toolbar in the previous cellset file and go on with the execution of this cellset. In A8, when all cursor files are closed, the temporary files will be deleted automatically. A6 reads from the first cursor file the employee information of the state of Alabama as follows:

A7 works out the grouping and summarizing result of the 22^nd group using groups function, that is, the number of employees from the state of Michigan:

It is thus clear that a sequence consisting of temporary cursor files will be returned in grouping records of cursors using directly specified group numbers. Each cursor file contains the records of a group and the data in a cursor can be further processed.

2.Grouping and summarizing result sets of huge data 

When grouping data of cursors, most of the time we needn't to know the detailed data of each group. What we only need is to get the grouping and summarizing result. To get the number of employees of each state from BirthStateRecord, for example, we use groups function to compute the grouping and summarizing result:

Thus we can get the result in A3:

Here we notice that the groups function for grouping and summarizing will return a table sequence of the result after the computation is completed. In the operation of processing massive data, sometimes it is required to produce a great many groups and the result set of grouping and summarizing itself is too big to be returned. Such as the telecom company makes statistics of each customer’s bill; online shopping malls make statistics by groups about the sales of each commodity, and the like. In these cases, the use of groups function may result in a memory overflow. We can use groupx(x:F,…;y:F,…;n) function instead to perform grouping and summarizing with the help of external memory. In the function, n represents the number of rows in buffer area. For example:

Still, the code is executed step by step until A4. In A3,groupx function uses external memory to perform grouping and summarizing. In cursor computing,groupx function is used in the operations of both grouping and summarizing with external memory and grouping by directly specified group numbers. The difference of the two operations lies in the parameters. A3 performs grouping by employees' birthdays, then summate the number of employees born each day. In the operation, the number of rows in buffer area is set as 1,000. The result returned by A3 is a cursor as follows:

After the code in A3 is executed, external files will be generated in the directory of temporary files:

The data of one of the temporary files can be imported:

The data A2 imports are as follows:

The data of A3 is as follows:

It can be seen that each temporary file is the grouping and summarizing result of a part of the data obtained according to employees' birthdays. A larger cursor composed of all temporary files will be merged and returned by esProc. When the temporary files are generated, esProc will select a group number suitable for computing, so the rows of data in the temporary files will be a little more than the number of rows we set in buffer area. Special attention is needed in this point.

Go on with the execution of cellsets in the previous cellset file. When cursors are closed in A5, the temporary files will be auotomatically deleted. A4 fetches the first 1,000 birthdays from the cursor generated in A3, and the numbers of employees of each birth date are as follows:

Methods of Grouping and Summarizing in R Language

The operation of grouping and summarizing includes grouping one or more certain fields of two-dimensional structured data and then summarizing fields of each group. The following will introduce methods of grouping and summarizing in R language through an example. In order to make the example more typical, we’ll set two fields to be grouped and two summarizing operations.

Case description:

Please group data frame orders according to CLIENT and SELLERID, and then summate field AMOUNT and seek its maximum value respectively in each group.

Note: orders contains records of sales orders. Its source can be a database or a file. Such as orders<-read.table("sales.txt",sep="\t", header=TRUE).The first rows of data are as follows: 

Method 1: aggregate function

Code:

result1<-aggregate(orders$AMOUNT, orders[,c("SELLERID","CLIENT")],sum)

result2<-aggregate(orders$AMOUNT, orders[,c("SELLERID","CLIENT")],max)

result<-cbind(result1,result2$x)

Part of the computed result:

Code interpretation:

1.The name aggregate implies that it is a function specializing in grouping and summarizing. Both its input parameters and computed result are data frame and its usage is relatively simple.

2. aggregate function cannot perform multiple summarizing operations on grouped data, thus two lines of code are required to realize the operations of seeking sum and maxrespectively, then their results are combined using cbind. Obviously, the code is not satisfactory in performance and usability.

3. aggregate function has a strange requirement about the order of the fields to be grouped, that is, the fields must be in reversed order. In view of this, the code for grouping CLIENT first and then SELLERID should only be written as orders[,c("SELLERID","CLIENT")]. The code written according to the normal way of thinking will be incorrect instead.

4. Not only the code is written in an unnatural way, but the computed result is weird too by putting filed SELLERID before CLIENT. In reality, the code should be improved in order to make the computed result conform to the business logic.

Summary:aggregate function manages to perform the task after a fashion. But it is not good in performance and usability because the way of coding, computed result and business logic are inconsistent with each other.

Method 2: split+lapply function

Code:

sp<-split(orders,orders[,c("SELLERID","CLIENT")],drop=TRUE)

result1<-lapply(sp,FUN=function(x) sum(x$AMOUNT))

result2<-lapply(sp,FUN=function(x) max(x$AMOUNT))

result<-cbind(result1,result2)

Part of the computed result:

Code interpretation:

1. The role of split function is to group the data frame according to specified fields. No further computation is involved. lapply function can perform the same operation on data of each group. By working with each other, split and lapply can fulfill the task.

2.  Because the grouped data can be reused, this operation performs better than that using aggregate function.

3. As lapply function doesn't support multiple statistical approaches, two lines of code are required too to realize the operations of seeking sum and max respectively, and then use cbind to combine the results. What’s more, this operation requires an extra split function, so instead of enhancing the usability, it reduces it.

4. The grouping order is still unnatural and the code has to be written reversely as orders[,c("SELLERID","CLIENT")].

5.  The computed result needs a lot of modification which brings great inconvenience. It can be seen that the first column of the computed result is, in fact, the "SELLERID.CLIENT". The column needs to be split into two columns whose orders should be exchanged.

Summary:This operation improves some performance but the usability is obviously poor with inconsistency in the aspects of way of coding, business logic and the computed result.

lapply belongs to the family of apply function. Similar functions include sapply and tapply, whose usages differ on parameters. For example:

sp<-split(orders,orders[,c("SELLERID","CLIENT")],drop=TRUE)

result1<-sapply(sp,simplify=FALSE,FUN=function(x) sum(x$AMOUNT))

result2<-sapply(sp,simplify=FALSE,FUN=function(x) max(x$AMOUNT))

result<-cbind(result1,result2)

tapply specializes in data frame, which, by rights, is the most suitable one for fulfilling this task. But it isn't in fact. It applies only to the situation where a single field is required to be grouped. When it is used to group two fields together, the result will be two-dimensional matrix. This requires users to make further complicated processing. For example, the computed result of the line of code tapply(orders$AMOUNT, orders[,c("SELLERID","CLIENT")],function(x) sum(x))is as follows:

Third-party library functions

There are various disadvantages when using R's built-in functions to group and summarize. In response to the problem, we may consider using the third-party library functions, such as reshape, stack, etc. The stability and computational efficiency of these library functions is generally not as good as those of the built-in functions, and the information for their use is not many. Therefore it is difficult for them to fulfil the task. Here we won't go into any example about their use.

Third-party languages

Python, esProc and Perl can also be employed to fulfil this task. All of them can perform grouping and summarizing as well as structured data computing as R language can. We’ll briefly introduce solutions of esProc and python.

esProc

esProc can fulfil this task by simply using groups function. Its syntax is concise and easy to understand, as well as in line with the natural way of thinking. The code is as follows: result=orders.groups(CLIENT,SELLERID;sum(Amount),max(Amount))

The computed result, syntax and business logic are highly consistent with each other in esProc. Some of the computed results are as follows:

Python(pandas)

If python's built-in functions are used to deal with this task, the code will be rather complicated. Here pandas, the third-party function library, comes to help. pandas will first perform grouping operation using groupby function, then summarize using agg function. Its code, which is simpler than R languagebut not as good as esProc,is written like this: result=orders.groupby(['CLIENT','SELLERID']).agg({'AMOUNT':[sum,max]}).

Pandas' computed result and syntax are highly consistent with the business logic. Part of the computed result is as follows:

The step-by-step computational mode of grouping first then summarizing adopted by pandas is not always bad. Such as, it can increase performance in situations where grouping result is reused. esProc can also perform the step-by-step operation, the equivalent code is group(CLIENT,SELLERID).new(CLIENT,SELLERID,sum(AMOUNT),max(AMOUNT)).

A Method of Grouping and Summarizing Data of Big Text Files in R Language

It is common to use R language to group and summarize data of files.Sometimes we may find ourselves processing comparatively big files which have smaller computed result and bigger source data. We cannot load them wholly to the memory when we need to compute them. The only solutions could be batch importing and computing as well as result merging. We’ll use an example in the following to illustrate the way of R language to group and summarize data from big text files.

Here is a file, sales.txt, of 1G size, which contains a great number of records of sales orders. We want to group field CLIENT and summarize field AMOUNT. "\t"is used in the file as the column separator. The first rows of data are as follows:

R's solution:

	con <- file("E: \\sales.txt", "r")
	result=read.table(con,nrows=100000,sep="\t",header=TRUE)
	result<-aggregate(result[,4],list(result[,2]),sum)
	while(nrow(databatch<-read.table(con,header=FALSE,nrows=100000,sep="\t",col.names=c("ORDERID", "Group.1","SELLERID","x","ORDERDATE")))!=0) {
	databatch<-databatch[,c(2,4)]
	result<-rbind(result,databatch)
	result<-aggregate(result[,2],list(result[,1]),sum)
	}
	close(con)

Part of the computed result:

Code interpretation:

The 1^stline: Open the file handle.

The 2^nd ~ 3^rdline: Import the first batch of 100,000 rows of data, group and summarize them and save the result in result.

The 4^th ~ 8^thline: Import data by loop, with 100,000 rows of data per batch, and store them in the variable databatch. Then get the second and fourth field, i.e. "CLIENT" and "AMOUNT", merge databatch into result, and execute grouping operation.

It can be seen that, at a certain moment, only databatch,which includes 100,000 rows of data, and result, the summarizing result, have memory usage. Usually, the size of the latter is small and will not result in a memory overflow.

The 11^thline: Close the file handle.

Matters needing attention:

Data frame. Because the data frame of R language cannot directly perform the computing of big files, loop statement is necessary to help to do the job in this occasion. The steps are: import a batch of data and merge them into the data frame result; group and summarize result and then import the next batch of data. You can see that this part of code of loop statement is a little complicated.

Column name. As the first row of data is the column name, header=TRUE can be used in the first batch of data to directly set the column name. But the subsequent data hasn't column names and header=FALSE should be used to import data. The default column names are V1, V2 and so forth when header=FALSE is used. But the default column names are Group.1 and x after grouping and summarizing are executed, and col.names is needed to change the column names in order to maintain structure consistency both before and after grouping and summarizing and set the stage for the subsequent merging. The code about column names is worth our notice because it is easy to get wrong.

Alternative solutions:

Python, esProc and Perl can also perform the same operation. They can execute the grouping and summarizing of data from big text files and the subsequent structured data computing as R language does. We'll briefly introduce the coding methods used by esProc and Python.

esProc can process data in batches automatically, which requires no manual control from the programmers by loop statement and produces quite simple code:

Cursor is a data type used for structured data computing in esProc. Its usage is similar to that of the data frame, but it is better at processing big files and performing complicated computations. What's more, @t option in the code indicates that the first line of the file is the column name. So it is convenient to use the column name directly in subsequent computation.

Python's code structure, which also requires manual loop control, is similar to that of R language. But Python itself hasn't the structured data type, like data frame or cursor, so its code is executed in a lower level:

	from itertools import groupby
	from operator import itemgetter
	result = []
	myfile = open("E:\\sales.txt",'r')
	BUFSIZE = 10240000
	myfile.readline()
	lines = myfile.readlines(BUFSIZE)
	value=0
	while lines:
	for line in lines:
	record=line.split('\t')
	result.append([record[1],float(record[3])])
	result=sorted(result,key=lambda x:(x[0]))                #the sorting before grouping is executed
	batch=[]
	for key, items in groupby(result, itemgetter(0)):    # group using groupBy function
	value=0
	for subItem in items:value+=subItem[1]
	batch.append([key,value])                 # finally, merger the summarizing results into a two-dimensional array
	result=batch
	lines = myfile.readlines(BUFSIZE)
	myfile.close()

Except for the above two-dimensional array, Python can execute the operation with the third-party packages. For example, pandas has the structured data object similar to the data frame. pandas simplifies the code in a similar way as R language. But it lacks sufficient ability to perform big file computing, thus loop statement is still needed while programming. 

Alignment Grouping and Enumeration Grouping in esProc

Grouping records is often required during presenting and analyzing data in databases. Though records can group the designated fields by using Group By statement in SQL statements, this type of grouping serving the purpose of summaryis too simple to deal with some complex situations, like grouping according to designated order or grouping with identical records. But with esProc, we can use alignment grouping function P.align@a()or enumeration grouping function P.enum() to manage various complicated requirements for grouping.

We'll look at how to use alignment grouping and enumeration grouping in esProc through the following example.

1. Alignment grouping

Database table LIQUORS has information of some wines:

But how can we group these wines by varieties in the order of Vodka, Gin, Rum, Whisky, Brandy, Tequila and Cordial?

We can manage it easily in esProc with P.align@a() function:

We just need to execute alignment grouping on records in A1 by using the field TYPE according to the designated sequence A2. The computed results in A3 are as follows:

Among these results, each group is made up of records in A1. Double-click to see more.

When P.align@a() is at work, it will look up every member of the record sequence P and put it into the group that meets the condition. A member cannotappear more than once during this process. If we want to modify A2 and add another Gin as the grouping value as follows: 

Seen from the computed results of A3, alignment grouping will only put records to the first eligible group and make the last group an empty one: 

esProc's P.align() function provides many options that can manage various situations in alignment operations.

If @n is used by P.align@a() function, a new group for taking all the ungrouped records will appear except those designated ones. Because @n option is only used in alignment grouping operations, here @a can be omitted: 

Computed results of A3 are as follows:

If @a option is not used by P.align() function, only the first value of each group will be retained: 

Computed results of A3 are:

If @p option is used by P.align@a() function, groups won't store records except their sequence numbers:

Computed results of A3 are:

It can be seen that only sequence number of each record is stored in the group.

If @s option is used by P.align() function, result returned will be a record sequence, that is, the re-sorted records in the table according to designated sequences. This is similar to the result obtained by using order by clause in SQL statements, only with a different sorting principle:

Now the computed results of A3 are as follows:

Note that when @s option is at work, it is invalid to introduce @a option at the same time.

If @b option is used by P.align@a() function, binary search will be used for alignment grouping. Using binary research will bring higher efficiency, but meanwhile, the alignment sequence designated by grouping must be ordered: 

Sort A2 in A3,and the computed results are as follows when @a option and @b option are used simultaneously:

While A4 doesn't execute sorting of A2, so the results of alignment grouping are incorrect when @a option and @b option are used simultaneously:

If grouping expressions directly compute each record's place in alignment grouping, then P.align@a(n,y) function can be used:

Because the places have been directly set, the specific value of each member in grouping value sequence of align function won't affect the result, and we can use the simplest sequence to(n) to complete alignment grouping according to the number of groups. Here to(n) can be abbreviated to n. Expressions in A3 and A4 have the same computed results:

If @r option is used by P.align@a(n,y) function, each record will correspond to a sequence of group numbers and can be put into more than one group. Such as:

Computed results of A3 are:

P.align() function can manage more complicated jobs through combinations of its options.

2. Enumeration grouping

Enumeration grouping is, in fact, a type of alignment grouping. Its alignment basisis the computed results of designated expressions.

For example, divide the wine information in table LIQUORS into three groups by names: ?<"D",?<"K", and ?>="K". First create a sequence according to the conditions, then execute enumeration grouping on all information of wines by using P.enum() function according to the condition sequences: 

Computed results of A3 are:

By default, records of P only appear once when P.enum() function is used for grouping. It can be noticed that the grouping condition of the second group is ?<"K", and though the records in the first group satisfy this condition, they won't appear again in the second group.

Various options can be used by enum function to realize its different functions.

If @r option is used by P.enum() function, the same records are allowed to appear in more than one groups:

Computed results of A3 are as follows:

It can be seen that, for the time being, the second group contains all eligible records, including those in the first group.

If @p option is used by P.enum() function, the groups will store only the corresponding record numbers: 

Now the computed results of A3 are as follows:

It can be seen that each group stores only the corresponding record numbers.

If @n is used by P.enum() function, a new group will be appended in the end to take the ungrouped records. If the third grouping condition is modified and @n option is used in enumeration grouping:

Results of A3 are as follows:

In actual grouping result, in contrast to A2's condition sequences,one extra and last group that stores all the ineligible records appears.

P.enum() function's three options can be combined in different ways to meet various needs.