时间戳

首页 > 解决方案 > 如何计算分组数据的时间差（MYSQL 5.6）

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

问题描述

我想获取组数据的时差，请看表：

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)

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

任何帮助都会很棒，在此先感谢

标签： mysql

解决方案

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

Returns:

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

Test it : Fiddle

推荐阅读