generate histogram from database

Question

In a MySQL database there's a table with a single numeric value column. I want to plot the distribution of these values as a bar chart/histogram with the following requirements:

• there should be a maximum of N bars (intervals) in the chart
• the width of each bar (x-axis range) should be uniform and the height of each bar should reflect the number of values in this interval.
• the endpoints of the bar should occur at round numbers. I understand that this is a fairly vague requirement, but hopefully the following example will illustrate what I mean
• the intervals should be contiguous, e.g. the next interval should start where the previous one ended
• ideally it should be possible to retrieve the data with a single query
• it's OK to have intervals with a count (y-axis value) of 0
• it's OK if the lower bound of the first interval is less than the minimum value and/or the upper bound of the last interval is greater than the maximum value

Example

If N = 3 and the table contains the following data

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

On inspection it's fairly easy to see that the intervals {-50..0, 0..50, 50..100} satisfy the requirements for this dataset and value of N.

However, I'm struggling to come up with a general solution that works for any value of N and any dataset. Here's what I've tried so far:

Calculate Interval Width

Get the max and min value via the following query

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

Then pass the result into this (Groovy/Java) method to calculate the width of each interval

// 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
}

Get Frequency Distribution

I then use the following query (with the value returned by getRoundedIntervalSize substituted for :groupSize) to get the number of values in each interval

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

This returns the lower limit of each interval and the number of values in each interval, which is all I need to build the frequency distribution.

Shortcomings

Although this approach works fairly well when the dataset is relatively uniformly distributed, when this is not the case, it results in intervals which have different widths or are not contiguous. Also, when the range of the dataset is small (e.g all values between 1 and 4) and N is large (e.g. 30), the number of intervals generated tends to be much smaller than N.

Is there a better approach to solving this problem that meets the requirements above?

Answer 1

I tried a couple of queries (I used a table with some coordinates)

get the min val and bar size:

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

get the distribution. The bars are numbered from 0 to N and might have gaps of course

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

the output for N = 30 is like:

ix  count
0   31
18  10149
20  36185
21  5443
24  1
29  3

to fill in the gaps you gonna need a groovy one-liner:

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

So, you should be getting your freq-groups for any N and min/max values.

HTH