Mysql中的重复值和分组值 [英] Repeated and grouped values in Mysql

问题描述

仅当该值恒定3次或多次（在下一行中不断重复）时，如何计算该值在表中重复多少次

How to calculate how many times the value 0 is repeated in a table only if that value is constant by 3 or more times (constant being repeated in next row)

在这种情况下，将2次重复0值超过3次。

in this case it would be 2 occasions that the value 0 is repeated more than 3 times.

+-----------+-------+---------------------+
| idDataGps | speed | eventDate           |
+-----------+-------+---------------------+
|    143483 |    56 | 2017-05-18 08:42:05 |
|    143484 |     0 | 2017-05-18 08:42:11 |
|    143485 |     0 | 2017-05-18 08:42:20 |
|    143486 |     0 | 2017-05-18 08:42:35 |
|    143487 |    43 | 2017-05-18 08:42:40 |
|    143488 |    44 | 2017-05-18 08:42:50 |
|    143489 |    48 | 2017-05-18 08:43:05 |
|    143490 |    24 | 2017-05-18 08:43:14 |
|    143491 |    34 | 2017-05-18 08:43:16 |
|    143492 |     9 | 2017-05-18 08:43:20 |
|    143493 |    14 | 2017-05-18 08:43:36 |
|    143494 |     0 | 2017-05-18 08:44:06 |
|    143495 |     0 | 2017-05-18 08:44:21 |
|    143496 |     0 | 2017-05-18 08:46:06 |
|    143497 |     0 | 2017-05-18 08:48:36 |
|    143498 |     0 | 2017-05-18 08:48:42 |
+-----------+-------+---------------------+

推荐答案

使用用户定义的变量将状态从一行移到另一行。使用一个变量 @zerocount 来计算连续的零速度；每当 speed！= 0 时，它就会重置为0，并且每当 speed = 0 时，它就会递增。每当遇到非零值时，另一个变量 @counter 就会递增，因此所有连续的零都将在相同的 @counter 组。

Use user-defined variables to keep state from one row to the next. Use one variable @zerocount to count consecutive zero speeds; it gets reset back to 0 whenever speed != 0, and increments whenever speed = 0. Another variable @counter gets incremented whenever a non-zero value is encountered, so all the consecutive zeroes will be in the same @counter group.

然后使用 MAX（zerocount）按计数器分组。

SELECT COUNT(*) FROM (
    SELECT counter, MAX(zerocount) AS maxcount
    FROM (
        SELECT speed, IF(speed = 0, @zerocount := @zerocount+1, @zerocount := 0) as zerocount,
            IF(speed != 0, @counter := @counter + 1, @counter) AS counter
        FROM (SELECT speed FROM yourTable ORDER BY eventDate) AS t
        CROSS JOIN (SELECT @counter := 0, @zerocount := 0) AS var
    ) AS x
    GROUP BY counter
) AS y
WHERE maxcount >= 3

演示

这篇关于Mysql中的重复值和分组值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！