Hy, i want to split the time to 2 columns by Event type.

My Table looks like this:

And i want to split the Time with EventType like this in SQLite:

any ideas?

CodePudding user response：

assuming there is no double logons without logoff, as in your example, you can do this:

select user, 
       logon_time, 
       logoff_time 
  from (select user, 
               time logon_time,
               lead(time) over(partition by user order by time) logoff_time,
               event_type
          from test_table)
 where event_type = 'Logon';

The LEAD function takes value from next line using ordering from "order by" part. In your case a logoff event takes place only after a logon, so it might working

db_fiddle

CodePudding user response：

WITH
  session AS
(
  SELECT
    SUM(
      CASE WHEN eventtype = 'logon' THEN 1 ELSE 0 END
    )
    OVER (
      PARTITION BY username
          ORDER BY eventtime
    )
      AS user_session_id,
    *
  FROM
    yourData
  WHERE
    eventtype IN ('logon', 'logoff')
)
SELECT
  username,
  user_session_id,
  MIN(CASE WHEN eventtype = 'logon'  THEN eventtime END),
  MIN(CASE WHEN eventtype = 'logoff' THEN eventtime END)
FROM
  session
GROUP BY
  username,
  user_session_id