I have a table in this format:

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

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.

CodePudding user response：

Try the following solution, it should work in most RDBMS, being careful to correctly delimit column names that clash with reserved words.

First you need to pair up the connects/disconnects, this can be done by numbering the rows and then duplicating every-other number using a modulo, then using conditional aggregation:

with rn as (
  select *, 
    Row_Number() over(partition by user order by time) rn
  from t
), gp as (
  select *, 
    case when rn %2=0 then
      Lag(rn) over(partition by user order by rn) 
    else rn end gp
  from rn
)
select 
    [user], 
    Max(case when status = 'connect' then time end) Connect,
    Max(case when status = 'disconnect' then time end) Disconnect
from gp
group by user, gp

CodePudding user response：

The Conditional Aggregation can be used after applying ROW_NUMBER() window function if your available databases' versions are 8.0 , 10.2 and 2005 for MySQL, MariaDB and SQL Server respectively such as

WITH t2 AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY user,status ORDER BY time) AS rn,
       t.*
  FROM t     
)
SELECT User, 
       MAX(CASE WHEN status = 'connect' THEN time END) AS connect,
       MAX(CASE WHEN status = 'disconnect' THEN time END) AS disconnect
  FROM t2
 GROUP BY user, rn
 ORDER BY user, rn

where

• the dataset is grouped by user and status columns while sorted by time column

• user, which's a reserved keyword, must be replaced by [user] as a column name for SQL Server DB

Demo for MySQL/MariaDB

Demo for SQL Server