The table in the Big Query database records multiple events of a user, such as a user sorted by time as follows:
Now we need to add a calculated column called 'session id': set the first event as a checkpoint, with session id set to 1; If the interval between the new event and the checkpoint is within 7 days, the session ID remains unchanged; If the interval between the new event and the checkpoint is greater than 7 days, the session ID will be incremented and the checkpoint will be reset to that event.
SPL code:
A1: Query the event records of a user through JDBC.
A2: Set variables, where d is the checkpoint date and the initial value is the date of the first event; s is a variable of session-id with an initial value of 1.
A3: Add the calculated column 'session id' according to the rules. When the difference between the current record date and the checkpoint date is greater than 7 days, reset the checkpoint date to the current record date and add s by 1.
Top comments (0)