如何在MySQL表中汇总连续的行

Question

我有一个跟踪建筑内多个用户移动的表格。列显示用户的ID、他们所在的房间以及发现他们在该房间的时间戳。表格如下：

user_id  location  time                
-------  --------  -------------------   
1        room1     2018-05-18 03:20:00     
1        room1     2018-05-18 03:21:15
1        room2     2018-05-18 03:22:07
2        room1     2018-05-18 03:24:20     
2        room1     2018-05-18 03:27:55
2        room1     2018-05-18 03:29:09      
1        room2     2018-05-18 03:32:37    
1        room1     2018-05-18 03:34:41
1        room1     2018-05-18 03:39:28

我想要做的是总结每个用户在每个房间停留的时间信息，如下所示：

user_id  location  duration(s)
-------  --------  -----------
1        room1     75
2        room1     289
1        room2     630
1        room1     287

有没有一种方法可以使用单个查询来完成这个操作？

- Firman R

---

这看起来像是一个间隔和岛屿问题，在MySQL中很难处理。但也许有人能够给你一个查询。 - Tim Biegeleisen

我认为样例输入数据的最后一条记录有误。time 不应该是 2018-05-18 03:39:28 吗？ - Giorgos Betsos

Answer 1

您可以使用变量或相关子查询来处理此问题。通常情况下，变量更加高效：

select user_id, location, min(time), max(time),
       timestampdiff(second, min(time), max(time)) as duration
from (select t.*,
             (@grp := if(@ul = concat_ws(':', user_id, location), @grp,
                         if(@ul := concat_ws(':', user_id, location), @grp + 1, @grp + 1)
                        )
             ) as grp
      from (select t.*
            from t
            order by user_id, time
           ) t cross join
           (select @ul := '', @grp := 0) params
     ) t
group by user_id, location, grp;

这里 有一个包含可工作代码的 SQL Fiddle。

- Gordon Linoff

---

你刚刚写的吗？ - Simon

我认为 concat(':', user_id, location) 应该改为 concat_ws(':', user_id, location)，不过这并不重要，因为concat(':', user_id, location) 在这个查询中也能正常工作。 - Raymond Nijland

这看起来很有趣，我想看看它是如何工作的。我已经创建了上面的示例数据集。然而，查询生成了75和289的访问量，与所需的输出一致，但包括一个从2017-10-18 03:39:28到2018-05-18 03:34:41的18316513，忽略了更明显的结束时间2018-05-18 03:20:00。它还在2018-05-18 03:22:07和2018-05-18 03:32:37为用户1和room2创建了零长度的访问。它也省略了所需输出中的最后两个。别问我为什么！ - Nick

@Nick，错误似乎在示例数据中而非查询中。我测试了Gordon的查询并且它产生的结果和我的查询完全一致。 - Giorgos Betsos

@Gordon Linoff 在使用相同数据集测试我的数据库后，我发现它未能正确地重现用户1在room2中的持续时间输出，而是产生了两个零长度访问，正如Nick之前所述。经过一些检查，似乎只有在被查询的行是连续的时，该查询才能正确地输出结果。在删除了room2输入的2行之间的3行后，查询输出了所需的630结果。是否有办法使其在行不连续时正确输出630？非常感谢！ - Firman R

Answer 2

如果您正在使用MySQL 8.0，您可以轻松地使用窗口函数来解决此问题：

;WITH GroupedTable AS (
   SELECT user_id, location, time,
          ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time) - 
          ROW_NUMBER() OVER (PARTITION BY user_id, location ORDER BY time) AS grp
   FROM mytable
)
SELECT user_id, location, TIMESTAMPDIFF(SECOND, MIN(time), MAX(time)) AS duration
FROM GroupedTable  
GROUP BY user_id, location, grp

- Giorgos Betsos