mysql - 连续行之间每人之间的时间差

Question

我有一些数据(广义上来说)包含以下字段:

Person  TaskID   Start_time                      End_time
Alpha   1       'Wed, 18 Oct 2017 10:10:03 GMT' 'Wed. 18 Oct 2017 10:10:36 GMT'
Alpha   2       'Wed, 18 Oct 2017 10:11:16 GMT' 'Wed, 18 Oct 2017 10:11:28 GMT'
Beta    1       'Wed, 18 Oct 2017 10:12:03 GMT' 'Wed, 18 Oct 2017 10:12:49 GMT'
Alpha   3       'Wed, 18 Oct 2017 10:12:03 GMT' 'Wed, 18 Oct 2017 10:13:13 GMT'
Gamma   1       'Fri, 27 Oct 2017 22:57:12 GMT' 'Sat, 28 Oct 2017 02:00:54 GMT'
Beta    2       'Wed, 18 Oct 2017 10:13:40 GMT' 'Wed, 18 Oct 2017 10:14:03 GMT'

对于这些数据，我所需的输出类似于:

Person  TaskID Time_between_attempts
Alpha   1      NULL      ['Wed, 18 Oct 2017 10:10:03 GMT' - NULL]
Alpha   2      0:00:40   ['Wed, 18 Oct 2017 10:11:16 GMT' -'Wed, 18 Oct 2017 10:10:36 GMT']
Beta    1      NULL      ['Wed, 18 Oct 2017 10:12:03 GMT' - NULL]
Alpha   3      0:00:35   ['Wed, 18 Oct 2017 10:12:03 GMT' -'Wed, 18 Oct 2017 10:11:28 GMT']
Gamma   1      NULL      ['Fri, 27 Oct 2017 22:57:12 GMT' - NULL]
Beta    2      0:00:51   ['Wed, 18 Oct 2017 10:13:40 GMT' -'Wed, 18 Oct 2017 10:12:49 GMT']

我的要求如下:

a.对于给定的人(Alpha、Beta 或 Gamma)，变量“time_ Between_attempts”的第一次出现将为零/NULL - 在示例中我将其显示为 NULL。

b.第二次(及后续)同一个人出现时将具有非 NULL 或非零“time_ Between_attempts”。该变量是通过前一个任务的结束时间和下一个任务的开始时间之间的差来计算的。

我在这方面有以下问题:

1. 如何编写一个 SQL 脚本来帮助我实现所需的输出？

请注意，TaskID 写为整数只是为了简化。原始数据中，TaskID比较复杂，由不连续的字符串组成，如下:

'q:1392763916495:441',
'q:1392763916495:436'

对此的任何建议将不胜感激。

Answer 1

使用 self Join() 方法。

    SELECT a.person, 
            a.taskid, 
            TIMEDIFF (DATE_FORMAT(STR_TO_DATE(a.Start_time, '%a, %d %b %Y %H:%i:%s'), '%Y-%m-%d %H:%i:%s') ,DATE_FORMAT(STR_TO_DATE(b.End_time, '%a, %d %b %Y %H:%i:%s'), '%Y-%m-%d %H:%i:%s') ) as Time_between_attempts,
            a.Start_time,
            b.End_time

        FROM   test a 
            LEFT JOIN test b 
                    ON a.person = b.person 
                        AND a.taskid = b.taskid + 1 
        ORDER  BY 1, 2; 

但这将忽略时区。