Getting data for histogram plot
MysqlHistogramBinningMysql Problem Overview
Is there a way to specify bin sizes in MySQL? Right now, I am trying the following SQL query:
select total, count(total) from faults GROUP BY total;
The data that is being generated is good enough but there are just too many rows. What I need is a way to group the data into predefined bins. I can do this from a scripting language, but is there a way to do it directly in SQL?
Example:
+-------+--------------+
| total | count(total) |
+-------+--------------+
| 30 | 1 |
| 31 | 2 |
| 33 | 1 |
| 34 | 3 |
| 35 | 2 |
| 36 | 6 |
| 37 | 3 |
| 38 | 2 |
| 41 | 1 |
| 42 | 5 |
| 43 | 1 |
| 44 | 7 |
| 45 | 4 |
| 46 | 3 |
| 47 | 2 |
| 49 | 3 |
| 50 | 2 |
| 51 | 3 |
| 52 | 4 |
| 53 | 2 |
| 54 | 1 |
| 55 | 3 |
| 56 | 4 |
| 57 | 4 |
| 58 | 2 |
| 59 | 2 |
| 60 | 4 |
| 61 | 1 |
| 63 | 2 |
| 64 | 5 |
| 65 | 2 |
| 66 | 3 |
| 67 | 5 |
| 68 | 5 |
------------------------
What I am looking for:
+------------+---------------+
| total | count(total) |
+------------+---------------+
| 30 - 40 | 23 |
| 40 - 50 | 15 |
| 50 - 60 | 51 |
| 60 - 70 | 45 |
------------------------------
I guess this cannot be achieved in a straight forward manner but a reference to any related stored procedure would be fine as well.
Mysql Solutions
Solution 1 - Mysql
> This is a post about a super quick-and-dirty way to create a histogram > in MySQL for numeric values. > > There are multiple other ways to create histograms that are better and > more flexible, using CASE statements and other types of complex logic. > This method wins me over time and time again since it's just so easy > to modify for each use case, and so short and concise. This is how you > do it: > > SELECT ROUND(numeric_value, -2) AS bucket, > COUNT() AS COUNT, > RPAD('', LN(COUNT()), '*') AS bar > FROM my_table > GROUP BY bucket; > > Just change numeric_value to whatever your column is, change the > rounding increment, and that's it. I've made the bars to be in > logarithmic scale, so that they don't grow too much when you have > large values. > > numeric_value should be offset in the ROUNDing operation, based on the rounding increment, in order to ensure the first bucket contains as many elements as the following buckets. > > e.g. with ROUND(numeric_value,-1), numeric_value in range [0,4] (5 elements) will be placed in first bucket, while [5,14] (10 elements) in second, [15,24] in third, unless numeric_value is offset appropriately via ROUND(numeric_value - 5, -1). > > > This is an example of such query on some random data that looks pretty > sweet. Good enough for a quick evaluation of the data. > > +--------+----------+-----------------+ > | bucket | count | bar | > +--------+----------+-----------------+ > | -500 | 1 | | > | -400 | 2 | * | > | -300 | 2 | * | > | -200 | 9 | ** | > | -100 | 52 | **** | > | 0 | 5310766 | *************** | > | 100 | 20779 | ********** | > | 200 | 1865 | ******** | > | 300 | 527 | ****** | > | 400 | 170 | ***** | > | 500 | 79 | **** | > | 600 | 63 | **** | > | 700 | 35 | **** | > | 800 | 14 | *** | > | 900 | 15 | *** | > | 1000 | 6 | ** | > | 1100 | 7 | ** | > | 1200 | 8 | ** | > | 1300 | 5 | ** | > | 1400 | 2 | * | > | 1500 | 4 | * | > +--------+----------+-----------------+ > > Some notes: Ranges that have no match will not appear in the count - > you will not have a zero in the count column. Also, I'm using the > ROUND function here. You can just as easily replace it with TRUNCATE > if you feel it makes more sense to you.
I found it here http://blog.shlomoid.com/2011/08/how-to-quickly-create-histogram-in.html
Solution 2 - Mysql
Mike DelGaudio's answer is the way I do it, but with a slight change:
select floor(mycol/10)*10 as bin_floor, count(*)
from mytable
group by 1
order by 1
The advantage? You can make the bins as large or as small as you want. Bins of size 100? floor(mycol/100)*100. Bins of size 5? floor(mycol/5)*5.
Bernardo.
Solution 3 - Mysql
SELECT b.*,count(*) as total FROM bins b
left outer join table1 a on a.value between b.min_value and b.max_value
group by b.min_value
The table bins contains columns min_value and max_value which define the bins. note that the operator "join... on x BETWEEN y and z" is inclusive.
table1 is the name of the data table
Solution 4 - Mysql
Ofri Raviv's answer is very close but incorrect. The count(*) will be 1 even if there are zero results in a histogram interval. The query needs to be modified to use a conditional sum:
SELECT b.*, SUM(a.value IS NOT NULL) AS total FROM bins b
LEFT JOIN a ON a.value BETWEEN b.min_value AND b.max_value
GROUP BY b.min_value;
Solution 5 - Mysql
select "30-34" as TotalRange,count(total) as Count from table_name
where total between 30 and 34
union (
select "35-39" as TotalRange,count(total) as Count from table_name
where total between 35 and 39)
union (
select "40-44" as TotalRange,count(total) as Count from table_name
where total between 40 and 44)
union (
select "45-49" as TotalRange,count(total) as Count from table_name
where total between 45 and 49)
etc ....
As long as there are not too many intervals, this is a pretty good solution.
Solution 6 - Mysql
I made a procedure that can be used to automatically generate a temporary table for bins according to a specified number or size, for later use with Ofri Raviv's solution.
CREATE PROCEDURE makebins(numbins INT, binsize FLOAT) # binsize may be NULL for auto-size
BEGIN
SELECT FLOOR(MIN(colval)) INTO @binmin FROM yourtable;
SELECT CEIL(MAX(colval)) INTO @binmax FROM yourtable;
IF binsize IS NULL
THEN SET binsize = CEIL((@binmax-@binmin)/numbins); # CEIL here may prevent the potential creation a very small extra bin due to rounding errors, but no good where floats are needed.
END IF;
SET @currlim = @binmin;
WHILE @currlim + binsize < @binmax DO
INSERT INTO bins VALUES (@currlim, @currlim+binsize);
SET @currlim = @currlim + binsize;
END WHILE;
INSERT INTO bins VALUES (@currlim, @maxbin);
END;
DROP TABLE IF EXISTS bins; # be careful if you have a bins table of your own.
CREATE TEMPORARY TABLE bins (
minval INT, maxval INT, # or FLOAT, if needed
KEY (minval), KEY (maxval) );# keys could perhaps help if using a lot of bins; normally negligible
CALL makebins(20, NULL); # Using 20 bins of automatic size here.
SELECT bins.*, count(*) AS total FROM bins
LEFT JOIN yourtable ON yourtable.value BETWEEN bins.minval AND bins.maxval
GROUP BY bins.minval
This will generate the histogram count only for the bins that are populated. David West ought to be right in his correction, but for some reason, unpopulated bins do not appear in the result for me (despite the use of a LEFT JOIN — I do not understand why).
Solution 7 - Mysql
That should work. Not so elegant but still:
select count(mycol - (mycol mod 10)) as freq, mycol - (mycol mod 10) as label
from mytable
group by mycol - (mycol mod 10)
order by mycol - (mycol mod 10) ASC
via Mike DelGaudio
Solution 8 - Mysql
SELECT
CASE
WHEN total <= 30 THEN "0-30"
WHEN total <= 40 THEN "31-40"
WHEN total <= 50 THEN "41-50"
ELSE "50-"
END as Total,
count(*) as count
GROUP BY Total
ORDER BY Total;
Solution 9 - Mysql
Equal width binning into a given count of bins:
WITH bins AS(
SELECT min(col) AS min_value
, ((max(col)-min(col)) / 10.0) + 0.0000001 AS bin_width
FROM cars
)
SELECT tab.*,
floor((col-bins.min_value) / bins.bin_width ) AS bin
FROM tab, bins;
Note that the 0.0000001 is there to make sure that the records with the value equal to max(col) do not make it's own bin just by itself. Also, the additive constant is there to make sure the query does not fail on division by zero when all the values in the column are identical.
Also note that the count of bins (10 in the example) should be written with a decimal mark to avoid integer division (the unadjusted bin_width can be decimal).
Solution 10 - Mysql
In addition to great answer https://stackoverflow.com/a/10363145/916682, you can use phpmyadmin chart tool for a nice result:
