DEV Community

Judy
Judy

Posted on

3 1 1 1 1

SQL,Classify time-close events into a group and assign them a number #eg84

The table in the Big Query database records multiple events of a user, such as a user sorted by time as follows:

Image description
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.

Image description
SPL code:

Image description

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.

Open source SPL source address

Free Download

Retry later

Top comments (0)

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

Retry later