Equal-height Binning
Learn how to create equal height binning in SQL.
Overview
Equal-height binning is used to create bins, or groups, with roughly the same number of objects. This type of binning is not very common for visualization, but it is useful for other purposes.
Equal-height binning
Given a table of student grades, say we want to divide the students into groups based on their grades. We want each group to have roughly the same number of students, and we want to group students of the same level, based on their grades.
First, let's get familiar with the grades table by producing descriptive data:
SELECTMIN(grade) AS min_grade,MAX(grade) AS max_grade,COUNT(grade) AS count_grade,COUNT(DISTINCT grade) AS distinct_gradesFROMgrades;
We have 30 grades ranging from 24 to 100. There are only 25 distinct grades, meaning some students received similar grades.
To get started with equal-height binning we first need to determine how many buckets, or groups, we want to have. If, for example, we want to divide our 30 students into 3 groups, each group should have 10 students.
Equal-height binning using arithmetic
The first approach to equal-height binning is using simple arithmetic. To calculate the bucket for each grade, we rank students by their grades, divide the rank by the width of the bucket, and round down the result.
Fortunately for us, PostgreSQL ...