mysql - 查找过去有相关记录的记录

Question

基本上，我尝试的结果是:“获取过去特定时间内有 0 个不成功 记录的成功 记录的数量”。 “成功”和“不成功”只是指列的值。

虽然有点复杂，但这里是我正在处理的表的描述:

`log`
  id                int PRIMARY KEY AUTO_INCREMENT
  fingerprint_id    int (foreign key)
  status            boolean
  date              timestamp

我们的小系统的工作流程是，当用户刷指纹时，会向该表添加一条记录，并根据是否匹配设置 status(同样，还有更多内容) ，我只是想简化)。我们根据执行此操作的用户获得 fingerprint_id，因此这是将记录与个人相关联的标识符。

现在，我们要求他们最多尝试 3 次。因此，它们可以在 3 的第 1 天、3 的第 2 天、3 的第 3 天匹配，或者根本不匹配。这意味着他们的“组”中可以有 1、2 或 3 条记录。虽然这不是真的，但我们可以假设用户会继续尝试，直到他们匹配或达到 3 次失败尝试(我们发现有时人们可能在失败一两次后不会继续)。

这是一些数据的例子:

id  fp_id status   date
----------------------------------------
20    2     0      '2013-01-21 12:30:01'
21    2     0      '2013-01-21 12:30:05'
22    2     0      '2013-01-21 12:30:10'
23    9     1      '2013-01-21 12:31:30'
24    1     0      '2013-01-21 12:35:00'
25    1     1      '2013-01-21 12:35:05'

数据中，用户 (fingerprint_id) 2 尝试了 3 次，从未匹配到。用户 9 在第一次尝试时就匹配了。用户 1 尝试一次失败，然后再次尝试并匹配。

重点是找出35秒前有多少条成功的(status=1)条记录有0条不成功的(status=0)条记录。当然，“连接”它们的唯一方法是通过 fingerprint_id。

同样，我们假设了很多事情，但这很好。

这是我的尝试:

SELECT  COUNT(*)
FROM    log AS log_main
WHERE   log_main.status=1 AND
        (SELECT COUNT(*)
         FROM   log AS log_inner
         WHERE  log_inner.fingerprint_id=log_main.fingerprint_id AND
                log_inner.status=0 AND
                log_inner.date<log_main.date AND log_inner.date>=(log_main.date - INTERVAL 35 SECOND))=0

^ 我希望这个选择所有成功记录，这些记录在 35 秒前(对于该用户)发生的不成功记录计数为 0。但我不知道，因为查询需要 600 多秒。我刚刚发现如何延长 MySQL Workbench 的最大超时时间，但无论哪种方式，都需要很长时间。该表总共有大约 120,000 条记录，所以我不确定这是否足以让这个查询变慢。

无论如何，这是另一种尝试:

SELECT  COUNT(*)
FROM    (SELECT log.fingerprint_id, log.date
         FROM log
         WHERE log.status=1) successful,
        (SELECT log.fingerprint_id, log.date
         FROM log
         WHERE log.status=0) unsuccessful
WHERE   successful.fingerprint_id=unsuccessful.fingerprint_id AND
        unsuccessful.date<successful.date AND unsuccessful.date>=(successful.date - INTERVAL 35 SECOND)

^ 我觉得这个更接近，但是当然，没有比较过去匹配多少条记录的“计数”。那是我对如何解决感到困惑的部分。我觉得它与 GROUP BY 或使用 IN 有关，但我所做的似乎不起作用(从某种意义上说超过 600 秒或类似时间)。这是我尝试使用 GROUP BY

的示例

SELECT  successful.id, COUNT(*) cnt
FROM    (SELECT log.fingerprint_id, log.date, log.id
         FROM log
         WHERE log.status=1) successful,
        (SELECT log.fingerprint_id, log.date, log.id
         FROM log
         WHERE log.status=0) unsuccessful
WHERE   successful.fingerprint_id=unsuccessful.fingerprint_id AND
        unsuccessful.date<successful.date AND unsuccessful.date>=(successful.date - INTERVAL 35 SECOND)
GROUP BY successful.id

^ 但是结果只包含计数不为 0 的行。我猜这是因为 WHERE 子句。但我只需要 0 个计数。

我尝试了很多组合，我觉得我的脑子快炸了。

Answer 1

尝试使用 NOT EXISTS 而不是 COUNT = 0。这应该表现得更好。

SELECT  COUNT(*)
FROM    log AS log_main
WHERE   log_main.status=1 
AND     NOT EXISTS
        (   SELECT 1
            FROM   log AS log_inner
            WHERE   log_inner.fingerprint_id=log_main.fingerprint_id
            AND     log_inner.status = 0
            AND     log_inner.date < log_main.date 
            AND     log_inner.date >= (log_main.date - INTERVAL 35 SECOND)
        );

您还应确保表已正确编制索引。

编辑

我相信在 MySQL 中使用 LEFT JOIN/IS NULL 比使用 NOT EXISTS 更有效，因此这会比上面的执行得更好(尽管可能不显着):

SELECT  COUNT(*)
FROM    log AS log_main
        LEFT JOIN log AS log_inner
            ON log_inner.fingerprint_id=log_main.fingerprint_id
            AND log_inner.status = 0
            AND log_inner.date < log_main.date 
            AND log_inner.date >= (log_main.date - INTERVAL 35 SECOND)
WHERE   log_main.status = 1 
AND     Log_inner.fingerprint_id IS NULL;

编辑 2

要获得 1 或 2 次尝试等的记录，我仍然会使用 JOIN，但像这样:

SELECT  COUNT(*)
FROM    (   SELECT  log_Main.id
            FROM    log AS log_main
                    INNER JOIN log AS log_inner
                        ON log_inner.fingerprint_id=log_main.fingerprint_id
                        AND log_inner.status = 0
                        AND log_inner.date < log_main.date 
                        AND log_inner.date >= (log_main.date - INTERVAL 35 SECOND)
            WHERE   log_main.status = 1 
            AND     Log_inner.fingerprint_id IS NULL
            GROUP BY log_Main.id
            HAVING COUNT(log_Inner.id) = 1
        ) d