MySQL 查询来计算某些数字的连续出现次数

Question

另一个“连续出现”MySQL 问题，但我认为以前没有被问过。

我有一个看起来像这样的表格

+----+------+---------------------+------+-----------+--------------+
| id | unit |       datetime      | idle | idlecount | boutduration |
+----+------+---------------------+------+-----------+--------------+
| 1  |  A   | 2009-12-04 08:10:05 |  139 |           |              |
| 2  |  A   | 2009-12-04 08:20:05 |  107 |           |              |
| 3  |  A   | 2009-12-04 08:30:05 |  0   |           |              |
| 4  |  A   | 2009-12-04 08:40:05 |  144 |           |              |
| 5  |  A   | 2009-12-04 08:50:05 |  0   |           |              |
| 6  |  A   | 2009-12-04 09:00:05 |  0   |           |              |
| 7  |  A   | 2009-12-04 09:10:05 |  58  |           |              |
| 8  |  A   | 2009-12-04 09:20:05 |  0   |           |              |
| 9  |  A   | 2009-12-04 09:30:05 |  0   |           |              |
| 10 |  A   | 2009-12-04 09:40:05 |  0   |           |              |
| 11 |  A   | 2009-12-04 09:50:05 |  0   |           |              |
| 12 |  A   | 2009-12-04 10:00:05 |  107 |           |              |
| 13 |  A   | 2009-12-04 10:10:05 |  0   |           |              |
| 14 |  A   | 2009-12-04 10:20:05 |  144 |           |              |
| etc...

我需要计算idle列中连续出现零的次数，并确定每个序列的持续时间。单次出现是无关紧要的。所以结果应该是这样的

+----+------+---------------------+------+-----------+--------------+
| id | unit |       datetime      | idle | idlecount | boutduration |
+----+------+---------------------+------+-----------+--------------+
| 1  |  A   | 2009-12-04 08:10:05 |  139 |           |              |
| 2  |  A   | 2009-12-04 08:20:05 |  107 |           |              |
| 3  |  A   | 2009-12-04 08:30:05 |  0   |           |              |
| 4  |  A   | 2009-12-04 08:40:05 |  144 |           |              |
| 5  |  A   | 2009-12-04 08:50:05 |  0   |     2     |   00:20:00   |
| 6  |  A   | 2009-12-04 09:00:05 |  0   |           |              |
| 7  |  A   | 2009-12-04 09:10:05 |  58  |           |              |
| 8  |  A   | 2009-12-04 09:20:05 |  0   |     4     |   00:40:00   |
| 9  |  A   | 2009-12-04 09:30:05 |  0   |           |              |
| 10 |  A   | 2009-12-04 09:40:05 |  0   |           |              |
| 11 |  A   | 2009-12-04 09:50:05 |  0   |           |              |
| 12 |  A   | 2009-12-04 10:00:05 |  107 |           |              |
| 13 |  A   | 2009-12-04 10:10:05 |  0   |           |              |
| 14 |  A   | 2009-12-04 10:20:05 |  144 |           |              |
| etc...

我认为这需要使用 flow control statements但我以前从未使用过它，并且对 MySQL 文档感到有点害怕。尽管如此，我梦想有一个单一查询的解决方案。

干杯，汤姆

Answer 1

您可以使用相关子查询来完成此操作。第一个在每个之后获取下一个非零值。第二个计算中间的值。以下是获取空闲计数的示例:

select t.*,
       (select (case when count(*) > 1 then count(*) end)
        from t t3
        where t3.id >= t.id and
              (t3.id < t.nextNonZeroIdle or nextNonZeroIdle is NULL) and
              t3.idle = 0
       ) as IdleCOunt
from (select t.id, t.unit, t.datetime, t.idle,
             (select id
              from t t2
              where t2.unit = t.unit and
                    t2.id > t.id and
                    t2.idle > 0
              order by id
              limit 1
             ) as nextNonZeroIdle
      from t
     ) t
from t;