Mysql – Histogram bin width in MySQL

MySQL

I want to create an histogram based on price column values from equipment table.

I know I can count the frequency of prices in an interval I can set, but the problem is that I won't get the ranges with 0 frequency.

so if I want an interval of 200 and the values are:

0,55,150,420,490,550

I will only get the ranges:

0-200, count = 3

400-600, count = 3

as a solution I can write explicitly the ranges, like:

SELECT 
count(CASE WHEN price>= 0 AND price < 200 THEN 1 END) AS '[0 - 200]',
count(CASE WHEN price>= 200 AND price < 400 THEN 1 END) AS '[200 - 400]'
...
...
from equipment
group by eq_type;

if I want to set multiple ranges, let's say from 0 to 10000 in steps of 200,
is there a way I can create an array/series and use it in the query instead of typing 50 rows of the above code?

Best Answer

You need to create synthetic table containing all the intervals in Your timeline range. Or the numbers table when range limits are easily calculated from the range number.

Example:

CREATE TABLE test (val INT);
INSERT INTO test VALUES (0),(55),(150),(420),(490),(550);

SELECT (nums.num-1)*200 mini, nums.num*200 maxi, COUNT(test.val) cnt
FROM (SELECT 1 num UNION ALL SELECT 2 UNION ALL SELECT 3) nums
LEFT JOIN test ON (nums.num-1)*200 <= test.val AND test.val < nums.num*200
GROUP BY nums.num;

The numbers count is to be equal (or more, not less) to the ranges count.

If You want to change the amount of ranges dynamically influenced by data values range (with some reasonable limit) You may use the next method to create synthetic table nums (for example if ranges count guaranteed less than 1000):

SELECT nums1.num*100+nums2.num*10+nums3.num num
FROM (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) nums1,
     (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) nums2,
     (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) nums3

To cut off the upper empty ranges use additional condition like

WHERE ... AND nums.num * @step <= (SELECT MAX(val) FROM datatable)
Related Question