mysql - 计算mysql中的连续数字，分组

Question

我在 MySQL 数据库中有以下数据:

id  value
--  -----   
1   7.07
2   1.00
3   4.81
4   1.39
5   1.60
6   9.11
7   6.99
8   1.95
9   1.00
10  1.00
11  1.62
12  1.38
13  1.33
14  1.41
15  3.67
16  3.62
17  2.43
18  3.15
19  57.30
20  1.33
21  1.68
22  6.52
23  2.75
24  2.36
25  2.01
26  2.22
27  5.35
28  2.30
29  1.05
30  24.21

我正在尝试对结果进行排序，计算同一范围内有多少个连续值，就像这样:

value   consecutive repetitions
-----   -----------------------
7.      1
1.      1
4.      1
1.      2
9.      1
6.      1
1.      7
3.      2
2.      1
3.      1
57.     1
1.      2
6.      1
2.      4
5.      1
2.      1
1.      1
24.     1

因此，如您所见，我主要是想删除 float ，根据它的整数值对数字进行分组，然后计算它们有多少次连续重复。

我已经试过了，但它似乎不适用于 MySQL:

SELECT value, COUNT(*) 
FROM (select live_records.*,
             (row_number() over (order by id) -
              row_number() over (partition by value order by id)
             ) as grp
      from live_records
     ) live_records
group by grp, value;

有什么想法吗？谢谢!

Answer 1

表结构:

create table test (a int, b  float);

插入值:

insert into test values(1,7.07);
insert into test values(2,1.00);
insert into test values(3,4.81);
insert into test values(4,1.39);
insert into test values(5,1.60);
insert into test values(6,9.11);
insert into test values(7,6.99);
insert into test values(8,1.95);
insert into test values(9,1.00);
insert into test values(10,1.00);
insert into test values(11,1.62);
insert into test values(12,1.38);
insert into test values(13,1.33);
insert into test values(14,1.41);
insert into test values(15,3.67);
insert into test values(16,3.62);
insert into test values(17,2.43);
insert into test values(18,3.15);
insert into test values(19,57.30);
insert into test values(20,1.33);
insert into test values(21,1.68);
insert into test values(22,6.52);
insert into test values(23,2.75);
insert into test values(24,2.36);
insert into test values(25,2.01);
insert into test values(26,2.22);
insert into test values(27,5.35);
insert into test values(28,2.30);
insert into test values(29,1.05);
insert into test values(30,24.21);

SQL 查询:

set @groupvalue := 0;
set @comparevalue := null;
select groupingvalue, max(value), count(value)
from 
(
select if(@comparevalue=floor(b),@groupvalue,@groupvalue := @groupvalue + 1 )
groupingvalue,
 @comparevalue := floor(b) as value
from test t
) a 
group by groupingvalue

解释:

• 如果连续的比较值相同则创建相同的分组值明智地增加分组值。
• 现在创建了不同的分组，因此我们可以轻松计算出现次数。