RaqForum 41 No.
94 View  • 
     ...

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

 

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

date

user

2024-04-08

bob

2024-04-11

bob

2024-04-12

bob

2024-04-17

bob

2024-04-18

bob

2024-04-25

bob

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.

date

user

session_id

2024-04-08

bob

1

2024-04-11

bob

1

2024-04-12

bob

1

2024-04-17

bob

2

2024-04-18

bob

2

2024-04-25

bob

3

SPL code:

　A

1

=BigQryJDBC.query("select * from tb order by date where user=?","Bob")

2

>d=A1.date,s=1

3

=A1.derive(s+=if(date-d>7,(d=date,1)):session_id)

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.

Problem source：https://stackoverflow.com/questions/78393653/sql-date-window-reset-after


 
 •  94 View  •