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
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?