mysql - 计算具有特定状态的连续行

Question

我需要统计用户在最近一小时内是否有3次连续登录失败。

例如

id    userid    status  logindate
1     1         0       2014-08-28 10:00:00
2     1         1       2014-08-28 10:10:35
3     1         0       2014-08-28 10:30:00 
4     1         0       2014-08-28 10:40:00

在上面的示例中，状态 0 表示尝试失败，1 表示尝试成功。

我需要一个查询来计算过去一小时内状态为 0 的用户的三个连续记录。

我试过下面的查询

SELECT COUNT( * ) AS total, Temp.status
FROM (
SELECT a.status, MAX( a.id ) AS idlimit
FROM loginAttempts a
GROUP BY a.status
ORDER BY MAX( a.id ) DESC
) AS Temp
JOIN loginAttempts t ON Temp.idlimit < t.id
HAVING total >1

结果:

total       status
2             1

我不知道为什么它显示状态为1。我还需要在登录日期和状态字段上添加一个 where 条件，但不知道它是如何工作的

Answer 1

对于连续计数，您可以使用用户定义的变量来记录系列值，就像在下面的查询中我使用了 @g和 @r变量，在内部查询中，我正在存储可能为 1/0 的当前状态值，如果是表达式，我正在比较存储在 @g 中的值。如果它们都相等，则使用状态列，如 @g持有前一行值并且前一行的状态等于当前行的状态然后不要更改存储在 @r 中的值，如果这些值不匹配，如 @g <> a.status然后增加 @r对于 1，需要注意一件事我正在使用 order by with id 列并假设它设置为 auto_increment 所以对于连续的 1s @r值将与 @r 相同第一个状态为 3，再次状态为 1，所以 @r将 3 直到状态更改为 0 与状态 0 相同，反之亦然

---

SELECT t.userid,t.consecutive,t.status,COUNT(1) consecutive_count
FROM (
SELECT a.* ,
@r:= CASE WHEN @g = a.status THEN @r ELSE @r + 1 END consecutive,
@g:= a.status g
FROM attempts a
CROSS JOIN (SELECT @g:=2, @r:=0) t1
WHERE a.`logindate` BETWEEN '2014-08-28 10:00:00' AND '2014-08-28 11:00:00'
ORDER BY id
) t
GROUP BY t.userid,t.consecutive,t.status
HAVING consecutive_count >= 3 AND  t.status = 0

---

现在在父查询中，我按 userid 对结果进行分组我有名字的 case 表达式的结果值是 consecutive和 status获取每个用户连续状态的计数

---

One thing to note for above query that its necessary to provide the hour range like i have used between without this it will be more difficult to find exactly 3 consecutive statuses with in an hour

示例数据

INSERT INTO attempts
    (`id`, `userid`, `status`, `logindate`)
VALUES
    (1, 1, 0, '2014-08-28 10:00:00'),
    (2, 1, 1, '2014-08-28 10:10:35'),
    (3, 1, 0, '2014-08-28 10:30:00'),
    (4, 1, 0, '2014-08-28 10:40:00'),
    (5, 1, 0, '2014-08-28 10:50:00'),
    (6, 2, 0, '2014-08-28 10:00:00'),
    (7, 2, 0, '2014-08-28 10:10:35'),
    (8, 2, 0, '2014-08-28 10:30:00'),
    (9, 2, 1, '2014-08-28 10:40:00'),
    (10, 2, 1, '2014-08-28 10:50:00')
;

正如您从 id 3 到 5 看到的那样，您可以看到 userid 1 的连续 0，类似地 id 6 到 8 userid 2 有连续的 0，并且它们在一个小时范围内使用上面的查询，您可以得到如下结果

userid  consecutive  status  consecutive_count  
------  -----------  ------  -------------------
     1            2       0                    3
     2            2       0                    3

Fiddle Demo