从数据库生成直方图。

Question

从数据库生成直方图。

javasqlgroovylanguage-agnostichistogram

5

在MySQL数据库中，有一张只包含单个数字 value 列的表。我想将这些值作为条状图/直方图进行分布绘制，并满足以下要求:

图表中最多应该有 N 个条 (间隔)
每个条的宽度 (x轴范围) 应该是均匀的，而每个条的高度应反映其区间内数值的数量。
条的两端点应该在圆整的数字处。我知道这是一个相当模糊的要求，但希望下面的示例能说明其意思
各区间之间应连续，例如下一个区间的起点应该在前一个区间的终点处
最好能够使用单个查询检索数据
某些区间的计数 (y轴值) 可以为0
如果第一个区间的下限小于最小值 value，或者最后一个区间的上限大于最大值 value，则也没有关系

示例

如果 N = 3 并且表包含以下数据:

+------------+
| value      |
+------------+
|     -49.2  |
|     -28.2  |
|      13.3  |
|      23.3  |
|      51.4  |
|      77.9  |
+------------+

检查后可以看出，区间{-50..0, 0..50, 50..100}符合此数据集和N值的要求。

然而，我正在努力想出适用于任何N值和任何数据集的通用解决方案。目前为止，我尝试过以下方法：

计算区间宽度

通过以下查询获取最大值和最小值value

SELECT min(value), max(value), count(*) FROM my_table

然后将结果传递到这个(Groovy/Java)方法中，以计算每个间隔的宽度。

// intervalCount is what I've referred to as "N"
static Integer getRoundedIntervalSize(Double min, Double max, Integer intervalCount) {
    Number intervalSize = Math.ceil((max - min) / intervalCount)

    Integer roundingScale = Math.log10(intervalSize) - 1
    Number roundNearest = 10 ** roundingScale

    // round up the interval size to the nearest roundNearest
    Number intervalDelta = roundNearest - (intervalSize % roundNearest)
    intervalSize + intervalDelta
}

获取频率分布

然后我使用以下查询（用 getRoundedIntervalSize 返回的值替换 :groupSize），以获取每个区间中的值的数量。

SELECT      floor(value / :groupSize) * :groupSize  as groupLowerLimit,
            count(*) as groupCount
FROM        my_table
GROUP BY    groupLowerLimit
ORDER BY    groupLowerLimit ASC

这会返回每个区间的下限和每个区间中值的数量，这就是我构建频率分布所需的全部内容。

缺点

尽管当数据集相对均匀分布时，这种方法效果还不错，但当数据集不是这种情况时，它会导致区间具有不同的宽度或不连续。此外，当数据集的范围很小（例如所有值介于1和4之间），而N很大（例如30）时，生成的区间数往往比N小得多。

是否有更好的方法来解决满足上述要求的问题？

- Dónal

+1 是因为你提出了一个非常难得的好问题：在 [so] 上。我真希望我能回答它 :( - user1864610

@MikeW 我也希望你能够 :) 我开始怀疑这更像是一个统计/数学问题，而不是一个编程问题。 - Dónal

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- injecteer · Answer 1

我尝试了几个查询（使用了一个带有一些坐标的表）

获取最小值和条形大小：

select min( lng ) as min, ( max( lng ) - min( lng ) ) / ? as interval from address

获取分布。条形图从0到N编号，当然可能有间隔。

select floor( abs( lng - :min ) / :interval ) as ix, count(*) from address group by ix order by ix

N = 30 的输出如下：

要填补这些空缺，你需要一个简洁的 Groovy 代码：

def metrics = run1stQuery(..., n )
def results = run2ndQuery(.., metrics )
def fullResults = (0..n).collect{ results[ it ] ?: metrics.min } // adjust the missing value

所以，您应该获取任何N和最小/最大值的频率组。

希望对你有帮助。