"Question Source: [url] I have a table in this format: User Time status User 1 2021-12-24 14:00:0 .."

blackduckie RaqForum 28 No.
1 Reply • 359 View • 2 Years ago

Transforming Corresponding Start-Stop Time in Rows to Columns

sql(179) row number(2) order-based grouping(8)

Question

Source: https://stackoverflow.com/questions/70531824/transforming-corresponding-start-stop-time-in-rows-to-columns

I have a table in this format:

User	Time	status
User 1	2021-12-24 14:00:00	connect
User 2	2021-12-24 14:05:23	connect
User 2	2021-12-24 14:10:11	disconnect
User 2	2021-12-24 14:10:17	connect
User 1	2021-12-24 19:35:22	disconnect
User 2	2021-12-25 01:10:40	disconnect

I'd like to have a table with connect & disconnect as columns, one row for each session from connect to next disconnect per user.

User	connect	disconnect
User 1	2021-12-24 14:00:00	2021-12-24 19:35:22
User 2	2021-12-24 14:05:23	2021-12-24 14:10:11
User 2	2021-12-24 14:10:17	2021-12-25 01:10:40

I could create this on MySQL, MariaDB or MSSQL, depending on where it's easier to do. Is it possible to do as a view? Great addon, but not absolutely necessary: column"duration" that shows the duration of each session from connect to disconnect.

If easier, connect/disconnect times could be (mili)seconds from 1970/1/1.

Answer

According to the general way of thinking, you just need to sort records arranged by user and time according to user, and, in each group, get the disconnect time from each even numbered row. SQL uses a roundabout method to do this. The method uses window function and specifically creates “row numbers” to achieve the goal. A convenient alternative is to import data out of database and handle it in Python or SPL. SPL, the open-source Java package, is easier to be integrated into a Java program and generate more concise code. It can accomplish the task with only two lines of code: