I have a pre-processed table which I want to group every pair into one record containing data from fields of both records.
x
|-------------------|-----|----|
|Timestamp |Event|User|
|-------------------|-----|----|
|17/03/2020 03:22:00|Start|1 |
|17/03/2020 03:22:05|End |1 |
|17/03/2020 03:22:10|Start|2 |
|17/03/2020 03:22:15|End |2 |
|17/03/2020 03:23:00|Start|1 |
|17/03/2020 03:23:22|End |1 |
|-------------------|-----|----|
The query should return:
|-------------------|-------------------|----|
|StartTimestamp |EndTimestamp |User|
|-------------------|-------------------|----|
|17/03/2020 03:22:00|17/03/2020 03:22:05|1 |
|17/03/2020 03:22:10|17/03/2020 03:22:15|2 |
|17/03/2020 03:23:00|17/03/2020 03:23:22|1 |
|-------------------|-------------------|----|
You can safely assume that every 2 records is the correct pair (events are Start and End respectively, and User is the same) since the table is pre-filtered.
EDIT: Sorry, I forgot to mention that having multiple pairs for a single user is allowed. I’ve adjusted the example table above to show that.
Advertisement
Answer
As suggested, this should do what you want :
SELECT
MIN(Timestamp) AS StartTimestamp,
MAX(Timestamp) AS EndTimestamp,
User
FROM
mytable
GROUP BY User;
EDIT : As a user id can appear multiple times, in multiple groups, see the following query :
WITH cte AS (
SELECT mt.*, ROW_NUMBER() OVER(ORDER BY time) AS rn FROM mytable mt
)
SELECT
t1.userid,
t1.time AS StartTimestamp,
t2.time AS EndTimestamp
FROM cte t1
JOIN cte t2 ON t1.rn+1 = t2.rn
WHERE t1.event = 'Start'