mysql - 显示记录组之间的平均时间

Question

我这里有一个大难题:)

我有一个包含 2 个表的数据库:survey_reply 和 questions，像这样:

table SURVEY_REPLYid | question_id | data_time            | user_id | user reply and others col...--------------------------------------------------------522|   2         |  2016-02-29 10:07:10 | jacky.  |   3         |  2016-02-29 10:07:22 | jacky.  |   1         |  2016-02-29 10:07:59 | jacky.. |   4         |  2016-02-29 10:08:40 | jacky...|   2         |  2016-02-29 11:21:10 | paul.  |   3         |  2016-02-29 11:21:32 | paul.  |   2         |  2016-02-29 11:21:35 | louise.  |   1         |  2016-02-29 11:21:50 | paul.. |   4         |  2016-02-29 11:22:30 | paul.. |   3         |  2016-02-29 11:23:01 | louise

The question are shown to the users following the order in this table:

table QUESTIONS    id | q_ord | survey_id | other columns....    -------------------------------------------    1  |   3   |   786    2  |   1   |   786    3  |   2   |   786    4  |   4   |   786

i would know the average reply time, the time spent by people for make his choice and reply to question.

calculation in this example

4th-3th. (last one minus the previus one)

for reply to question.id=4 (question.q_ord=4)

    jacky spent 41 sec (10:08:40-10:07:59)
    paul        80 sec (11:22:30-11:21:50)
    louise doesnt reply

第三至第二。 (第三个减去第二个)

对于 Question.id=1 (question.q_ord=3)

    jacky spent 37 sec (10:07:59-10:07:22)
    paul        18 sec (11:21:50-11:21:32)
    louise doesnt reply

第二至第一。对于 Question.id=3 (question.q_ord=2)

    jacky spent 12 sec (10:07:22-10:07:10)
    paul        22 sec (11:21:32-11:21:10)
    louise      86 sec (11:23:01-11:21:35)

我不需要计算开始question.id=2 (question.q_ord=1)的时间

结果应该是:

    q_id | q_ord | av_reply_time    -------------------------------------------      3  |    2  |    (12+22+86)/3      1  |    3  |    (37+18)/2      4  |    4  |    (41+80)/2

怎么算出来的？

PS q_ord 是一个连续的整数，没有跳过任何数字。 总是从 1 开始。我总是知道最大数量(调查中的总问题)，在这种情况下只是 4。

Answer 1

我假设您的结果集略有偏差，并且我(还)看不到第二个表的重要性...

SELECT question_id
     , AVG(diff) avg_diff 
  FROM 
     ( SELECT x.user_id
            , x.question_id
            , TIME_TO_SEC(TIMEDIFF(MAX(y.data_time),x.data_time)) diff 
         FROM survey_reply x 
         JOIN survey_reply y 
           ON y.user_id = x.user_id 
          AND y.data_time < x.data_time 
        GROUP 
           BY x.user_id
            , x.question_id
     ) a
 GROUP 
    BY question_id;