选择具有相同值的“连续”行

Question

我有一张表格，其中包含以下数据：

userID  tStamp                status
------  -------------------   ------
Jason   2017-10-18 03:20:00   idle  
Brown   2017-10-18 03:20:28   idle  
Brown   2017-10-18 03:25:28   idle  
Brown   2017-10-18 04:00:28   active    
Brown   2017-10-18 04:10:28   active    
Brown   2017-10-18 04:35:28   idle  
Brown   2017-10-18 04:45:28   idle  

我想提取具有相同状态的连续行。例如，我想看到用户在“空闲”状态下花费的时间，然后是“活动”状态，然后是“空闲”状态，依此类推。 如何在单个SQL查询中实现这一点？ 我的期望输出如下：

userID        staus          Duration_in_this_status (min)
------  -------------------   ------
Jason         idle             ---  
Brown         idle              5
Brown         active           10   
Brown         idle             10   

- user3719749

---

你能展示一个你想要的输出样例吗？你需要一个“从”和“到”的时间吗？ - BradC

@BradC：编辑了帖子以显示所需的输出。 - user3719749

在“ORDER BY…”中遍历表时，请使用@变量。 - Rick James

你正在使用的是哪个版本的MySQL或MariaDB？ - Evan Carroll

我正在使用的是MySQL Ver 14.14。 - user3719749

Answer 1

SELECT userID, status, TIMESTAMPDIFF(minute, MIN(tStamp), MAX(tStamp)) AS duration
FROM (
    SELECT
    t.*
    , @groupNumber := IF(@prev_userID != userID OR @prev_status != status, @groupNumber + 1, @groupNumber) AS gn
    , @prev_userID := userID
    , @prev_status := status
    FROM t
    , (SELECT @groupNumber := 0, @prev_userID := NULL, @prev_status := NULL) var_init_subquery
    ORDER BY userID, tStamp
) sq
GROUP BY gn, userID, status

• 在sqlfiddle中实时查看它的工作情况

这是它的工作原理。我们定义了三个变量。一个保存组号，另外两个保存前一行的status和userId的值。请注意，在关系数据库中，除非您指定顺序，否则没有顺序。这一点非常重要。在select子句中，我们首先检查变量值是否与当前行不同。如果是，则增加组号；如果不是，则保持不变。之后，我们赋值给当前行的值。因此，当下一行被处理时，变量仍然保存着前一行的值。所以顺序在这里也很重要。在外部查询中，我们只需按照这个组号进行分组，就可以得到时间戳的最小值和最大值。

• 在这里阅读有关用户定义变量的更多信息here

- tombom