MySQL to get records that repeat the value in order

Question

I have a large table with 300,000 entries. This table has an integer value called "speed", and the value of `s is from 0 to 100. In the first entries, the value is 0, and I want to delete. I want to remove records from the query where the velocity field is repeated more than 10 times. For example:

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 2 3 5 10 12 13 15 20 30 20 15 10 8 5 2 1 0 0 0 0 4 5 10 20 ...

[------- delete this -----------] ............................ ........................................... [--- --- ---] <- do not delete this

thanks

Answer 1

- . , , . , . , , .

, SQL, , :

set @groupnum=0;

select 
  GroupNum,
  count(*) as RecsInGroup 
from
(
    select 
      t1.id as Id,
      t1.velocity as velocity1,
      t2.velocity as velocity2,  
      if(t1.velocity<>t2.velocity,@groupnum:=@groupnum+1,@groupnum) as GroupNum
    from 
      VelocityTable as t1
    join
      VelocityTable as t2  
    on
      t1.id=t2.id-1
) as groups
group by
  GroupNum  
having RecsInGroup>10

?

1

, .

, , :

velocity : 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 2 3 5 10 12 13 15 20 30 20 15 10  8  5  2  1  0  0  0  0  4  5 10 20
Groupnum : 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 2 3 4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 18 18 18 19 20 21 22

, , . , , GroupNum . .

2

, GroupNum. , , :

GroupNum,RecsInGroup
0,15 // !!
1,1
2,1
3,1
4,1
5,1
6,1
7,1
8,1
9,1
10,1
11,1
12,1
13,1
14,1
15,1
16,1
17,1
18,4 // !!
19,1
20,1
21,1

having RecsInGroup>10, :

GroupNum,RecsInGroup
0,15

, GroupNum, .

3

:

• GroupNum.
• GroupNum, .

.

Answer 2

, , , . , = = 10 , .

BEGIN TRAN COMMIT TRAN DELETE, .

Answer 3

Thank you very much. I'm all there, but I tried it with mySQL View as the source of the table and it does not work (unkown table xxx). I can’t use the whole table because it has more than 19 million records, I just need a record from a certain day, car plate and city.