...

/

Equal-width Binning

Equal-width Binning

Learn how to use equal-width binning using SQL.

Overview

Equal-width binning divides values into ranges of equal width. Unlike equal-height binning, where all buckets have roughly the same frequency, in equal-width binning, each bucket can have a different frequency.

Equal-width binning is often used to produce histograms—a bar chart that shows the frequency of each bucket. A histogram makes it easier to get a sense of how the data is distributed and what are the most and least frequent values.

Equal-width binning using arithmetic

Given a table of student grades, we want to visualize the distribution of grades. We can divide the range of possible scores 1–100 into 10 equal-width buckets. To assign each value to a bucket, we divide it by the desired width of the bucket, 10:

Press + to interact
SELECT
grade,
(grade - 1) / 10 as bucket
FROM
grades
ORDER BY
grade;

Both the grade and width are integers, so the result of the division is truncated, and we get an integer.

We decrease 1 from the grade to make sure we have exactly 10 buckets. Consider for example the lowest grade 1. If we divide 1 by 10 we get 0–-this is the first bucket. The bucket should contain exactly 10 possible grades, so the last grade in the first bucket should be 10. However, if we divide 10 by 10, we get 1. To solve that, we decrease 1 from the grade: (1 - 1) / 10 = 0, and (10 - 1) / 10 = 0. This works for the top end of the range as well, the grade 100. If we divide 100 by 10, we get 10, which will be the eleventh bucket. However, if we decrease 1 from the ...