Generate a histogram of values grouped by a column

Question

I have the following data in a reviews table for certain set of items, using a score system that ranges from 0 to 100

+-----------+---------+-------+
| review_id | item_id | score |
+-----------+---------+-------+
| 1         | 1       | 90    |
+-----------+---------+-------+
| 2         | 1       | 40    |
+-----------+---------+-------+
| 3         | 1       | 10    |
+-----------+---------+-------+
| 4         | 2       | 90    |
+-----------+---------+-------+
| 5         | 2       | 90    |
+-----------+---------+-------+
| 6         | 2       | 70    |
+-----------+---------+-------+
| 7         | 3       | 80    |
+-----------+---------+-------+
| 8         | 3       | 80    |
+-----------+---------+-------+
| 9         | 3       | 80    |
+-----------+---------+-------+
| 10        | 3       | 80    |
+-----------+---------+-------+
| 11        | 4       | 10    |
+-----------+---------+-------+
| 12        | 4       | 30    |
+-----------+---------+-------+
| 13        | 4       | 50    |
+-----------+---------+-------+
| 14        | 4       | 80    |
+-----------+---------+-------+

I am trying to create a histogram of the score values with a bin size of five. My goal is to generate a histogram per item. In order to create a histogram of the entire table, it is possible to use the width_bucket. This can also be tuned to operate on a per-item basis:

SELECT item_id, g.n as bucket, COUNT(m.score) as count 
FROM generate_series(1, 5) g(n) LEFT JOIN
     review as m
     ON width_bucket(score, 0, 100, 4) = g.n
GROUP BY item_id, g.n
ORDER BY item_id, g.n;

However, the result looks like this:

+---------+--------+-------+
| item_id | bucket | count |
+---------+--------+-------+
| 1       | 5      | 1     |
+---------+--------+-------+
| 1       | 3      | 1     |
+---------+--------+-------+
| 1       | 1      | 1     |
+---------+--------+-------+
| 2       | 5      | 2     |
+---------+--------+-------+
| 2       | 4      | 2     |
+---------+--------+-------+
| 3       | 4      | 4     |
+---------+--------+-------+
| 4       | 1      | 1     |
+---------+--------+-------+
| 4       | 2      | 1     |
+---------+--------+-------+
| 4       | 3      | 1     |
+---------+--------+-------+
| 4       | 4      | 1     |
+---------+--------+-------+

That is, bins with no entries are not included. While I find this not to be a bad solution, I would rather have either all buckets, with 0 on those with no entries. Even better, using this structure:

+---------+----------+----------+----------+----------+----------+
| item_id | bucket_1 | bucket_2 | bucket_3 | bucket_4 | bucket_5 |
+---------+----------+----------+----------+----------+----------+
| 1       | 1        | 0        | 1        | 0        | 1        |
+---------+----------+----------+----------+----------+----------+
| 2       | 0        | 0        | 0        | 2        | 2        |
+---------+----------+----------+----------+----------+----------+
| 3       | 0        | 0        | 0        | 4        | 0        |
+---------+----------+----------+----------+----------+----------+
| 4       | 1        | 1        | 1        | 1        | 0        |
+---------+----------+----------+----------+----------+----------+

I prefer this solution as it uses a row per item (instead of 5n), which is simpler to query and minimizes memory consumption and data transfer costs. My current approach is as follows:

select item_id, 
(sum(case when score >= 0 and score <= 19 then 1 else 0 end)) as bucket_1,
(sum(case when score >= 20 and score <= 39 then 1 else 0 end)) as bucket_2,
(sum(case when score >= 40 and score <= 59 then 1 else 0 end)) as bucket_3,
(sum(case when score >= 60 and score <= 79 then 1 else 0 end)) as bucket_4,
(sum(case when score >= 80 and score <= 100 then 1 else 0 end)) as bucket_5
from review;

Even though this query satisfies my requirements, I am curious to see if there might be a more elegant approach. so many case statements are not easy to read and changes in the bin criteria might require updating every sum. Also I am curious about the potential performance concerns that this query might have.

Answer 1

The second query can be rewritten to use ranges to make editing and writing the query a bit easier:

with buckets (b1, b2, b3, b4, b5) as (
  values ( 
     int4range(0, 20), int4range(20, 40), int4range(40, 60), int4range(60, 80), int4range(80, 100) 
  )
)
select item_id, 
       count(*) filter (where b1 @> score) as bucket_1,
       count(*) filter (where b2 @> score) as bucket_2,
       count(*) filter (where b3 @> score) as bucket_3,
       count(*) filter (where b4 @> score) as bucket_4,
       count(*) filter (where b5 @> score) as bucket_5
from review 
  cross join buckets
group by item_id
order by item_id;

A range constructed with int4range(0,20) includes the lower end and excludes the upper end.

The CTE named buckets only creates a single row, so the cross join does not change the number of rows from the review table.