User Behavior Analysis in Practice 2: Redundant Grouping Key Field
Target task:
We have a user events table T. Below is its structure and part of its data:
| Time | UserID | EventTypeID | EventType |
| 2022/6/1 10:20 | 1072755 | 3 | Search |
| 2022/6/1 12:12 | 1078030 | 2 | Browse |
| 2022/6/1 12:36 | 1005093 | 5 | Submit |
| 2022/6/1 13:21 | 1048655 | 1 | Login |
| 2022/6/1 14:46 | 1037824 | 6 | Logout |
| 2022/6/1 15:19 | 1049626 | 4 | AddtoCart |
| 2022/6/1 16:00 | 1009296 | 5 | Submit |
| 2022/6/1 16:39 | 1070713 | 2 | Browse |
| 2022/6/1 17:40 | 1090884 | 3 | Search |
Fields in table T:
| Field name | Data type | Description |
| Time | Datetime | Time stamp of an event, accurate to milliseconds |
| UserID | Integer | User ID |
| EventTypeID | Integer | Event type ID |
| EventType | String | Event type name |
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, and display the event type name in the result.
Both the code fields and value fields, which can be uniquely determined by corresponding code fields have been generated at the creation of the wide table so that less JOIN operations will be needed. In our case, each event type name is determined by one event type ID.
Techniques involved:
Redundant grouping key field: With a composite grouping key where a certain key field or certain key fields can determine values of another field or other fields, we can use them as the grouping field(s) and retrieve the first matching value in each controlled field, which will not engage in the grouping operation any more. This leads to better performance.
In our case, values of EventType are completely determined by EventTypeID field. So, we can group table only by EventTypeID and get the first corresponding value of EventType field.
Sample code
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(EventTypeID; EventType, count(1):Num, icount(UserID):iNum) |
In groups function, the grouping field EventTypeID comes before the semicolon. Since EventType is commanded by EventTypeID, it is written in the aggregation part without being preceded by an aggregate function – meaning retrieving the first member of the current group directly. Excluding such a field from comparisons of the grouping operation makes computation faster.
The grouping field must be one that determines the other or other fields and the controlled field(s) should be written after the semicolon. They need to be written at right positions.
Execution result:
| EventTypeID | EventType | Num | iNum |
| 1 | Login | 4033000 | 393400 |
| 2 | Browse | 3578901 | 348791 |
| 3 | Search | 2947931 | 257539 |
| 4 | AddtoCart | 1845674 | 175476 |
| 5 | Submit | 867345 | 83375 |
| 6 | Logout | 4033000 | 393400 |
