User Behavior Analysis in Practice 2: Redundant Grouping Key Field

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:

2022/6/1 10:2010727553Search
2022/6/1 12:1210780302Browse
2022/6/1 12:3610050935Submit
2022/6/1 13:2110486551Login
2022/6/1 14:4610378246Logout
2022/6/1 15:1910496264AddtoCart
2022/6/1 16:0010092965Submit
2022/6/1 16:3910707132Browse
2022/6/1 17:4010908843Search

Fields in table T:

Field nameData typeDescription
TimeDatetimeTime stamp of an event, accurate to milliseconds
UserIDIntegerUser ID
EventTypeIDIntegerEvent type ID
EventTypeStringEvent 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:

2>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd")<=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:


Leave a Reply