User Behavior Analysis in Practice 1: Conventional Grouping and Aggregation
Target task
We have a user events table T. Below is its structure and part of its data:
| Time | UserID | EventType |
| 2022/6/1 10:20 | 1072755 | Search |
| 2022/6/1 12:12 | 1078030 | Browse |
| 2022/6/1 12:36 | 1005093 | Submit |
| 2022/6/1 13:21 | 1048655 | Login |
| 2022/6/1 14:46 | 1037824 | Logout |
| 2022/6/1 15:19 | 1049626 | AddtoCart |
| 2022/6/1 16:00 | 1009296 | Submit |
| 2022/6/1 16:39 | 1070713 | Browse |
| 2022/6/1 17:40 | 1090884 | Search |
Fields in table T:
| Field name | Data type | Description |
| Time | Datetime | Time stamp of an event, accurate to milliseconds |
| UserID | Integer | User ID |
| EventType | String | Types of events |
Computing task:
Find the number of events under each type and that of distinct users who perform that type of event in the specified time period.
Techniques involved:
1. Use binary file storage instead of database storage.
2. Use parallel processing during traversal.
Sample code
1. Dump data from database and store it in a binary file
Stocked data: the data is retrieved from the database and written to a bin file:
| A | |
| 1 | =connect("demo").cursor@x("select * from T") |
| 2 | =file("T.btx").export@b(A1) |
A1 Connect to the database, retrieve data from table T and generate a cursor. @x option enables to automatically close database connection after data retrieval finishes.
A2 Export A1’s data to bin file T.btx. @b option enables writing data to a binary file.
Newly-increased data: the newly-increased data can be obtained using a filtering condition in SQL when there is any that needs to be appended to an existing bin file. @a option enables appending data to a bin file.
The newly-increased data can be identified through time stamp. Each day after 0 o’clock we append the newly-generated data in the past day to a bin file:
| A | |
| 1 | =connect("demo").cursor@x("select * from T where Time>=? && Time<?",date(now()-1), date(now())) |
| 2 | =file("T.btx").export@ba(A1) |
A1 Get data generated in the previous day through filtering condition and store it in a cursor.
A2 Fetch A1’s data from the cursor and append it to bin file T.btx. @a enables data appending; without it the existing bin file will be overwritten.
2. Perform grouping and aggregation on a bin file
Suppose we need to summarize data that falls in between 2022-03-15 and 2022-06-16:
| A | |
| 1 | =file("T.btx").cursor@mb() |
| 2 | >start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
| 3 | =A1.select(Time<=end && Time>=start).groups(EventType; count(1):Num, icount(UserID):iNum) |
A1 Generate cursor for bin file "T.btx". @m enables parallel processing through a multicursor; set the default number of parallel threads in esProc configuration file raqsoftconfig.xml, or just write f.cursor@m(n) where n represents the number of parallel threads. It is recommended that n should be less than the number of computer’s CPU cores, otherwise speed will be lower.
A2 Generate two variables – start and end – to filter data by the time stamp. The variables will be passed in through parameters in real-life computations.
A3 Perform filtering and grouping & aggregation on A1’s cursor. The grouping field is EventType; count(1) performs a simple count while icount(UserID) finds the number of unique UserIDs. Note that no matter how many operations are performed on a cursor, like multiple rounds of filtering, sorting or grouping, they will be executed together at one time – data is retrieved once through one traversal – to get the final result.
Execution result:
| EventType | Num | iNum |
| AddtoCart | 1845674 | 175476 |
| Browse | 3578901 | 348791 |
| Login | 4033000 | 393400 |
| Logout | 4033000 | 393400 |
| Search | 2947931 | 257539 |
| Submit | 867345 | 83375 |
