mysql - 选择特定的 MySQL 行出现次数来计算平均值

Question

我们有一个 MySQL 表，其中包含跟踪用户 session 开始和结束时间的信息:

UserId      Start                   End
150392  2012-07-11 01:11:28 2012-07-11 01:17:36
150392  2012-07-11 01:19:03 2012-07-11 01:23:47
150393  2012-07-11 01:22:47 2012-07-11 01:38:15
150051  2012-07-11 01:31:03 2012-07-11 01:36:07
150393  2012-07-11 01:37:43 2012-07-11 01:56:41
150392  2012-07-11 01:48:57 2012-07-11 01:54:23

我希望能够根据用户进入我们系统的次数来检索平均 session 时间。为了澄清，我想知道用户第一次进入我们系统的平均 session 时间，第二次进入我们系统的平均 session 时间等等。所以第一次出现在表中的用户ID，这是一个用户第一届 session ；我们将所有首次发生的 session 时间相加，然后除以计数。然后对第二次出现的 userid 执行相同操作并获取平均时间等。

以上表为例，第三次 session 的平均时间为 326 秒，因为只有一个用户有第三次 session (用户 ID 150392，最后一行)。

所以我正在寻找类似这样的输出:

SessionCount    AvgTime (seconds)
1               540
2               810
3               326

我希望这是有道理的。任何建议将非常感激!谢谢。

Answer 1

SELECT SessionCount, AVG(duration) AS AvgTime FROM (
  SELECT   COUNT(*) AS SessionCount,
           TIMESTAMPDIFF(SECOND, t2.Start, t2.End) AS duration
  FROM     my_table t1
      JOIN my_table t2
        ON t1.UserId = t2.UserId AND t1.Start <= t2.Start
  GROUP BY t2.UserId, t2.Start, t2.End
) t GROUP BY SessionCount

查看 sqlfiddle .