Mysql橫向分組

我有一個表，它基本上包含以下行：

sessionId
date/time
event

事件可以是（為了簡單起見，“開始”和“結束”）

然而，相同的 sessionId 可以重複使用，所以，我有

sessionId     datetime                event
AAAAA         2020-01-01 11:30:00     start
AAAAA         2020-01-01 12:20:00     end
AAAAA         2020-01-01 14:22:00     start
AAAAA         2020-01-01 14:30:00     end
BBBBB         2020-01-02 14:22:00     start
BBBBB         2020-01-02 14:42:00     end
CCCCC ...etc ...

我需要將其轉換為：

SessionId     StartTime              duration
AAAAA         2020-01-01 11:30:00     00:50:00
AAAAA         2020-01-01 14:22:00     00:08:00
BBBBB         2020-01-02 14:22:00     00:20:00

有什麼辦法可以用 sql 查詢來做到這一點，還是我必須在程式碼中做到這一點？

提前致謝！

SELECT t1.sessionId, t1.dt StartTime, TIMEDIFF(t2.dt, t1.dt) duration
FROM src t1
JOIN src t2 ON t1.event = 'start'
          AND t2.event = 'end'
          AND t1.sessionId = t2.sessionId
          AND t1.dt < t2.dt
          AND NOT EXISTS ( SELECT NULL
                           FROM src t3
                           WHERE t3.sessionId = t1.sessionId
                             AND t1.dt < t3.dt
                             AND t3.dt < t2.dt );

小提琴

在 Oracle 中，也可以使用分析函式來完成。在 MySQL 的最後一個版本中，它們也存在。

create table T (
 sessionId varchar(20),
 datetime date,
 event varchar (10) );


Insert into T
Values ('aa', TO_DATE('21/04/2020 17:02:41', 'DD/MM/YYYY HH24:MI:SS'), 'start');
Insert into T
Values ('aa', TO_DATE('21/04/2020 17:03:01', 'DD/MM/YYYY HH24:MI:SS'), 'end');
Insert into T
Values ('bb', TO_DATE('21/04/2020 17:03:22', 'DD/MM/YYYY HH24:MI:SS'), 'start');
Insert into T
Values ('bb', TO_DATE('21/04/2020 17:03:37', 'DD/MM/YYYY HH24:MI:SS'), 'end');
Insert into T
Values ('aa', TO_DATE('21/04/2020 17:03:54', 'DD/MM/YYYY HH24:MI:SS'), 'start');
Insert into T
Values ('aa', TO_DATE('21/04/2020 17:04:27', 'DD/MM/YYYY HH24:MI:SS'), 'end');
Insert into T
Values ('bb', TO_DATE('21/04/2020 17:04:48', 'DD/MM/YYYY HH24:MI:SS'), 'start');
Insert into T
Values ('bb', TO_DATE('21/04/2020 17:05:04', 'DD/MM/YYYY HH24:MI:SS'), 'start');
COMMIT;

select * from T;  

SESSIONID      DATETIME            EVENT
-------------- ------------------- ----------
aa             21.04.2020 17:02:41 start
aa             21.04.2020 17:03:01 end
bb             21.04.2020 17:03:22 start
bb             21.04.2020 17:03:37 end
aa             21.04.2020 17:03:54 start
aa             21.04.2020 17:04:27 end
bb             21.04.2020 17:04:48 start
bb             21.04.2020 17:05:04 start

通常，並不是所有的會話都會有一個“結束”事件，其中一些可能會異常結束，或者一些還沒有結束。

所以在我的範例數據中， sessionid=“bb” 有兩行只有“開始”。

select sessionid, start_time,
      case when next_event = 'end' then end_time end AS end_time
 from (  
select sessionid, datetime start_time,
      lead(datetime) over (partition by sessionid order by datetime) end_time,
      lead(event) over (partition by sessionid order by datetime) next_event,  
      event
 from t   )
where event = 'start'  ; 

SESSIONID       START_TIME          END_TIME
--------------- ------------------- -------------------
aa              21.04.2020 17:02:41 21.04.2020 17:03:01
aa              21.04.2020 17:03:54 21.04.2020 17:04:27
bb              21.04.2020 17:03:22 21.04.2020 17:03:37
bb              21.04.2020 17:04:48
bb              21.04.2020 17:05:04

引用自：https://dba.stackexchange.com/questions/265458