mysql - 如何计算分组数据的时间差(MYSQL 5.6)

Question

我想得到组数据的时差，请看表:

id | session_id | event | created

1  | 101  | START | 2010-01-10 09:30:10
2  | 102  | START | 2010-01-10 09:31:10
3  | 101  | END   | 2010-01-10 09:32:10
4  | 103  | START | 2010-01-10 09:35:10
5  | 102  | END   | 2010-01-10 09:38:10
6  | 103  | END   | 2010-01-10 09:39:10
7  | 101  | START | 2010-01-10 09:39:10
8  | 102  | START | 2010-01-10 09:42:10
9  | 103  | START | 2010-01-10 09:39:10
10 | 102  | END   | 2010-01-10 09:45:10

输出应该如下:

session_id | time_count

 101  | 2 (minutes)
 102  | 7 (minutes)
 103  | 4 (minutes)
 102  | 3 (minutes)

所以在这里我想计算每个成功结束的 session 的时间差，而不是那些没有结束的 session 。

任何帮助都会很棒，提前致谢

Answer 1

SELECT session_id,TIMESTAMPDIFF(MINUTE,started, ended) as time_count
FROM(

    SELECT session_id, Tstart.created AS started, 
            (
                SELECT created
                FROM mytable Tend
                WHERE event = 'END' AND Tstart.created < Tend.created AND Tend.session_id=Tstart.session_id
                GROUP BY session_id
            ) AS ended
    FROM mytable Tstart
    WHERE event = 'START'
) t
WHERE ended IS NOT NULL
ORDER BY session_id

返回:

| session_id | time_count |
|------------|------------|
|        101 |          2 |
|        102 |          7 |
|        102 |          3 |
|        103 |          4 |

测试它:Fiddle