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.
解答
用自然思维去想这个问题，只需要将用户、时间有序的数据，按用户有序分组，再将偶数行的时间作为断开连接时间即可。这类运算用SQL写起来很麻烦，要借助窗口函数，利用"行号"，绕路解决问题。通常的办法是读出来用Python或SPL来做， SPL（一种 Java 的开源包）更容易被Java应用集成，代码也更简单一点，只要两句：
A |
|
1 |
=MYSQL.query("SELECT USER,TIME FROM t ORDER BY USER,TIME") |
2 |
=A1.new(USER,TIME:CONNECT,if(#%2==1,TIME[+1]):DISCONNECT).select(DISCONNECT) |
English version