mysql - 如果表中的值很接近，如何对它们进行分组？

Question

假设我将 10 定义为两个值之间足够接近的差异，我想要的是所有彼此足够接近的值的平均值(或者换句话说，按它们的接近程度进行分组)。因此，如果我有一个包含以下值的表:

+-------+
| value |
+-------+
|     1 |
|     1 |
|     2 |
|     4 |
|     2 |
|     1 |
|     4 |
|     3 |
|    22 |
|    23 |
|    24 |
|    22 |
|    20 |
|    19 |
|    89 |
|    88 |
|    86 |
+-------+

我想要一个输出以下结果的查询:

+---------+
| 2.2500  |
| 21.6667 |
| 87.6667 |
+---------+

其中 2.2500 将作为从 1 到 4 范围内的所有值的平均值生成，因为它们彼此之间的距离为 10 或更小。同样，21.6667 是 19 到 24 范围内所有值的平均值，87.6667 是 86 到 89 范围内所有值的平均值。

我指定的当前 10 的差异必须是可变的。

Answer 1

这还不算太糟糕。您想要在 MySQL 中实现 lag() 函数来确定某个值是否是一组新行的开始。然后您需要该值的累积和来标识一个组。

代码看起来很痛苦，因为在 MySQL 中，您需要使用相关子查询和连接/聚合而不是使用 ANSI 标准函数来完成此操作，但它看起来是这样的:

select min(value) as value_min, max(value) as value_max, avg(value) as value_avg
from (select t.value, count(*) as GroupId
      from table t join
           (select value
            from (select value,
                         (select max(value)
                          from table t2
                          where t2.value < t.value
                         ) as prevValue
                  from table t
                 ) t
            where value - prevvalue < 10
           ) GroupStarts
           on t.value >= GroupStarts.value
      group by t.value
     ) t
group by GroupId;

子查询GroupStarts正在查找断点，即与前一个值相差10或更多的值的集合。下一个级别使用连接/聚合来计算任何给定值之前的此类断点的数量。最外层查询然后使用此 GroupId 进行聚合。