In data analysis, we often need to group data and then compute the aggregate value for each group, or perform other computations on each group. esProc allows using groups function to compute aggregate values for groups of data, as well as grouping records of a table with group function for use in subsequent computations. However, external memory grouping is required when data being grouped is big and cannot be loaded to the memory in its entirety and thus the above-mentioned methods for data summarizing and grouping become useless.
A table with big data containing1,000,000 records is used to simulate call duration records of mobile phone users. It is stored in the binary file PhoneBill:
| A | B | C | |
| 1 | 1000000 | 1000 | =A1/B1 |
| 2 | =file(“PhoneBill”) | =create(Phone,DateTime,Duration) | |
| 3 | 2014-8-1 00:00:00 | 2014-9-1 00:00:00 | =interval@s(A3,B3) |
| 4 | for C1 | for B1 | =rand(10000)+12340000 |
| 5 | =rand(C3) | ||
| 6 | =after@s(A3,C5) | ||
| 7 | =if(rand()>0.9,rand(20)+1,1) | ||
| 8 | >B2.insert(0,C4,C6,C7) | ||
| 9 | >A2.export@ab(B2) | ||
| 10 | >B2.reset() | ||
| 11 | =A2.cursor@b() | =A11.fetch(1000) | >A11.close() |
Mobile phone numbers used for the simulation are 8-digit integers whose first four digits are the fixed 1234 and the rest is randomly generated. The records start on a randomly generated day of August in 2014. Values of call duration are integers randomly generated with a 90 percent chance of being one minute. The maximum limit is 20 minutes. After the data file is prepared, B11 fetches the first 1,000 rows:
Perform the following computations based on the data in PhoneBill:
① Compute total call duration of all users and their average call duration in each day of August.
② Compute every user’s total call duration in August.
③ Store each day’s call records in a file.
④ Find the numbers of five users who make the longest call in total for each day of August.
To solve the first problem, data needs to be grouped and summarized by DateTime. As there are only 31 days in the month of August, the result set won’t contain big data and the aggregate operation can be performed in the memory:
| A | |
| 1 | =file(“PhoneBill”) |
| 2 | =A1.cursor@b() |
| 3 | =A2.groups(date(DateTime):Date;sum(Duration):TotalDuration,count(~):Count) |
| 4 | =A3.derive(TotalDuration/Count:AvgDur) |
A2 generates a cursor for the binary text data in A1. A3 groups and summarizes data from the cursor. The result is as follows:
Since there is not so much data in the result, the cursor data only needs to be traversed once. Here groups function is adequate to perform the group and aggregate operation and external memory grouping is not necessary. In fact the result set can be directly acquired without the cursor, thanks to the reasonable amount of data. For more related information, please see esProc External Memory Computing: Principle of Grouping. To compute the average call duration in a day, we need to first get the total call duration and the total number of calls in this day. The result of A4 is as follows:
It is a different case for the second task. As the number of users is far more than the number of days in August, we should consider if the result of data grouping and summarizing can be returned to the memory all at once. There are 10,000 users at most in this example, which, actually, is not a large number. It is merely used to illustrate the computation of big result set. Here we suppose the memory can only hold 1,000 records:
| A | |
| 1 | =file(“PhoneBill”) |
| 2 | =A1.cursor@b() |
| 3 | =A2.groupn(Phone;sum(Duration):TotalDuration;1000) |
| 4 | =A3.fetch(1000) |
| 5 | >A3.close() |
groupn function is needed to handle the grouping and summarizing of big data. The function fetches data from cursor and performs group operation in batches according to the pre-specified number of buffer rows, using the external memory. By Phone and according to the specified 1,000 buffer rows, A3 groups and aggregates cursor data, computing the total call duration in the month. The result of grouping and summarizing big data is still a cursor. Fetching data from it is no different to fetching data from any other cursor. The result of A3 is as follows:
A4 fetches the records of the first 1,000 users. Their total call duration is as follows:
By the way, if data in the cursor hasn’t been entirely fetched, cs.close() function needs to be called to clean the temporary files from the external memory in time.
The third task requires grouping data by the DateTime first before storing data of each day in a file:
| A | B | |
| 1 | =file(“PhoneBill”) | |
| 2 | =A1.cursor@b() | |
| 3 | =A2.groupn(day(DateTime)) | |
| 4 | for A3 | =file(“PhoneBill”+string(#A4)) |
| 5 | >B4.export@b(A4) | |
| 6 | =file(“PhoneBill4”) | =A6.cursor@b() |
| 7 | =B6.fetch(1000) | >B6.close() |
Different from other grouping operations on big data, groupn function requires specifying group numbers directly in the grouping expression. In A3, the dates in DateTime are used as the group numbers. Different from the previous result of data grouping and summarizing, A3’s function returns a sequence of cursors. Each cursor corresponds to a group:
The fourth and fifth lines of code store cursor data in each day in a file. A6 selects four days’ data, and A7 fetches the first 1,000 records from it:
For the fourth problem, group data by DateTime, and then group and summarize data of each group. Finally, select the desired mobile phone numbers from the aggregate results:
| A | B | |
| 1 | =file(“PhoneBill”) | |
| 2 | =A1.cursor@b() | |
| 3 | =A2.groupx(day(DateTime)) | =create(Date,Phone) |
| 4 | for A3 | =A4.groupx(Phone;sum(Duration):TotalDuration;1000) |
| 5 | =B4.groups(;topx([-TotalDuration,Phone],5)) | |
| 6 | >B3.insert(0,#A4,B5.#1.(~(2))) |
A3 directly specifies dates of DateTime as group numbers in the grouping expression, and returns a sequence of cursors:
The code from the fourth to sixth line loops through cursor data of each group to compute the total call duration per user per day, and according to the aggregate result, finds records of users whose call duration is in the top five. Please note that when aggregate function topx is used to sort data in descending order, just add a negative sign before the sorting expression, like –TotalDuration in B5. Select data of five numbers that have made the longest call and store it in the table sequence in B3. After loops are finished, the final result can be viewed in B3 as follows:
An alternative choice is to use the files generated from handling the third problem, instead of grouping all the original data.