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

Heroku

Simplify your DevOps and maximize your time.

Since 2007, Heroku has been the go-to platform for developers as it monitors uptime, performance, and infrastructure concerns, allowing you to focus on writing code.

Learn More

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay